SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Shared primary key question

    I have a table that looks like so:

    friends:
    ----------
    friend_a_id
    friend_b_id

    Now I added a shared primary key to this table that appears like so:

    Primary key (friend_a_id, friend_b_id)

    However it only checks for duplicate values if the combination is always in the same order.

    For example the database allows the following two entries:
    13
    or
    31

    I'd like it to reject any COMBINATION of two ids that were already entered into the database. So in my above example if the primary key 13 already exists it should reject an entry of 31.

    Is this possible or is it something I need to check via programming before entering data?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    as far as i know, you can't do it with DDL (data definition language, i.e. the part of SQL that defines stuff like keys, constraints, etc.)

    the simplest solution, in my mind, is to put a line of code in your application program that always puts the lower of the two ids in the first position

    thus, you would only ever attempt to enter 1,3 but never 3,1

    similarly, you would enter 9,37 but not 37,9 etc.

    you can enforce this with a CHECK constraint, but unfortunately not in mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have to check via programming first.

    a good way to avoid "checking" is sort the friends so that the lowest ID is always first.

    you will need to clean up your current table to make this work.

    first, delete the duplicates:
    Code:
    delete f1
    from friends f1
      join friends f2
    on (f1.friend_a_id, f1.friend_b_id) = (f2.friend_b_id, f2.friend_a_id)
     where f1.friend_b_id < f1.friend_a_id
    then update the remaining rows where the friends are not sorted properly:
    Code:
    update friends
       set friend_a_id=(@temp:=friend_a_id)
         , friend_a_id = friend_b_id
         , friend_b_id = @temp
     where friend_a_id > friend_b_id
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the info folks!

    This actually won't be too difficult to add since my site isn't live and I have little data. (yet! )


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
  •