mysql - 简单外键/创建 View 问题

Question

我做错了什么？我需要创建外键，但以 CONSTRAINT 开头的代码不知何故是错误的，我的观点也需要帮助我认为我做的子查询是错误的你能帮我吗？

CREATE TABLE dealer(
dealershipId CHAR(10),
phone CHAR(10),
dealershipName CHAR(10),
webAddress char (10),
street CHAR (10),
city char (10),
zip char (5),
mCapacity INT,
managerFName CHAR (10),
managerLName CHAR (10),
PRIMARY KEY (dealershipId));

CREATE TABLE automobiles(
autoId CHAR (4),
vehiclecode VARCHAR (15),
manufacturer VARCHAR(15),
model VARCHAR (20),
style VARCHAR (5),
color VARCHAR (10),
ownership VARCHAR (8),
PRIMARY KEY (autoId)
CONSTRAINT fkHasRep FOREIGN KEY (dealershipId)
REFERENCE dealer(dealershipId) ON DELETE RESTRICT
);

--7.
CREATE VIEW division AS
SELECT dealershipName, webAddress, phone
FROM dealer
WHERE  dealershipId IN
( SELECT  manufacturer, model, style, ownership
FROM automobiles);

--8.
SELECT phone, manufacturer, model
FROM division;

Answer 1

您缺少主 Ked 和外键之间的逗号:

CREATE TABLE automobiles(
autoId CHAR (4),
vehiclecode VARCHAR (15),
manufacturer VARCHAR(15),
model VARCHAR (20),
style VARCHAR (5),
color VARCHAR (10),
ownership VARCHAR (8),
PRIMARY KEY (autoId),
CONSTRAINT fkHasRep FOREIGN KEY (dealershipId)
REFERENCE dealer(dealershipId) ON DELETE RESTRICT
);

MySQL View 不允许在 View 中使用子查询(错误 #1349)- restrictions are documented here .因此，将其转换为使用 JOIN 代替:

CREATE VIEW division AS
   SELECT d.dealershipName, d.webAddress, d.phone, a.*
     FROM DEALER d
     JOIN AUTOMOBILES a ON d.dealershipid IN (a.manufacturer, 
                                              a.model, 
                                              a.style, 
                                              a.ownership);