MySQL错误1005 errno 150外键约束格式不正确

Question

我创建了下表:

停靠点:

CREATE TABLE Stop (  
 routeNo        DECIMAL (4,0) UNSIGNED,  
 stopNo         DECIMAL(3,0) UNSIGNED,  
 latitude       DECIMAL(19,16),  
 longitude      DECIMAL(19,16),  
 CONSTRAINT PK_Location PRIMARY KEY (routeNo, stopNo),  
 CONSTRAINT FK_stop_location FOREIGN KEY (latitude,longitude) REFERENCES Location(latitude,longitude),  
 CONSTRAINT FK_stop_route FOREIGN KEY (routeNo) REFERENCES Route(routeNo)  
);  

以及 WorkDay 表:

CREATE TABLE Workday (
 theDate    DATE    PRIMARY KEY,
 notes  VARCHAR(30)
);  

然后，我尝试使用以下代码创建表 Order Delivery:

CREATE TABLE OrderDelivery 
(
 routeNo DECIMAL(4,0) UNSIGNED,
 stopNO DECIMAL(3,0) UNSIGNED, 
 orderNo INT(9),
 CONSTRAINT pk_Orders PRIMARY KEY (routeNo, stopNo, orderNo),
 deliveryDateExpected DATE, 
 deliveryTime TIME,
 customerName VARCHAR(40),
 creditCardNo CHAR(16) NOT NULL,
 customerRanking ENUM('platinum','gold','occasional','one-off'),
 CONSTRAINT fk_Routes FOREIGN KEY (routeNo) REFERENCES Stop(routeNo),
 CONSTRAINT fk_Stops FOREIGN KEY (stopNo) REFERENCES Stop(stopNo),   
 CONSTRAINT fk_Dates FOREIGN KEY (deliveryDateExpected) REFERENCES Workday(theDate)
);

但是，每次我尝试创建 OrderDelivery 表时，都会出现外键格式不正确的错误

"SQL Error (1005): Can't create table 'harry.OrderDelivery' (errno:150)Foreign key constraint is incorrectly formed"

我该如何纠正这个问题？

Answer 1

您无法为具有多个主键的表创建仅引用该主键的一个字段的外键。

所以你的问题出在这里:

CONSTRAINT fk_Routes FOREIGN KEY (routeNo) REFERENCES Stop(routeNo),
CONSTRAINT fk_Stops FOREIGN KEY (stopNo) REFERENCES Stop(stopNo),  

你应该将其更改为

 CONSTRAINT fk_Stops_Routes 
    FOREIGN KEY (routeNo, stopNo) 
    REFERENCES Stop(routeNo, stopNo),

或者根据您的要求，您必须更改模型以满足您的需求。