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



  • 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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2