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

ora-02270 no matching unique or primary key for this column-list solution
oracle unique constraint
oracle add primary key
how to check unique constraint in oracle
foreign key in oracle
composite foreign key oracle
ora-02256: number of referencing columns must match referenced columns
how to insert data in table with foreign key in oracle

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

Oracle (ORA-02270) : no matching unique or primary key for this , The error has occurred because you are referencing a column in another table which is not unique. The good answers are already given by  ORA-02270: no matching unique or primary key for this column-list Cause You tried to reference a table using a unique or primary key , but the columns that you listed did not match the primary key, or a primary key does not exist for this table.

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

ORA-02270: no matching unique or primary key for this column-list , In order to give foriegn key constraint to a column in a table there are few Why do we get ORA-02270: no matching unique or primary key for this column-list error in The PARENT reference column should be PRIMARY KEY or UNIQUE KEY constraint can only reference a primary key or unique key column in oracle . Conclusion: A foreign key must always refer to a column or columns declared as either PRIMARY KEY or UNIQUE in Oracle. improve this answer. edited Dec 18 '17 at 8:36. answered Feb 27 '16 at 18:23. 10 silver badges. 24 bronze badges. Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the

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.

no matching unique or primary key for this column-list error , RA-02270: no matching unique or primary key for this column-list SQL Language (MOSC) Oracle Database (MOSC) SQL Language (MOSC) the impdp doesn'​t show any issue but at the moment to enable it I"m getting these errors ORA-​02270: no matching unique or primary key for this column-list. In my opinion, Reason is: ACCREDITATION_BODY_ID must be a primary key in order to be referenced as a foreign key, but that's not the case here. Your table's constraint is a compound key , and you need to reference all the columns in the foreign key statement.

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.

RA-02270: no matching unique or primary key for this column-list, ORA-39083: Object type REF_CONSTRAINT failed to create with error: ORA-​02270: no matching unique or primary key for this column-list Oracle DB can not create Table Showing “ORA-02270: no matching unique or primary key for this column-list” 3 “no matching unique or primary key for this column-list”.

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;

IMPDP Fails With ORA-39083, ORA-02270 Where , Error SQL: ORA-02270: no matching unique or primary key for this column-list Oracle 11g. 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

Error SQL: ORA-02270: no matching unique or primary , ORA-02270: no matching unique or primary key for this column-list : FOREIGN KEY « Table « Oracle PL/SQL Tutorial. 1. re:ORA-02270 no matching unique or primary key for this column list 287693 Jun 6, 2002 6:26 AM ( in response to 276609 ) The key that you are trying to refer must have primary key or unique constraint applied on it.

ORA-02270: no matching unique or primary key for this column-list, Annals of Oracle's Improbable Errors ORA-02270: no matching unique or primary key for this column-list. If you see this error, it simply means that the target table (the table name following the REFERENCES keyword) lacks  Re: ORA-02270: no matching unique or primary key for this column-list Saubhik Jul 18, 2011 8:01 AM ( in response to Tony007 ) > Enforcing Referential Integrity with Constraints Whenever two tables contain one or more common columns, Oracle Database can enforce the relationship between the two tables through a referential integrity constraint.

Oracle Solution for Error ora-02270, ORA-02270: no matching unique or primary key for this column-list. What triggered the Error: A REFERENCES clause in a CREATE/ALTER  When i add all three columns as foreign key then able to create table. Is there any way only add emp_no as foreign key. Please suggest us. ORA-02270: No matching unique or Primary key for this column list. Class : A references clause in a created or Alter table statement gives a column list for which there is no matching unique or primary key

Comments
  • 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).
  • @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.
  • reason behind this error is - ID column of User table is not not unique or primary key
  • "...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.
  • i should have mentioned it upfront, both id as well as userid are number type.
  • 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.
  • 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);
  • i have rectified the error in question, both userid as well as id are numbers