sql - "no matching unique or primary key for this column-list"。主键确实存在

Question

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

这是不起作用的表:

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

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

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

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

完整代码如下(最后一张表还没有测试):

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 来制作它

Answer 1

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

要解决此问题，请将 EMP_ID 设置为 Employees 表的主键(这看起来很直观，因为每个员工都应该有一个唯一的 ID)或添加一个单独的唯一EMP_ID 的约束。

正如评论中所指出的，如果您将 EMP_ID 设为主键，则 (EMP_ID, EXPERT_ID) 也将是唯一的。