ORA-02267 - Column Type Incompatible with Referenced Column

ORA-02267: Column Type Incompatible with Referenced Column

The ORA-02267 error occurs when creating a foreign key constraint where the data types of the referencing column and the referenced column are incompatible. Oracle requires exact data type matching for foreign key relationships.

Use at Your Own Risk

This documentation is for educational purposes only. Always test solutions in development environments before production use. See our disclaimer for full terms.

Error Details

• Error Code: ORA-02267
• Error Message: “column type incompatible with referenced column”
• Error Type: Constraint Creation Error
• Severity: Compilation Error

Common Causes

1. Data Type Mismatch

The foreign key column has a different data type than the referenced primary key column.

2. Length Mismatch

VARCHAR2 columns with different lengths.

3. Precision/Scale Mismatch

NUMBER columns with different precision or scale.

4. Character Set Differences

CHAR vs VARCHAR2 or different character sets.

5. NULL Constraints

Different nullability constraints between columns.

Examples and Solutions

Example 1: Data Type Mismatch

Problem:

-- Parent table with NUMBER primary key
CREATE TABLE departments (
    dept_id NUMBER(4) PRIMARY KEY,
    dept_name VARCHAR2(50)
);

-- Child table with VARCHAR2 foreign key
CREATE TABLE employees (
    emp_id NUMBER(6),
    emp_name VARCHAR2(100),
    dept_id VARCHAR2(10)  -- Wrong data type
);

-- This will fail
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);
-- ORA-02267: column type incompatible with referenced column

Solution:

-- Fix: Change foreign key column to match parent
ALTER TABLE employees MODIFY dept_id NUMBER(4);

-- Now add the constraint
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

Example 2: VARCHAR2 Length Mismatch

Problem:

-- Parent table
CREATE TABLE countries (
    country_code VARCHAR2(3) PRIMARY KEY,
    country_name VARCHAR2(100)
);

-- Child table with different length
CREATE TABLE regions (
    region_id NUMBER,
    region_name VARCHAR2(50),
    country_code VARCHAR2(10)  -- Different length
);

-- This will fail
ALTER TABLE regions
ADD CONSTRAINT fk_region_country
FOREIGN KEY (country_code) REFERENCES countries(country_code);
-- ORA-02267: column type incompatible with referenced column

Solution:

-- Fix: Match the exact length
ALTER TABLE regions MODIFY country_code VARCHAR2(3);

-- Add the constraint
ALTER TABLE regions
ADD CONSTRAINT fk_region_country
FOREIGN KEY (country_code) REFERENCES countries(country_code);

Example 3: NUMBER Precision/Scale Mismatch

Problem:

-- Parent table
CREATE TABLE products (
    product_id NUMBER(8,0) PRIMARY KEY,
    product_name VARCHAR2(100)
);

-- Child table with different precision
CREATE TABLE order_items (
    order_id NUMBER,
    line_number NUMBER,
    product_id NUMBER(6,0),  -- Different precision
    quantity NUMBER
);

-- This will fail
ALTER TABLE order_items
ADD CONSTRAINT fk_item_product
FOREIGN KEY (product_id) REFERENCES products(product_id);
-- ORA-02267: column type incompatible with referenced column

Solution:

-- Fix: Match the exact precision and scale
ALTER TABLE order_items MODIFY product_id NUMBER(8,0);

-- Add the constraint
ALTER TABLE order_items
ADD CONSTRAINT fk_item_product
FOREIGN KEY (product_id) REFERENCES products(product_id);

Example 4: CHAR vs VARCHAR2

Problem:

-- Parent table with CHAR
CREATE TABLE status_codes (
    status_code CHAR(2) PRIMARY KEY,
    description VARCHAR2(50)
);

-- Child table with VARCHAR2
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_date DATE,
    status_code VARCHAR2(2)  -- Different character type
);

-- This will fail
ALTER TABLE orders
ADD CONSTRAINT fk_order_status
FOREIGN KEY (status_code) REFERENCES status_codes(status_code);
-- ORA-02267: column type incompatible with referenced column

Solution:

-- Fix: Use the same character type
ALTER TABLE orders MODIFY status_code CHAR(2);

-- Add the constraint
ALTER TABLE orders
ADD CONSTRAINT fk_order_status
FOREIGN KEY (status_code) REFERENCES status_codes(status_code);

Example 5: Composite Foreign Key Mismatch

Problem:

-- Parent table with composite key
CREATE TABLE order_headers (
    company_id NUMBER(4),
    order_id NUMBER(8),
    order_date DATE,
    PRIMARY KEY (company_id, order_id)
);

-- Child table with mismatched types
CREATE TABLE order_lines (
    line_id NUMBER,
    company_id VARCHAR2(10),  -- Wrong type
    order_id NUMBER(6),       -- Wrong precision
    product_id NUMBER
);

-- This will fail
ALTER TABLE order_lines
ADD CONSTRAINT fk_line_header
FOREIGN KEY (company_id, order_id)
REFERENCES order_headers(company_id, order_id);
-- ORA-02267: column type incompatible with referenced column

Solution:

-- Fix: Match all column types exactly
ALTER TABLE order_lines MODIFY company_id NUMBER(4);
ALTER TABLE order_lines MODIFY order_id NUMBER(8);

-- Add the constraint
ALTER TABLE order_lines
ADD CONSTRAINT fk_line_header
FOREIGN KEY (company_id, order_id)
REFERENCES order_headers(company_id, order_id);

Diagnostic Queries

Check Column Data Types

