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.

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? — Brendan McCaffrey, Mar 04 '22 at 11:18
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. — Akina, Mar 04 '22 at 12:29
I just found this answer: https://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. — Jeggy, Mar 04 '22 at 12:34
Can you provide some sample data to help demonstrate the scenario & desired outcome? — AMtwo, Mar 04 '22 at 12:37
This is a little bit more complicated case, because you want the reverse values of the fields to be checked against the original values. I was originally going to propose using a computed column for your logic which could then be uniquely indexed but now seeing what you actually want to accomplish with your example, likely a CHECK CONSTRAINT with a function is what you'd need to use instead. — J.D., Mar 04 '22 at 13:03
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. — Lukas.Navratil, Mar 04 '22 at 13:10
@Lukas.Navratil I think this approach would work for the OP's question. You should write this as an answer. — AMtwo, Mar 04 '22 at 13:37
@Lukas.Navratil It's important to not have rules on if the FROM_ID is required to be higher or lower than TO_ID, because it will have side effects. — Jeggy, Mar 04 '22 at 13:45
@jeggy if order is important, and changing it has side effects, how are 1 & 3 "duplicates"? Is there existing data that would need to be cleaned up as part of a solution? How would you determine whether 1 or 3 is the "good" row that should be kept? — AMtwo, Mar 04 '22 at 13:49
You /could/ try a computed column based on least(from,to) and greater(from,to) and put a unique index on that ... — Phill W., Mar 04 '22 at 14:11
@J.D. This is perfectly doable with just declarative constraints — Charlieface, Mar 09 '22 at 15:00

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.

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. — Jeggy, Mar 04 '22 at 14:58

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