Oracle ORA-02270 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02270: No Matching Unique or Primary Key for This Column-List

ORA-02270 occurs when you try to create a foreign key constraint that references a column (or set of columns) in a parent table that does not have a corresponding Primary Key or Unique Key defined. Oracle strictly enforces referential integrity, meaning every foreign key must point to a uniquely identifiable set of columns in the parent table. This error is most commonly encountered during DDL operations such as CREATE TABLE or ALTER TABLE.

Top 3 Causes and Fixes

Cause 1: No Primary Key or Unique Key on the Referenced Column

The most common cause is simply that the parent table's referenced column lacks a PRIMARY KEY or UNIQUE constraint. A regular index alone is not sufficient.

-- This will cause ORA-02270 if parent_table.parent_id has no PK or UNIQUE constraint
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id);  -- ORA-02270 if no PK/UK on parent_id

-- Fix: Add a Primary Key to the parent table first
ALTER TABLE parent_table
ADD CONSTRAINT pk_parent PRIMARY KEY (parent_id);

-- Now the foreign key creation will succeed
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id);

Cause 2: Composite Key Column Order Mismatch

When referencing a composite Primary Key or Unique Key, the column list in the foreign key must exactly match the order and composition of the parent key.

-- Parent table has a composite PK on (dept_id, emp_type) in this order
CREATE TABLE parent_table (
    dept_id   NUMBER,
    emp_type  VARCHAR2(10),
    CONSTRAINT pk_parent PRIMARY KEY (dept_id, emp_type)
);

-- WRONG: Column order is reversed — causes ORA-02270
ALTER TABLE child_table
ADD CONSTRAINT fk_wrong
FOREIGN KEY (emp_type, dept_id)
REFERENCES parent_table(emp_type, dept_id);

-- CORRECT: Match the exact column order of the parent PK
ALTER TABLE child_table
ADD CONSTRAINT fk_correct
FOREIGN KEY (dept_id, emp_type)
REFERENCES parent_table(dept_id, emp_type);

Cause 3: Typo in Table/Column Name or Wrong Schema Reference

If the parent table name, column name, or schema is misspelled, Oracle either cannot find the object or finds it without a matching key constraint.

-- Verify the parent table and its constraints before creating the FK
SELECT owner, constraint_name, constraint_type, table_name
FROM all_constraints
WHERE table_name = 'PARENT_TABLE'
  AND constraint_type IN ('P', 'U');

-- Check column details of the constraint
SELECT ucc.constraint_name, ucc.column_name, ucc.position
FROM all_cons_columns ucc
JOIN all_constraints uc ON ucc.constraint_name = uc.constraint_name
WHERE uc.table_name = 'PARENT_TABLE'
  AND uc.constraint_type IN ('P', 'U')
ORDER BY ucc.position;

-- Cross-schema foreign key: always specify the schema explicitly
ALTER TABLE myschema.child_table
ADD CONSTRAINT fk_cross
FOREIGN KEY (dept_id)
REFERENCES hrschema.parent_table(dept_id);

Quick Fix Checklist

Verify the parent table has a PK or UK on the referenced column(s) using USER_CONSTRAINTS or ALL_CONSTRAINTS.
Check composite key column order — it must match exactly between parent and child.
Confirm table and column names are spelled correctly and the schema is accessible.
Ensure data types match between the foreign key columns and the parent key columns.

Prevention Tips

Always define Primary Keys at table creation time. Establish a DDL coding standard in your organization that requires PRIMARY KEY constraints to be declared in the CREATE TABLE statement, never deferred.
Use a pre-deployment validation script to verify all parent table constraints exist before running FK creation scripts, reducing the chance of ORA-02270 in production.

-- Quick pre-check before adding a foreign key
SELECT COUNT(*) AS pk_uk_count
FROM user_constraints
WHERE table_name = 'PARENT_TABLE'
  AND constraint_type IN ('P', 'U');
-- If result is 0, add the required constraint before proceeding

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community