Create unique index based on two columns (bi-directional)

Question

I want to make a check constraint or a unique index to validate that I'm not creating duplicates within my table based on 2 columns, I do not want the same two IDs in these columns.

Thinking about something like below: (but this isn't possible)

CREATE UNIQUE INDEX entity_merge_no_recursive_merge_request_index on ENTITY_MERGE (
    TYPE_CODE,
    CONCAT(
        IIF(INTO_ID > FROM_ID, INTO_ID, FROM_ID),
        '|',
        IIF(INTO_ID < FROM_ID, INTO_ID, FROM_ID)
    )
)

Example of what I would like to achieve:

ID	TYPE_CODE	FROM_ID	TO_ID
1	PERSON	3	5
2	USER	3	5
3	PERSON	5	3

I want to make sure that inserting the last one here would have failed, because it's the same as ID 1 but reversed.

And it's important to not have any rules on FROM_ID to be higher or lower than TO_ID as it should be possible to merge any 2 records and either have some automation or a user via frontend to choose who is the winner record. And this would inactivate the FROM_ID record.

score 1 · Accepted Answer · answered Mar 04 '22 at 14:21

I would rather see this be handled someplace else. Probably in this order:

Take care of this in the application. Assuming you have control over what can insert/update into this table, handle it there in code.
Write the procs that do the inserts/updates into this table accordingly.
I am NOT a huge fan of triggers - but this may be a situation where a trigger could help. This is conditional logic based on two different columns and checking for the existence of them per your rules as stated in your most recent update.

I would also ask yourself more about the rules and if they make sense, if they scale, and if there are other ways to achieve the same outcome.

score 1 · Answer 2 · answered Mar 09 '22 at 14:58

You can do it like this:

Create two computed columns with the higher and lower values

ALTER TABLE ENTITY_MERGE
  ADD LowerId AS IIF(INTO_ID < FROM_ID, INTO_ID, FROM_ID);
ALTER TABLE ENTITY_MERGE
  ADD HigherId AS IIF(INTO_ID > FROM_ID, INTO_ID, FROM_ID);

Add a unique index across those

CREATE UNIQUE INDEX entity_merge_no_recursive_merge_request_index on ENTITY_MERGE (
    TYPE_CODE,
    LowerId,
    HigherId
);

Create unique index based on two columns (bi-directional)

2 Answers2