CODEWITHSUNDEEP
sqlsql-server
Xunrui Chow
Xunrui Chow

Reputation: 41

More than one key specified in column level FOREIGN KEY constraint

I am doing a database project for my school and I stumble upon this strange issue that I can't seem to solve it. Here is the code i have when trying the create the tables:

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)
);

The error message said that:

More than one key specified in column level FOREIGN KEY constraint

I am really confused by the problem, I tried to create multiple foreign keys that references to that many primary key of the parent table.

Really hope that this can be fixed, thank you

Upvotes: 2

Views: 6674

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

You are missing commas. Try:

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)
);

You can try the following, naming the foreign key's:

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)
);

Upvotes: 13

L&#233;o R.
L&#233;o R.

Reputation: 2708

The CREATE TABLE statement is missing a comma before each CONSTRAINT

Upvotes: 1

Related Questions