mysql ora-02270:此列列表的唯一关键字或主键不匹配,Oracle（ORA-02270）：此列列表错误没有匹配的唯一或主键...

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?

解决方案

The ORA-2270 error is quite simple: it happens when the columns we reference 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 constraint altogether

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.

Because, if we fix your code so it runs, it - er - runs. All the way.

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>

So, that statement failed because USER is a reserved keyword, and 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.

So, 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.