Composite key syntax. How can I fix this?

Databases
1

i wanted to create a table name booking that have 3 composite key where 2
of the composite key is referring to each another table named customer and room.

however when i wanted to create the table, it give me error. can someone tell me
how can i fix this.

Here’s the command :


CREATE TABLE booking(
bookingID INT NOT NULL AUTO_INCREMENT ,
checkin DATETIME,
checkout DATETIME,
nights INT,
totalprice INT,
b_ic_no VARCHAR(30),
b_room_no INT,
PRIMARY KEY ( bookingID) ,
PRIMARY KEY ( b_ic_no ) REFERENCES customer( ic_no ) ,
PRIMARY KEY ( b_room_no ) REFERENCES room( room_no ),
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB;
2

it looks like instead of a composite primary key consisting of several columns, what you really want is a primary key consisting of only one column

the other two should actually be FOREIGN KEYs instead

also, the ON UPDATE CASCADE ON DELETE CASCADE is not a specification on its own, it is specified for each foreign key separately

so you should change your table as follows –

CREATE TABLE booking
( bookingID INT NOT NULL AUTO_INCREMENT 
, checkin DATETIME
, checkout DATETIME
, nights INT
, totalprice INT
, b_ic_no VARCHAR(30)
, b_room_no INT
, PRIMARY KEY ( bookingID) 
, FOREIGN KEY ( b_ic_no ) 
      REFERENCES customer( ic_no ) 
        ON UPDATE CASCADE ON DELETE CASCADE
, FOREIGN KEY ( b_room_no ) 
      REFERENCES room( room_no )
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB
3

isn’t that primary key that referring to another primary key in another table is called as ‘composite key’?
i’m not not really aggree when you said that i’m actually wanted to declare is a foreign key. or, am i wrong in understanding this ‘composite key’ term? if so, someone please explain to me…
i’m sorry, i’m not expert in database i just started to learn it and stil learn about it.

however, here’s the relationship below and i really thinks what i’m trying to achieve is not foreign key but composite key…

4

your diagram does show three columns as a composite primary key

but that’s not necessary, because the booking_id is an auto_increment – it’s guaranteed to be unique all by itself