More than one key specified in column level FOREIGN KEY constraint

Question

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

That message matches a SQL Server one so I've added that tag. If that's incorrect, please remove that but add a tag specific to your database system, as the sql tag already told you to do. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 8, 2018 at 10:31

HoneyBadger · Accepted Answer · 2018-02-08 10:31:08Z

13

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

edited Feb 8, 2018 at 10:31

answered Feb 8, 2018 at 10:21

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Xunrui Chow Over a year ago

It still does not seem to work for me, let me try to give a full context of the question this time.

HoneyBadger Over a year ago

When you say "it still doesn't work", does that mean you get the same error, or a new one?

Léo R. · Accepted Answer · 2018-02-08 10:23:55Z

1

The CREATE TABLE statement is missing a comma before each CONSTRAINT

answered Feb 8, 2018 at 10:23

Léo R.

2,7161 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

More than one key specified in column level FOREIGN KEY constraint

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related