Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011

    Unanswered: Interchangeable columns in primary key

    I am designing a mySQL database.

    I have the problem however whereby I have a table representing relationships, i.e. person1 and person2 are related. I only want one record representing this relationship however and I'm not sure how to define the primary key so that both records (person1-person2 and person2-person1) are not allowed in a single table, rather only one of the two records, as having both records is redundant.


    Stephen Adams

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    There is no primary key variation that covers what you are looking to achieve, however, by using a simple technique during the insert by always adding the lower value in the first field and the higher value in the second field then a primary key on person1, person2 will work. The technique can be done programmatically through your programming language or by creating a stored procedure and using this as an API. Any time an insert needs to be performed on this table then use the stored procedure which includes the logic of placing the lower value first and the higher value second.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by skimbels View Post
    I only want one record representing this relationship however
    the drawback to this is that any query looking for friends will require a UNION

    if you were to add both rows -- person1,person2 and person2,person1 -- then your query would be a lot simpler and more efficient

    it's a tradeoff, which i know i woulda solved one way back in the day when disk space was expensive (hunnerts of bucks per kilobyte), and which i would solve the other way in this day when a terabyte of space is only a couple bucks | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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