4 replies to this topic

[faith89]

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;

[Arctic Fire]

You can only have one primary key per table. And, you had a comma where it wasn't suppose to be.

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 ) ,
FOREIGN KEY ( b_room_no ) REFERENCES room( room_no ) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE = INNODB;

[WingedPanther]

It depends on the type of database. Most databases allow multiple fields in a primary key, and allow setting one or more of those fields as foreign keys. The details, as always, depends on the type of database.

[Arctic Fire]

WingedPanther said:

It depends on the type of database. Most databases allow multiple fields in a primary key, and allow setting one or more of those fields as foreign keys. The details, as always, depends on the type of database.

True, but that's multiple columns for the key. You would set it up as one primary key with multiple columns, not multiple primary keys.

[WingedPanther]

Good point :)