Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have this setup on innoDB:

TblUser 
user details

TblConnection
User Connection

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

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
add comment

migrated from serverfault.com Apr 9 '13 at 19:08

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

1 Answer

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
add comment

Your Answer

 
discard

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.