Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this setup on innoDB:

user details

User Connection

TblConnection also has these indexes: User, Connection where User is the foreign key of and another index: Connection, User where Connection is also a foregin key to

Now I want to make sure that when adding a new row I have complete uniqueness between column combinations. By this I mean I want that 1,2 is treated the same as 2,1 and therefore an INSERT is not made.

share|improve this question

migrated from Apr 9 '13 at 19:08

This question came from our site for system and network administrators.

I'm not sure if I understand correctly. As long as you have a unique key on both columns, it does not matter what order the columns (or combination) they are in. Otherwise, the tuple must also exist in the referencing table in order to enforce it's uniqueness.

CREATE TABLE tbluser (
  user int,
  details int,
  primary key(user, details));

CREATE TABLE tblconnections (
  user int,
  details int,
  connection int,
  primary key(user, details, connection),
  unique key(user, details),
  foreign key (user, details) 
    references tbluser (user, details));

Make sure that you specify a column list when you make this INSERT.

share|improve this answer
I actually couldn't post the answer but me and some friends figured it out. We made these changes and this algorithm: First: Made tblConnection have just 2 columns user and connection Second: We made the primary key of tblConnection the composite of both keys Third: We made an algorithm to take 2 user ids and then submit the smallest one as the user and the bigger as the connection therefor the table is automatically ordered with indexes and no duplicates can occur. – Dylan Grech Apr 9 '13 at 21:04
An algorithm where? – Kermit Apr 9 '13 at 23:09
The algorithm is done in PHP :) – Dylan Grech Apr 10 '13 at 12:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.