“此列列表没有匹配的唯一键或主键".主键确实存在 [英] "no matching unique or primary key for this column-list". The primary key does exist though

问题描述

所以我正在为测试练习一些sql编码，但是我无法获得外键来引用主键.

So i'm practicing some sql coding for a test and I can't get a foreign key to reference a primary key.

这是不起作用的表:

CREATE TABLE ASSIGNMENT(
ASSIGN_ID NUMBER(2) NOT NULL,
START_DATE DATE,
END_DATE DATE,
BUDGET NUMBER (10,2),
MANAGER_ID NUMBER(2),
  PRIMARY KEY (ASSIGN_ID,MANAGER_ID),
  FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE(EMP_ID)
);

这是它所引用的表:

CREATE TABLE EMPLOYEE(
EMP_ID NUMBER(2) NOT NULL,
NAME VARCHAR(40),
OFFICE VARCHAR(20),
EXPERT_ID NUMBER(2),
  PRIMARY KEY (EMP_ID,EXPERT_ID),
  FOREIGN KEY (EXPERT_ID) REFERENCES EXPERTISE(EXPERT_ID)
);

每当我尝试运行该脚本时，它总是会返回:

Whenever I try to run the script it always comes back with:

错误报告-
SQL错误:ORA-02270:此列列表没有匹配的唯一键或主键
02270. 00000-此列列表没有匹配的唯一键或主键"
*原因:CREATE/ALTER TABLE语句中的REFERENCES子句
给出没有匹配的唯一或主列的列列表 引用表中的关键约束.
*操作:使用ALL_CONS_COLUMNS查找正确的列名称 目录视图

Error report -
SQL 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 catalog view

我环顾四周，但似乎找不到问题.任何帮助将不胜感激.

I've looked around but can't seem to find the problem. Any help would be appreciated.

这是完整的代码(尚未测试最后一张表):

Here's the full code (haven't tested the last table yet):

CREATE TABLE EXPERTISE(
EXPERT_ID NUMBER(2) NOT NULL,
DESCRIPTION VARCHAR(50),
HOURLY_RATE NUMBER(3,2),
CHARGE_RATE NUMBER(3,2),
  PRIMARY KEY(EXPERT_ID)
);

CREATE TABLE EMPLOYEE(
EMP_ID NUMBER(2) NOT NULL,
NAME VARCHAR(40),
OFFICE VARCHAR(20),
EXPERT_ID NUMBER(2),
  PRIMARY KEY (EMP_ID,EXPERT_ID),
  FOREIGN KEY (EXPERT_ID) REFERENCES EXPERTISE(EXPERT_ID)
);

CREATE TABLE ASSIGNMENT(
ASSIGN_ID NUMBER(2) NOT NULL,
START_DATE DATE,
END_DATE DATE,
BUDGET NUMBER (10,2),
MANAGER_ID NUMBER(2),
  PRIMARY KEY (ASSIGN_ID,MANAGER_ID),
  FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE(EMP_ID)
);


CREATE TABLE ALLOCATION(
EMP_ID NUMBER(3) NOT NULL,
ASSIGN_ID NUMBER(3) NOT NULL,
DAYS_WORKED_ON DATE,
HOURS_WORKED_ON DATE,
  PRIMARY KEY(EMP_ID,ASSIGN_ID),
  FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(EMP_ID),
  FOREIGN KEY(ASSIGN_ID) REFERENCES ASSIGNMENT(ASSIGN_ID)
);

我正在使用Oracle SQL Developer来实现

I'm using Oracle SQL Developer to make it

推荐答案

*原因:CREATE/ALTER TABLE语句中的REFERENCES子句提供了没有匹配的唯一键或主键的列列表 约束在引用表中.

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

问题在于EMP_ID(本身)不是表Employees的主键或唯一键，而是具有复合主键(EMP_ID, EXPERT_ID).

The problem is that EMP_ID (by itself) isn't a primary or unique key of table Employees, instead, you have a compound primary key (EMP_ID, EXPERT_ID).

要解决该问题，可以将EMP_ID用作Employees表的主键(这似乎很直观，因为每个员工都应该具有唯一的ID)，或者在EMP_ID上添加单独的唯一约束.

To fix the issue either make EMP_ID the primary key of the Employees table (which seems intuitive as each employee ought to have a unique id) or add a separate unique constraint on EMP_ID.

正如注释中指出的那样，如果将EMP_ID用作主键，则(EMP_ID, EXPERT_ID)在扩展方面也将是唯一的.

As pointed out in the comments, if you make EMP_ID the primary key, then (EMP_ID, EXPERT_ID) will also be unique by extension.

这篇关于“此列列表没有匹配的唯一键或主键".主键确实存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！