SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    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) 
    , 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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 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..
    Attached Images Attached Images

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •