1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Create table with primary key using two foreign keys

Discussion in 'MySQL' started by piropeator, Mar 10, 2016.

Thread Status:
Not open for further replies.
0
  1. #1
    I have two tables:
    Table1
    idproduct (PK)
    des_product

    Table2
    idclient (PK)
    des_client

    I need create a table like this:
    Table3
    idproduct (FK)(PK)
    idclient (FK)(PK)
    des_anything

    So, what is the code sql for create this new table?
    Thanks.
     
    piropeator, Mar 10, 2016 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I think I'd still have a traditional primary key and have the two id columns as foreign keys.
     
    sarahk, Mar 10, 2016 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    jestep, Mar 29, 2016 IP
  4. Bitpalace

    Bitpalace Greenhorn

    Messages:
    53
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    13
    #4
    You can do composite indexes, but that is not what the user wants. The user's requirement cannot be met, because MySQL allows only one primary key per table, and this must have the name PRIMARY. It is impossible to have two primary keys. That's one reason for that it is called "primary", because if you had two, one could not be primary, but secondary to primary.
     
    Bitpalace, Mar 29, 2016 IP
    sarahk likes this.
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    Based on his table design, Table3 doesn't require a primary key other than the composite one.

    I can make a table:

    CREATE TABLE Table3 (
    idproduct INT,
    idclient INT, PRIMARYKEY(idproduct, idclient ))

    And then add foreign key constraints on idproduct and idclient. This would act as a reference for N:M relationships between Table1 and Table2.
     
    jestep, Apr 7, 2016 IP
Thread Status:
Not open for further replies.