Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: Multicolumn primary Key & Referential Int.

    I'm trying out this database layout under MS SQL (de), with primary multicolomn key, but ....


    Let's say table A, has primary KEY on the field PK_A
    Let's say table B, has primary KEY on the field PK_B

    Now table C, has fields ID_A and ID_B, defined as multicolomn primary key, referencing obviuosly to the PK_A and PK_B values.



    NOW THE PROBLEM !!

    Let present you table D , with his beautiful field ID_C.
    How can I succed in referencing with the ID_C field the primary multicolomn key of table C ??

    I' designing the whole thing with access97+mde, but dragging the PK key of table C (ID_A & ID_B) over the table D (ID_C) doesn't work !!

    Any help ??

    Thanks in advance !!

    H2O

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Multicolumn primary Key & Referential Int.

    Have a surrogate key on Table C.

  3. #3
    Join Date
    Oct 2003
    Posts
    24

    Re: Multicolumn primary Key & Referential Int.

    Originally posted by joejcheng
    Have a surrogate key on Table C.
    Sure but .........this is a work around !!!

    multicolomn key is there.. and I would like to know if there exist a way to state a SQL constrain in TableD like :

    CREATE TABLE TableD (
    .....
    FOREIGN KEY(ID_C) REFERENCES TableC(ID_A, IDB)

    )

    H2O

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hello H2O
    How can I succed in referencing with the ID_C field the primary multicolomn key of table C ??
    that's easy -- you can't

    if tableD is to be a child of tableC, then tableD must have a multi-column foreign key to the multi-column primary key of tableC

    note that tableD is allowed to have its own primary key -- the primary key does not have to be the foreign key too

    so you will need something like this --

    CREATE TABLE TableD
    ( ...
    , ID_CA ...
    , ID_CB ...
    , FOREIGN KEY(ID_CA, ID_CB)
    REFERENCES TableC(ID_A, IDB)
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    24
    Originally posted by r937
    hello H2Othat's easy -- you can't

    ... thanks !!

    ---
    so you will need something like this --

    CREATE TABLE TableD
    ( ...
    , ID_CA ...
    , ID_CB ...
    , FOREIGN KEY(ID_CA, ID_CB)
    REFERENCES TableC(ID_A, IDB)
    )
    Ok... so following your solution, in a join clause, the match would be expressed like:
    SELECT *
    FROM TableC JOIN TableD ON ( ( ID_A=ID_CA) AND (ID_B=ID_CB) )

    Right ?


    I'm wondering if using multicolomn primary key is so userfull.
    Despite Codd0s therorem (simple, stable, minimal) M.C. primary keys seems less simplier to use....


    H2O

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    useful? yes

    less simple to use? that is debatable

    i guess the alternative is a surrogate key

    if you have been on these database forums for a while, you will have seen -- regularly -- people posting questions like "how do i delete all but one of the duplicate entries in my table?"

    these difficulties arise because people use autonumber surrogate keys and forget to declare a unique constraint on the "real" primary keys -- if in fact they even have any idea of what a primary key is for (to provide uniqueness)

    a multi-column key is quite simple to use, if that is the real primary key

    but go ahead and use a surrogate if you feel like it

    it is a very sensitive subject
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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