composite key syntax. How can I fix this?

[digituf]

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;
 

[Atli]

Hey.

I assume you are talking about creating a composite primary key; a primary key made up of more than one column? - The syntax to do that is as follows:

PRIMARY KEY ( col1, col2, col3, ..., colN)

Creating foreign key restraints is done seperately, using the FOREIGN KEY syntax. For example, if you wanted to add a foreign key constraint on "col2" in a three-column primary, you might do:

CREATE TABLE `table1`(
    /* Column definitions */,
    PRIMARY KEY ( col1, col2, col3 ),
    FOREIGN KEY ( col2 )
        REFERENCES `table2` ( `some_column` )
)ENGINE=InnoDB;

Note that without the "ENGINE=InnoDB" clause the FOREIGN KEY is pointless. - MyISAM does not enforce foreign keys.

[digituf]

hi atli. can you please explain further this syntax to declare composite key :

PRIMARY KEY ( col1, col2, col3, ..., colN)
 

it seems that the primary key that i wanted to define as composite key is declare in one row 'PRIMARY KEY (col1, col2, col3)', so how can i know that which primary key that supossed to refer to the table that i wanted..

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
 
 

if you see from the above myql syntax, i wanted the 'b_ic_no' refer to the customer table that have 'ic_no' which is the primary key in the table customer...

i'm using phpmyadmin to create this table just for your info..

any explanation is really appreciated...

here's the relationship that i've created in acecss:

[Atli]

Adding a column to a composite primary key does not affect the way you use it to create foreign keys. The column remains unaltered, even though it is a part of the key. - The key only enforces some restrictions on the values the column can take. (Namely that they be unique when combined with the other PK columns.)

By using more than one column as a primary key, you aren't creating multiple keys. You are creating a single primary key made up of all three.

Consider a typical N:M (many-to-many) link table; a table that links rows in one table to rows in another table. It only has to have two columns, one to reference a row in either table, and both serving as the Primary Key. Like:

CREATE TABLE `link_table` (
    `left_id` Int Unsigned Not Null,
    `right_id` Int Unsigned Not Null,
    PRIMARY KEY ( `left_id`, `right_id` ),
    FOREIGN KEY ( `left_id` ) 
        REFERENCES `left_table` ( `id` ),
    FOREIGN KEY ( `right_id` ) 
        REFERENCES `right_table` ( `id` )
)ENGINE=InnoDB;

Say that we link ID #1 on the left to IDs #1 through #5 on the right. The data and the PK for this table could be displayed by doing something like:

mysql> SELECT *,
    ->     CONCAT(`left_id`, '-', `right_id`) AS 'PRIMARY'
    -> FROM `link_table`;
+---------+----------+---------+
| left_id | right_id | PRIMARY |
+---------+----------+---------+
|       1 |        1 | 1-1     |
|       1 |        2 | 1-2     |
|       1 |        3 | 1-3     |
|       1 |        4 | 1-4     |
|       1 |        5 | 1-5     |
+---------+----------+---------+
5 rows in set (0.00 sec)

The PRIMARY key is separate. It doesn't affect how the actual columns are used. The columns themselves are still working as Foreign Keys for their respective tables, just like they would without the primary key restraint.

The point of a composite primary key, just like an primary key, is to make sure each row can be identified by a unique value. The only difference between a composite key and a normal key is that a composite key is based on multiple columns; the unique value that is used to identify the row is created from the values of multiple columns. - This has nothing to do with how the columns are linked to other tables. That is a completely different thing. You can create a foreign key from any column, even one that is a part of a primary key.

Hope that helps clear it up.