database Oracle (ORA-02270)：此列列表错误没有匹配的唯一键或主键

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

提问by Rachel

I have two tables, Table JOBand Table USER, here is the structure

我有两个表，Table JOB并且Table USER，这里是结构

 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 JOBreferencing to USERtable, as

现在，我想添加外键约束来JOB引用USER表，如

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 keyconstraint on USERIDbut I cannot have that as one USERIDcan have multiple JOBSassociated with him, any thoughts or suggestions on how to fix this issue?

这引发Oracle (ORA-02270) : no matching unique or primary key for this column-list error了一些调查，似乎我们需要对其中任何一个进行unique key or primary key限制，USERID但我不能这样做，因为一个人USERID可以有多个JOBS与他相关的人，关于如何解决此问题的任何想法或建议？

Researched ORA-02270and SO related question

研究了ORA-02270和SO 相关问题

回答by APC

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

ORA-2270 错误是一个简单的逻辑错误：当我们在外键中列出的列与父表上的主键或唯一约束不匹配时，就会发生这种错误。造成这种情况的常见原因是

• 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.

• 父级完全没有 PRIMARY KEY 或 UNIQUE 约束
• 外键子句引用了父表中错误的列
• 父表的约束是一个复合键，我们没有引用外键语句中的所有列。

Neither appears to be the case in your posted code. But that's a red herring, because your code does not runas 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.

在您发布的代码中似乎都不是这种情况。但这是一个红鲱鱼，因为您的代码没有像您发布的那样运行。从之前的编辑来看，我认为您没有发布您的实际代码，而是一些简化的示例。不幸的是，在简化过程中，您已经消除了导致 ORA-2270 错误的任何因素。

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:

该语句失败，因为 USER 是保留关键字，因此我们无法命名表 USER。让我们解决这个问题：

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.

还有！没有 ORA-2270 错误。

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:

注意：早期版本的代码将 HOB.USERID 定义为 VARCHAR2(20)。因为 USER.ID 被定义为 NUMBER，所以尝试创建外键会抛出一个不同的错误：

ORA-02267: column type incompatible with referenced column type

ORA-02267: 列类型与引用的列类型不兼容

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

回答by LSU.Net

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

Job 表 (Varchar2(20)) 中的数据类型与 USER 表中的数据类型 (NUMBER NOT NULL) 不匹配。

回答by Lorenzo Lerate

In my case the problem was cause by a disabled PK.

在我的情况下，问题是由禁用的 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;

1. 我寻找约束名称：

   SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'referenced_table_name';

2. 然后我取了约束名称，以便使用以下命令启用它：

   ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

回答by Navin

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.

希望这会奏效。

回答by Lajos Arpad

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.

方案正确，User.ID必须是User的主键，Job.ID应该是Job的主键，Job.UserID应该是User.ID的外键。此外，您的命令在语法上似乎是正确的。

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:

那么可能有什么问题呢？我相信你至少有一个 Job.UserID，它在 User.ID 中没有一对。例如，如果 User.ID 的所有值都是：1,2,3,4,6,7,8 并且 Job.UserID 的值为 5（不在 1,2,3,4,6 ,7,8，这是 UserID 的可能值），您将无法创建外键约束。解决方案：

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.

将删除用户不存在的所有作业。您可能希望将这些迁移到此表的副本，该副本将包含存档数据。

回答by chetan pawar

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

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:

现在，当我想在章节（具有复合主键）和主题（具有单列主键）之间创建一个名为 Chapter_topic 的关系时，我遇到了以下查询的问题：

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

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

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:

感谢 APC 的帖子，他在帖子中提到了以下声明：

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

造成这种情况的常见原因是
——
父表完全没有约束——父表的约束是一个复合键，我们没有引用外键语句中的所有列。
- 引用的 PK 约束存在但已禁用

回答by codingbiz

Isn't the difference between your declaration of USERID the problem

您声明的 USERID 之间的区别不是问题吗

JOB: UserID is Varchar
USER: UserID is Number?

回答by AConsumer

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

回答by Amy B

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.

引用的表具有匹配类型的主键约束。就我而言，此错误的根本原因是禁用了主键约束。

回答by Daniyar

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

[42000][2270] ORA-02270：此列列表没有匹配的唯一键或主键

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

之后我们使用第一个脚本创建了新表，没有任何问题