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

2022-01-20 00:00:00 database foreign-keys oracle primary-key

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

这会抛出 Oracle (ORA-02270) : no matching unique key or primary key for this column-list error，做一些调查看来我们需要有 unique key 或 primaryUSERID 上的 key 约束，但我不能这样，因为一个 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 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

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

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

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.让我们解决这个问题:

That statement failed because USER is a reserved keyword so 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.

唉，我们在这里无法为您提供更多帮助.您的代码中有错误.你可以在这里发布你的代码，我们中的一个人可以发现你的错误.或者您可以检查自己的代码并自己发现它.

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.

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

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:

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

ORA-02267: column type incompatible with referenced column type

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

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

相关文章