mysql - #1005 - 通过 FOREIGN KEY 连接表时无法在 ALTER TABLE 上创建表

Question

我正在做家庭作业。我必须为一家音像店建立一个数据库。以下所有作品:

CREATE TABLE Stock
(
PKStock_ID VARCHAR(8) NOT NULL,
FKTitle VARCHAR(8) NOT NULL,
NoOfDVD INT(10) NOT NULL,
NoOfVHS INT(10) NOT NULL,
PRIMARY KEY (PKStock_ID)
);

CREATE TABLE Inventory
(
PKUnique_ID VARCHAR(8) NOT NULL,
DistributorSerialNo VARCHAR(8) NOT NULL,
Distributor_ID VARCHAR(8) NOT NULL,
FKTitle_ID VARCHAR(8) NOT NULL,
InStock CHAR(1) NOT NULL, 
DateOut TIMESTAMP,
DateBack TIMESTAMP,
Customer_ID VARCHAR(8) NOT NULL,
Rental_Price DECIMAL(4,2) NOT NULL,
PRIMARY KEY (PKUnique_ID)
);

CREATE TABLE Movie
(
PKTitle_ID VARCHAR(8) NOT NULL,
FKTitle_ID VARCHAR(8) NOT NULL,
Title VARCHAR(30),
Genre VARCHAR(8),
YearReleased INT,
Length INT,
PRIMARY KEY (PKTitle_ID)
);

CREATE TABLE Actors
(
PKActor_ID VARCHAR(8) NOT NULL,
FKActor_ID VARCHAR(8) NOT NULL, 
Actors VARCHAR(30),
PRIMARY KEY (PKActor_ID)
);

CREATE TABLE Awards
(
PKAward_ID VARCHAR(8) NOT NULL,
FKAward_ID VARCHAR(8) NOT NULL,
Awards VARCHAR(30),
PRIMARY KEY (PKAward_ID)
);

CREATE TABLE Directors
(
PKDirector_ID VARCHAR(8) NOT NULL,
FKDirector_ID VARCHAR(8) NOT NULL,
Directors VARCHAR(30),
PRIMARY KEY (PKDirector_ID)
);

CREATE TABLE ElectronicCatalogue
(
PKElectronicCatalogue VARCHAR(8) NOT NULL,
FKDistributor_ID VARCHAR(8) NOT NULL,
DistributorSerialNo VARCHAR(8) NOT NULL,
Price DECIMAL(6,2) NOT NULL,
PRIMARY KEY (PKElectronicCatalogue)
);

CREATE TABLE Distributors
(
PKDistributor_ID VARCHAR(8) NOT NULL,
FKDistributor_ID VARCHAR(8) NOT NULL,
NameOfDistributer VARCHAR(30) NOT NULL,
Horror CHAR(1) NOT NULL, 
Drama CHAR(1) NOT NULL, 
Comedy CHAR(1) NOT NULL, 
Action CHAR(1) NOT NULL, 
Thrillers CHAR(1) NOT NULL,
PRIMARY KEY (PKDistributor_ID)
);

CREATE TABLE Customers
(
PKCustomer_ID VARCHAR(8) NOT NULL,
FKUnique_ID VARCHAR(8) NOT NULL,
Name VARCHAR(30),
Address VARCHAR(100),
Phone INT NOT NULL,
PRIMARY KEY (PKCustomer_ID)
);

CREATE TABLE Fees
(
PKFee_ID VARCHAR(8) NOT NULL,
FK_ID VARCHAR(8) NOT NULL,
Damages DECIMAL(10,2) NOT NULL,
Late DECIMAL(10,2) NOT NULL,
PRIMARY KEY (PKFee_ID)
);

ALTER TABLE Stock
ADD FOREIGN KEY (FKTitle)
REFERENCES Inventory(PKUnique_ID);

ALTER TABLE Movie
ADD FOREIGN KEY (FKTitle_ID)
REFERENCES Stock (PKStock_ID);

ALTER TABLE Actors
ADD FOREIGN KEY (FKActor_ID)
REFERENCES Movie (PKTitle_ID);

ALTER TABLE Awards
ADD FOREIGN KEY (FKAward_ID)
REFERENCES Movie (PKTitle_ID);

ALTER TABLE Directors
ADD FOREIGN KEY (FKDirector_ID)
REFERENCES Movie (PKTitle_ID);

ALTER TABLE ElectronicCatalogue
ADD FOREIGN KEY (FKDistributor_ID)
REFERENCES Inventory (PKUnique_ID);

ALTER TABLE Distributors
ADD FOREIGN KEY (FKDistributor_ID)
REFERENCES ElectronicCatalogue (PKElectronicCatalogue);

接下来我想将 Inventory 表连接到 customers 表。当我执行以下操作时:

ALTER TABLE Customers
ADD FOREIGN KEY (FKUnique_ID)
REFERENCES Inventory (Customer_ID);

我收到这个错误:

#1005 - Can't create table 'mm.#sql-9f69_110' (errno: 150)

我做错了什么？

Answer 1

外键应指向唯一列(主键或唯一)。您的库存 (Customer_ID) 不是唯一的。

我认为您正在尝试:

ALTER TABLE Inventory
ADD CONSTRAINT fk1_Inv FOREIGN KEY (Customer_ID)
REFERENCES Customers (PKCustomer_ID);