Oracle (ORA-02270) : no matching unique or primary key for this column-list error

The ORA-2270 error is a straightforward logical error: it happens when the columns we list in the foreign key do not match a primary key or unique constraint on the parent table. Common reasons for this are

• the parent lacks a PRIMARY KEY or UNIQUE constraint altogether
• the foreign key clause references the wrong column in the parent table
• the parent table's constraint is a compound key and we haven't referenced all the columns in the foreign key statement.

Neither appears to be the case in your posted code. But that's a red herring, because your code does not run as you have posted it. Judging from the previous edits I presume you are not posting your actual code but some simplified example. Unfortunately in the process of simplification you have eradicated whatever is causing the ORA-2270 error.

SQL> CREATE TABLE JOB
 (
   ID       NUMBER NOT NULL ,
   USERID   NUMBER,
   CONSTRAINT B_PK PRIMARY KEY ( ID ) ENABLE
 );  2    3    4    5    6  

Table created.

SQL> CREATE TABLE USER
 (
   ID       NUMBER NOT NULL ,
   CONSTRAINT U_PK PRIMARY KEY ( ID ) ENABLE
 );  2    3    4    5  
CREATE TABLE USER
             *
ERROR at line 1:
ORA-00903: invalid table name


SQL> 

That statement failed because USER is a reserved keyword so we cannot name a table USER. Let's fix that:

SQL> 1
  1* CREATE TABLE USER
SQL> a s
  1* CREATE TABLE USERs
SQL> l
  1  CREATE TABLE USERs
  2   (
  3     ID       NUMBER NOT NULL ,
  4     CONSTRAINT U_PK PRIMARY KEY ( ID ) ENABLE
  5*  )
SQL> r
  1  CREATE TABLE USERs
  2   (
  3     ID       NUMBER NOT NULL ,
  4     CONSTRAINT U_PK PRIMARY KEY ( ID ) ENABLE
  5*  )

Table created.

SQL> Alter Table JOB ADD CONSTRAINT FK_USERID FOREIGN KEY(USERID) REFERENCES USERS(ID);   

Table altered.

SQL> 

And lo! No ORA-2270 error.

Alas, there's not much we can do here to help you further. You have a bug in your code. You can post your code here and one of us can spot your mistake. Or you can check your own code and discover it for yourself.

---

Note: an earlier version of the code defined HOB.USERID as VARCHAR2(20). Because USER.ID is defined as a NUMBER the attempt to create a foreign key would have hurl a different error:

ORA-02267: column type incompatible with referenced column type

An easy way to avoid mismatches is to use foreign key syntax to default the datatype of the column:

CREATE TABLE USERs
 (
   ID    number NOT NULL ,
   CONSTRAINT U_PK PRIMARY KEY ( ID ) ENABLE
 );

CREATE TABLE JOB
 (
   ID       NUMBER NOT NULL ,
   USERID   constraint FK_USERID references users,
   CONSTRAINT B_PK PRIMARY KEY ( ID ) ENABLE
 );

The data type in the Job table (Varchar2(20)) does not match the data type in the USER table (NUMBER NOT NULL).

In my case the problem was cause by a disabled PK.

In order to enable it:

1. I look for the Constraint name with:

   SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'referenced_table_name';

2. Then I took the Constraint name in order to enable it with the following command:

   ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;