How to add 2 foreign keys in a table in a SQL Server?

Question

I have 2 tables, one of them has 2 foreign keys and another has 2 composite foreign keys in it. Whenever I try to run the query, it says error and could not create constraint or index. So I have two tables here.

I know I'm completely wrong, I apologize for that as I'm kind of new to coding.

create table Booking
(
    BookingID char(4) primary key, 
    dateBooked datetime not null,
    creditCard char(16) null, 
    expiryDate datetime not null,
    CVC char (3) not null,
    confirmationID char(4) not null,
    ticketType varchar(20) 
         foreign key references ticketType(ticketType),
    NRIC char(9) 
         foreign key references Patron(NRIC)
)

create table BookingSeat 
(
    seatNo char(2) not null,
    rowNo char(2) not null,
    date datetime not null,
    startTime time not null,
    rowNo char(2) not null,
    seatNo char(2) not null,

    foreign key (date, startTime) 
         references hallSchedule(date, startTime),
    foreign key (rowNo, seatNo) 
         references Seat(rowNo, seatNo)
)

score 2 · Answer 1 · edited May 23 '17 at 11:59

The foreign key syntax is:

 FOREIGN KEY (addid) REFERENCES Table1_Addr(addid),
 FOREIGN KEY (id) REFERENCES Table1(id)

For Table Booking. Here's what i have noticed NRIC should be on the table before setting it as foreign key:

CREATE TABLE BOOKING 
(BookingID char(4) primary key
, dateBooked datetime not null
, creditCard char(16) null
, expiryDate datetime not null
, CVC char (3) not null
, confirmationID char(4) not null
, ticketType varchar(20) 
, NRIC char(9)    
,FOREIGN KEY (ticketType) REFERENCES ticketType(ticketType)
,FOREIGN KEY (NRIC) REFERENCES Patron(NRIC))

For BookingSeat you don't have a primary key on this table? I noticed that you defined seatNo twice.

CREATE TABLE BookingSeat 
(seatNo char(2) not null
, rowNo char(2) not null
, date datetime not null
, startTime time not null
, rowNo char(2) not null
, FOREIGN KEY (date) REFERENCES hallSchedule(date)
, FOREIGN KEY (startTime) REFERENCES hallSchedule(startTime)
, FOREIGN KEY (rowNo) REFERENCES Seat(rowNo)
, FOREIGN KEY (seatNo) REFERENCES Seat(seatNo))

Please see these links for references: Can a table have two foreign keys? Multiple foreign keys?

Thank you, it helps a lot. I'm finally able to run my query now. — J_fruitty, Jul 28 '16 at 05:04

How to add 2 foreign keys in a table in a SQL Server?

1 Answers1

Linked