#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    13
    Rep Power
    0
    here I have a question,
    can one table consists of 2 primary key ? thank you.
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    You can't have two primary keys in a single table.

    Only one primary key is allowded in a single table.



    ------------------
    SR -
    shiju.dreamcenter.net
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    13
    Rep Power
    0
    i know one table can have 2 index keys but can't have 2 primary key. But in mysql how to add index key.

    for example:
    State lookup table:
    (3 fields:country_id,state_id,statedesc)
    data:

    record 1('sg','st1','Happy State')
    record 1('sg','st2','Perfect State')
    record 1('sg','st3','Hello State')
    record 1('tw','st1','Apple State')
    record 1('tw','st2','Orange State')
    record 1('tw','st3','Star State')
    record 1('tw','st4','Bird State')

    I want to set country_id and state_id as index key. how to do ? thank you.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    Heerlen, The Netherlands
    Posts
    24
    Rep Power
    0
    You can have a primary key consisting of two (or more) columns though. Have a look at http://www.mysql.com/Manual_chapter/...l#CREATE_TABLE

    Hope this helps!

    Greetz,
    Peter



    [This message has been edited by WuzWuz (edited May 05, 2000).]
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    Make a composite primary key when you create the table.

    > create table state_lookup(
    > country_id varchar(10) not null,
    > state_id varchar(10) not null,
    > statedesc varchar(50),
    > primary key(country_id, state_id));
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    13
    Rep Power
    0
    Thanks for your helps
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    1
    Rep Power
    0
    Originally Posted by Kyuzo
    Make a composite primary key when you create the table.

    > create table state_lookup(
    > country_id varchar(10) not null,
    > state_id varchar(10) not null,
    > statedesc varchar(50),
    > primary key(country_id, state_id));

    Hi,

    I have create a table with two primary key but i want to use one of them in one table and another primary key in another table. Like this
    create table counrty
    (
    countryId int not null auto_increment,
    countryName varchar(20) not null,
    countryCode varchar(20) not null,
    primary key(countryId, countryCode)
    )

    Here this country table got created but when i am trying to use one primary key in state table then its showing me 1005 error. Here below is the query.

    create table state
    (
    stateId int not null auto_increment,
    stateName varchar(20) not null,
    stateCode varchar(20) not null,
    countryCode varchar(20) not null,
    primary key(stateId),
    foreign key(countryCode) references country(countryCode)
    )

    While i am trying to execute this state query then i am facing this 1005 error. Please help me regarding this problem.

    Thanks
    Sailendra Narayan Jena
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    first of all, terminology -- a table can have only one primary key, but it can be a composite key consisting of more than one column

    i think giving the country table a composite PK of id and code is wrong

    if you want to use an auto_increment (which i think is wrong), you should make it the PK and then make countrycode UNIQUE so that it can be referenced by a FK in the state table

    in my opinion your country table should have countrycode as PK and no auto_increment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Similar Threads

  1. autonumber primary key
    By densma in forum MS SQL Development
    Replies: 7
    Last Post: March 8th, 2005, 01:46 PM
  2. newbie with postgres
    By edwinx in forum PostgreSQL Help
    Replies: 5
    Last Post: October 9th, 2003, 06:21 PM
  3. mouseover a table rows
    By robin01 in forum HTML Programming
    Replies: 3
    Last Post: April 4th, 2003, 12:35 AM
  4. getting id from one table, placing in another
    By davaby in forum MySQL Help
    Replies: 2
    Last Post: June 19th, 2002, 09:05 AM
  5. Replies: 4
    Last Post: May 26th, 2001, 11:53 AM

IMN logo majestic logo threadwatch logo seochat tools logo