Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

composite key syntax. How can I fix this?

syntax

  • Please log in to reply
4 replies to this topic

#1 faith89

faith89

    CC Newcomer

  • Just Joined
  • PipPip
  • 14 posts

Posted 27 March 2010 - 10:15 AM

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;

  • 0

#2 Arctic Fire

Arctic Fire

    CC Regular

  • Member
  • PipPipPip
  • 37 posts

Posted 28 March 2010 - 03:06 AM

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;

  • 0

#3 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 28 March 2010 - 05:40 AM

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.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#4 Arctic Fire

Arctic Fire

    CC Regular

  • Member
  • PipPipPip
  • 37 posts

Posted 28 March 2010 - 06:02 AM

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.
  • 0

#5 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 28 March 2010 - 08:56 AM

Good point :)
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/






Also tagged with one or more of these keywords: syntax

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download