sql - 在列级 FOR​​EIGN KEY 约束中指定了多个键

Question

我正在为我的学校做一个数据库项目，我偶然发现了这个我似乎无法解决的奇怪问题。这是我尝试创建表时的代码:

Create table Offering(
StartDate       DATE            NOT NULL    ,       
PRIMARY KEY(StartDate) 
);

Create table OfferPerWeek(
StartDate       DATE            NOT NULL    ,
StartTime       TIME(0)         NOT NULL    ,
[Day]           VARCHAR(10)     NOT NULL
PRIMARY KEY (StartDate,[Day],StartTime)
FOREIGN KEY (StartDate) 
REFERENCES Offering(StartDate)
);

--The table that have the issues 
Create table OfferPerWeek_Venue(  
[Day]           VARCHAR(10)     NOT NULL    ,
StartDate       DATE            NOT NULL    , 
StartTime       TIME(0)         NOT NULL    
PRIMARY KEY (StartDate, [Day], StartTime)
FOREIGN KEY (StartDate, [Day], StartTime) 
REFERENCES OfferPerWeek (StartDate, [Day] , StartTime)
);

错误信息是这样说的:

More than one key specified in column level FOREIGN KEY constraint

我真的被这个问题弄糊涂了，我试图创建多个外键来引用父表的那么多主键。

希望能解决这个问题，谢谢

Answer 1

您缺少逗号。尝试:

Create table OfferPerWeek_Venue(  
[Day]           VARCHAR(10)     NOT NULL    ,
StartDate       DATE            NOT NULL    , 
StartTime       TIME(0)         NOT NULL , <-- comma there  
PRIMARY KEY (StartDate, [Day], StartTime), <-- comma there
FOREIGN KEY (StartDate, [Day], StartTime) 
REFERENCES OfferPerWeek (StartDate, [Day] , StartTime)
);

您可以尝试以下方法，命名外键:

Create table OfferPerWeek_Venue(  
[Day]           VARCHAR(10)     NOT NULL    ,
StartDate       DATE            NOT NULL    , 
StartTime       TIME(0)         NOT NULL ,  
CONSTRAINT [PK_GoodName] PRIMARY KEY (StartDate, [Day], StartTime), 
CONSTRAINT [FK_GoodName] FOREIGN KEY (StartDate, [Day], StartTime) 
REFERENCES OfferPerWeek (StartDate, [Day] , StartTime)
);