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.

• Brendan McCaffrey: Can you provide a little more detail? What is the scheme of the table? Are you looking for each of the two columns to be unique, or the combination of the two columns to be unique?
• Akina: CONCAT() is excess, (TYPE_CODE, IIF(INTO_ID > FROM_ID, INTO_ID, FROM_ID), IIF(INTO_ID < FROM_ID, INTO_ID, FROM_ID)) is enough. But AFAIR SQL Server does not allow to create index by an expression... so create generated column(s) and use it in the index.
• Jeggy: I just found this answer: dba.stackexchange.com/a/14110/168293 . It sounds like I should just put this logic in the application code instead of directly in the database.
• anon: Can you provide some sample data to help demonstrate the scenario & desired outcome?
• Lukas.Navratil: I personally would rather add a check constraint that FROM_ID is less (or equal depending on your business logic) than TO_ID and make sure that you enter the data correctly. Then you can use normal unique index and also it will be easier for you to work with such data.

Answer 1

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

1. Take care of this in the application. Assuming you have control over what can insert/update into this table, handle it there in code.
2. Write the procs that do the inserts/updates into this table accordingly.
3. 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.

• Jeggy: Exactly, as I found out, it's not supported out of the box to do something like this with indicies or constraints. So we have decided to keep this kinda logic in the application code and throw an exception when it happens.

Answer 2

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