Oracle（ORA-02270）：此列列表错误没有匹配的唯一或主键 [英] Oracle (ORA-02270) : no matching unique or primary key for this column-list error

问题描述

我有两个表，表JOB 和表USER ，这里是结构

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

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

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）：没有匹配的唯一或主键对于此列列表错误，做一些调查，似乎我们需要唯一键或主键约束 USERID ，但我不能拥有 USERID 可以有多个 JOBS ，有关如何解决此问题的任何想法或建议吗？

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?

研究 ORA-02270 和 SO相关问题

推荐答案

ORA-2270错误很简单：当我们在外键中引用的列不匹配主键时或父表上的唯一约束。这种情况的常见原因是

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

• 父项缺少约束


• 父表的约束是复合键，我们没有引用外键语句中的所有列。

您的发布代码。但这是一个红色的鲱鱼，因为你的代码不会运行，因为你发布它。从以前的编辑判断，我推测你不是发布你的实际代码，但一些简化的例子。不幸的是在简化过程中，你已经根除了导致ORA-2270错误的任何原因。

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> 

因此，该语句失败，因为USER是保留关键字，我们不能为USER表命名。让我们修正：

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> 

没有ORA-2270错误。

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.

这篇关于Oracle（ORA-02270）：此列列表错误没有匹配的唯一或主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！