-- Compare data types between parent and child tables
SELECT
    'PARENT' AS table_type,
    table_name,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable
FROM user_tab_columns
WHERE table_name = 'PARENT_TABLE'
AND column_name = 'PRIMARY_KEY_COLUMN'

UNION ALL

SELECT
    'CHILD' AS table_type,
    table_name,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable
FROM user_tab_columns
WHERE table_name = 'CHILD_TABLE'
AND column_name = 'FOREIGN_KEY_COLUMN'
ORDER BY table_type, column_name;

Find Existing Foreign Keys

-- Check existing foreign key constraints
SELECT
    c.constraint_name,
    c.table_name AS child_table,
    cc.column_name AS child_column,
    c.r_constraint_name,
    rc.table_name AS parent_table,
    rcc.column_name AS parent_column
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
JOIN user_constraints rc ON c.r_constraint_name = rc.constraint_name
JOIN user_cons_columns rcc ON rc.constraint_name = rcc.constraint_name
WHERE c.constraint_type = 'R'
AND cc.position = rcc.position
ORDER BY c.table_name, cc.position;

Verify Constraint Readiness

-- Check if foreign key can be created
WITH parent_cols AS (
    SELECT column_name, data_type, data_length, data_precision, data_scale
    FROM user_tab_columns
    WHERE table_name = 'PARENT_TABLE'
    AND column_name = 'PARENT_COLUMN'
),
child_cols AS (
    SELECT column_name, data_type, data_length, data_precision, data_scale
    FROM user_tab_columns
    WHERE table_name = 'CHILD_TABLE'
    AND column_name = 'CHILD_COLUMN'
)
SELECT
    CASE
        WHEN p.data_type = c.data_type
        AND NVL(p.data_length, 0) = NVL(c.data_length, 0)
        AND NVL(p.data_precision, 0) = NVL(c.data_precision, 0)
        AND NVL(p.data_scale, 0) = NVL(c.data_scale, 0)
        THEN 'COMPATIBLE'
        ELSE 'INCOMPATIBLE'
    END AS compatibility,
    'Parent: ' || p.data_type ||
    CASE
        WHEN p.data_precision IS NOT NULL
        THEN '(' || p.data_precision ||
             CASE WHEN p.data_scale IS NOT NULL THEN ',' || p.data_scale END || ')'
        WHEN p.data_length IS NOT NULL
        THEN '(' || p.data_length || ')'
    END AS parent_definition,
    'Child: ' || c.data_type ||
    CASE
        WHEN c.data_precision IS NOT NULL
        THEN '(' || c.data_precision ||
             CASE WHEN c.data_scale IS NOT NULL THEN ',' || c.data_scale END || ')'
        WHEN c.data_length IS NOT NULL
        THEN '(' || c.data_length || ')'
    END AS child_definition
FROM parent_cols p, child_cols c;

Resolution Strategies

1. Modify Child Table Column

-- Most common solution: change foreign key column
ALTER TABLE child_table
MODIFY foreign_key_column parent_column_datatype;

-- Examples:
ALTER TABLE employees MODIFY dept_id NUMBER(4);
ALTER TABLE regions MODIFY country_code VARCHAR2(3);

2. Modify Parent Table Column (If Possible)

-- Less common: change parent table if no data/constraints conflict
ALTER TABLE parent_table
MODIFY primary_key_column new_datatype;

-- Note: This may affect other foreign keys

3. Add Conversion Function

-- Create view with conversion if direct modification isn't possible
CREATE VIEW employees_v AS
SELECT
    emp_id,
    emp_name,
    TO_NUMBER(dept_id) AS dept_id  -- Convert VARCHAR2 to NUMBER
FROM employees;

-- Use view for foreign key relationship

4. Recreate Tables

-- For complex scenarios, recreate with correct structure
-- 1. Export data
CREATE TABLE employees_temp AS SELECT * FROM employees;

-- 2. Drop and recreate with correct types
DROP TABLE employees;
CREATE TABLE employees (
    emp_id NUMBER(6) PRIMARY KEY,
    emp_name VARCHAR2(100),
    dept_id NUMBER(4)  -- Correct type
);

-- 3. Import data with conversion
INSERT INTO employees
SELECT emp_id, emp_name, TO_NUMBER(dept_id)
FROM employees_temp;

-- 4. Create foreign key
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

Prevention Best Practices

1. Design Standards

-- Establish data type standards
-- Use consistent naming conventions
-- Document foreign key relationships

-- Example standards:
-- IDs: NUMBER(10)
-- Codes: VARCHAR2(10)
-- Flags: CHAR(1)

2. Create Tables with Constraints

-- Define foreign keys during table creation
CREATE TABLE employees (
    emp_id NUMBER(6) PRIMARY KEY,
    emp_name VARCHAR2(100),
    dept_id NUMBER(4),
    CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

3. Use DESCRIBE Command

-- Always check column definitions before creating constraints
DESCRIBE parent_table;
DESCRIBE child_table;

4. Documentation

-- Document all foreign key relationships
-- Include data type requirements
-- Maintain data dictionary

Related Errors

• ORA-02270: No Matching Unique or Primary Key
• ORA-02291: Integrity Constraint Violated
• ORA-00001: Unique Constraint Violated
• ORA-02264: Name Already Used by Existing Constraint

Summary

ORA-02267 occurs when foreign key and referenced columns have incompatible data types. Resolution requires ensuring exact data type matching including length, precision, and scale. The most common solution is modifying the foreign key column to match the referenced column’s data type exactly.