Shared primary key question

[TheMonk]

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?

[r937]

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

[longneck]

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:

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:

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

[TheMonk]

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! )