database - 甲骨文 (ORA-02270) : no matching unique or primary key for this column-list error

Question

我有两个表，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
 );

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

Alter Table JOB ADD CONSTRAINT FK_USERID FOREIGN KEY(USERID) REFERENCES USER(ID);

这会抛出 Oracle (ORA-02270) : no matching unique or primary key for this column-list error，进行一些调查后我们似乎需要有 unique key 或 primary USERID 上的 key 约束，但我不能拥有它，因为一个 USERID 可以有多个 JOBS 与他相关联，任何想法或建议如何解决这个问题？

已研究 ORA-02270和 SO related question

Answer 1

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

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

在您发布的代码中似乎都不是这种情况。但这是一个转移注意力的问题，因为您的代码没有像您发布的那样运行。从之前的编辑来看，我认为您没有发布您的实际代码，而是发布了一些简化的示例。不幸的是，在简化过程中，您已经消除了导致 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> 

该语句失败，因为 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> 

瞧!没有 ORA-2270 错误。

唉，我们能做的不多，无法进一步帮助您。您的代码中有错误。您可以在此处发布您的代码，我们中的一个可以发现您的错误。或者您可以检查自己的代码并自行发现。

---

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

ORA-02267: column type incompatible with referenced column type

避免不匹配的一种简单方法是使用外键语法来默认列的数据类型:

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