mysql - 错误消息 "ORA-02270: no matching unique or primary key for this column-list"

Question

我在将内容映射到单独的数据表上时遇到问题。将有 DEPARTMENT_LOCATIONS、DEPARTMENT、EMPLOYEE 和 PROJECT 表。到目前为止，除了项目之外，所有表都是创建的，因为我收到了问题标题的错误消息。

下面是我用来创建表的信息，创建的表没有错误:

员工表:

CREATE TABLE EMPLOYEE
(FNAME VARCHAR(25) NOT NULL,
MINIT CHAR(1),
LNAME VARCHAR(25) NOT NULL,
SSN NUMBER(10) NOT NULL,
BDATE DATE NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
SEX CHAR(1) NOT NULL,
SALARY DECIMAL(6,2) NOT NULL,
SUPERSSN NUMBER(10),
DNO NUMBER (1) NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (DNO) REFERENCES DEPT_LOCATIONS(DNUMBER));

DEPARTMENT_LOCATIONS 表:

CREATE TABLE DEPT_LOCATIONS
(DNUMBER NUMBER(1) NOT NULL,
DLOCATION VARCHAR(25) NOT NULL,
PRIMARY KEY (DNUMBER));

部门表:

CREATE TABLE DEPARTMENT
(DNUMBER NUMBER(1) NOT NULL,
DNAME VARCHAR(25) NOT NULL,
MGRSTARTDATE DATE NOT NULL,
MGRSSN NUMBER(10) NOT NULL,
PRIMARY KEY (DNUMBER),
FOREIGN KEY (DNUMBER) REFERENCES DEPT_LOCATIONS(DNUMBER),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN));

现在，当我为 PROJECT 表输入以下信息时，我收到错误消息“ORA-02270:此列列表没有匹配的唯一键或主键。”

CREATE TABLE PROJECT
(PNUMBER NUMBER(2,1) NOT NULL PRIMARY KEY,
PNAME VARCHAR(25) NOT NULL,
PLOCATION VARCHAR(25) NOT NULL,
DNUM NUMBER(1) NOT NULL,
FOREIGN KEY (PLOCATION) REFERENCES DEPT_LOCATIONS(DLOCATION),
FOREIGN KEY (DNUM) REFERENCES DEPT_LOCATIONS(DNUMBER));

为什么会出现此错误？我需要采取什么措施来纠正它？谢谢。

Answer 1

您必须将 dlocation 声明为唯一，以便在另一个表中引用它。

CREATE TABLE DEPT_LOCATIONS
(DNUMBER NUMBER(1) NOT NULL,
DLOCATION VARCHAR(25) UNIQUE,
PRIMARY KEY (DNUMBER));