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

Question

I have two tables, Table JOB and Table USER, here is the structure

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

 CREATE TABLE USER
 (
   ID       NUMBER NOT NULL ,
   CONSTRAINT U_PK PRIMARY KEY ( ID ) ENABLE
 );

Now, i want to add foreign key constraint to JOB referencing to USER table, as

Alter Table JOB ADD CONSTRAINT FK_USERID FOREIGN KEY(USERID) REFERENCES USER(ID);

this throws Oracle (ORA-02270) : no matching unique or primary key for this column-list error, doing some investigation it appears that we need to have either unique key or primary key constraint on USERID but I cannot have that as one USERID can have multiple JOBS associated with him, any thoughts or suggestions on how to fix this issue?

Researched ORA-02270 and SO related question

Solution 1

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
 );

Solution 2

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

Solution 3

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;

Solution 4

We have following script for create new table:

CREATE TABLE new_table
(
id                     NUMBER(32) PRIMARY KEY,
referenced_table_id    NUMBER(32)    NOT NULL,
CONSTRAINT fk_new_table_referenced_table_id
    FOREIGN KEY (referenced_table_id)
        REFERENCES referenced_table (id)
);

and we were getting this error on execute:

[42000][2270] ORA-02270: no matching unique or primary key for this column-list

The issue was due to disabled primary key of referenced table in our case. We have enabled it by

ALTER TABLE referenced_table ENABLE PRIMARY KEY USING INDEX;

after that we created new table using first script without any issues

Solution 5

Most Probably when you have a missing Primary key is not defined from parent table. then It occurs.

Like Add the primary key define in parent as below:

ALTER TABLE "FE_PRODUCT" ADD CONSTRAINT "FE_PRODUCT_PK" PRIMARY KEY ("ID") ENABLE;

Hope this will work.

View more solutions

Comments

[Bob Jarvis - Слава Україні]

Suggestion: drop these tables and then re-run the DDL. I suggest creating USER first and then JOB. Let us know if it works. You also might try putting "USER" in double-quotes, as noted in @LSU.Net's comment to the first answer (below).

[Rachel]

@APC: First, I do not intend to waste communities time, I value efforts community is putting in. Apart from that I do not know where is bug generated and so having issues.

[Derick Daniel]

reason behind this error is - ID column of User table is not not unique or primary key

[Rachel]

i should have mentioned it upfront, both id as well as userid are number type.

[Rachel]

i have rectified the error in question, both userid as well as id are numbers

[LSU.Net]

You have some other issue Rachel, I created the two tables along with the FK constraint and it works fine. Note that "USER" needs escaping quotes and your primary keys also have identical constraint names which is invalid DDL.

[LSU.Net]

This DDL executes without issue in Oracle 11g: codeCREATE TABLE JOB ( ID NUMBER NOT NULL , USERID NUMBER, CONSTRAINT B_PK PRIMARY KEY ( ID ) ENABLE ); CREATE TABLE "USER" ( ID NUMBER NOT NULL , CONSTRAINT U_PK PRIMARY KEY ( ID ) ENABLE ); Alter Table JOB ADD CONSTRAINT FK_USERID FOREIGN KEY(USERID) REFERENCES "USER"(ID);

[Denis M. Kitchen]

"...clearly you have failed to understand it. The error is quite simple:" We (all of us) do well to find better, higher quality ways to communicate than this.