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

User · Answer

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

User · Answer

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

User · Answer

create table articles code varchar2 30 constraint articles pk primary key titre varchar2 30   support varchar2 30  constraint support fk references supports support  type support varchar2 30   numeroDePage varchar2 30  datepublication date categorie varchar2 30 constraint categorie fk references organismes categorie   tendance varchar2 30 constraint tendance fk references apreciations tendance

User · Answer

The scheme is correct  User ID must be the primary key of User  Job ID should be the primary key of Job and Job UserID should be a foreign key to User ID  Also  your commands appear to be syntactically correct   So what could be wrong  I believe you have at least a Job UserID which doesn t have a pair in User ID  For instance  if all values of User ID are  1 2 3 4 6 7 8 and you have a value of Job UserID of 5  which is not among 1 2 3 4 6 7 8  which are the possible values of UserID   you will not be able to create your foreign key constraint  Solution   delete from Job where UserID in  select distinct User ID from User     will delete all jobs with nonexistent users  You might want to migrate these to a copy of this table which will contain archive data

User · Answer

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 gt  CREATE TABLE JOB       ID       NUMBER NOT NULL      USERID   NUMBER     CONSTRAINT B PK PRIMARY KEY   ID   ENABLE      2    3    4    5    6    Table created   SQL gt  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 gt     That statement failed because USER is a reserved keyword so we cannot name a table USER   Let s fix that   SQL gt  1   1  CREATE TABLE USER SQL gt  a s   1  CREATE TABLE USERs SQL gt  l   1  CREATE TABLE USERs   2       3     ID       NUMBER NOT NULL     4     CONSTRAINT U PK PRIMARY KEY   ID   ENABLE   5     SQL gt  r   1  CREATE TABLE USERs   2       3     ID       NUMBER NOT NULL     4     CONSTRAINT U PK PRIMARY KEY   ID   ENABLE   5      Table created   SQL gt  Alter Table JOB ADD CONSTRAINT FK USERID FOREIGN KEY USERID  REFERENCES USERS ID       Table altered   SQL gt     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

User · Answer

When running this command   ALTER TABLE MYTABLENAME MODIFY CONSTRAINT MYCONSTRAINTNAME FK ENABLE    I got this error   ORA-02270  no matching unique or primary key for this column-list 02270  00000 -   no matching unique or primary key for this column-list   Cause     A REFERENCES clause in a CREATE ALTER TABLE statement           gives a column-list for which there is no matching unique or primary           key constraint in the referenced table   Action    Find the correct column names using the ALL CONS COLUMNS   The referenced table has a primary key constraint with matching type   The root cause of this error  in my case  was that the primary key constraint was disabled

User · Answer

I faced the same issue in my scenario as follow   I created textbook table first with  create table textbook txtbk isbn varchar2 13  primary key txtbk title varchar2 40   txtbk author varchar2 40       Then chapter table   create table chapter txtbk isbn varchar2 13  chapter title varchar2 40   constraint pk chapter primary key txtbk isbn chapter title   constraint chapter txtbook foreign key  txtbk isbn  references textbook  txtbk isbn     Then topic table   create table topic topic id varchar2 20  primary key topic name varchar2 40      Now when I wanted to create a relationship called chapter topic between chapter  having composite primary key  and topic  having single column primary key   I faced issue with following query   create table chapter topic txtbk isbn varchar2 13  chapter title varchar2 40  topic id varchar2 20   primary key  txtbk isbn  chapter title  topic id    foreign key  txtbk isbn  references textbook txtbk isbn    foreign key  chapter title  references chapter chapter title    foreign key  topic id  references topic  topic id     The solution was to refer to composite foreign key as below   create table chapter topic txtbk isbn varchar2 13  chapter title varchar2 40  topic id varchar2 20   primary key  txtbk isbn  chapter title  topic id    foreign key  txtbk isbn  chapter title  references chapter txtbk isbn  chapter title    foreign key  topic id  references topic  topic id     Thanks to APC post in which he mentioned in his post a statement that      Common reasons for this are    - the parent lacks a constraint altogether    - the parent table s constraint is a compound key and we haven t referenced all the columns in the foreign key statement    - the referenced PK constraint exists but is DISABLED

User · Answer

In my case the problem was cause by a disabled PK  In order to enable it   I look for the Constraint name with  SELECT   FROM USER CONS COLUMNS WHERE TABLE NAME    referenced table name    Then I took the Constraint name in order to enable it with the following command  ALTER TABLE table name ENABLE CONSTRAINT constraint name

User · Answer

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

User · Answer

If primary key is not already defined on parent table then this issue may arise  Please try to define the primary key on existing table   For eg   ALTER TABLE table name ADD PRIMARY KEY  the column which is primary key

User · Answer

Isn t the difference between your declaration of USERID the problem  JOB  UserID is Varchar USER  UserID is Number

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

Examples related to database

Examples related to oracle

Examples related to foreign-keys

Examples related to primary-key