Lets say I have a table with 3 fields:
- id1
- id2
- mapTypeId
What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database:
- 1, 2, 3
I should not be able to insert a record like:
- 2, 1, 3
Right now I have id1 and id2 as my composite primary key however I am able to insert the data above, is there anyway to prevent this on the database level?
Best Answer
Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order
Here is some Sample Data:
Here it is the Sample Data Loaded
Watch What Happens When I Insert the Rows:
Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2
This may not be contextually what you want because id1 and id2 are unique identifiers.