TalkPHP - composite key syntax. How can I fix this?

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - composite key syntax. How can I fix this? (http://www.talkphp.com/mysql-databases/5376-composite-key-syntax-how-can-i-fix.html)

fairytale89

03-27-2010 06:16 PM

composite key syntax. How can I fix this?

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 :

Code:

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;

delayedinsanity

03-27-2010 07:46 PM

You can't have more than one primary key, thus what makes a primary key primary.

Village Idiot

03-27-2010 08:30 PM

A primary key is what the database uses to identify the row. While you do not have to have one, it makes looking up a particular row a lot faster becuase the database expects you to find it via the primary key. However, it is a unique ID and there can only be one per row.

fairytale89

03-27-2010 11:55 PM

1 Attachment(s)

isn't that primary key that referring to another primary key in another table is called as 'composite key'?
i'm not not really agree when you said that i'm actually wanted to declare is a unique ID. 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..

Village Idiot

03-31-2010 04:19 PM

Quote:

Originally Posted by fairytale89 (Post 30339)

A primary key can never refer to another table, it is used exclusively to identify each row. What you are looking for is a foreign key, use google to lean about those.

All times are GMT. The time now is 02:17 PM.