databaseoracle-databaseforeign-keysprimary-key

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


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

  • 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

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