MySQL UNIQUE Constraint Condition

Question

I am trying to create a unique index constraint for two columns (id_1 and id_2) with the following condition: If two rows have the same value in column id_2, their values ​​in column id_1 should also be the same.

Is it possible?

Thanks.

Answer 1

There are no declarative restrictions to support such a restriction. The scenario you are describing does not meet the requirements for a unique constraint. (You can create a constraint, but you cannot add multiple rows with the same values ​​for id_1 and id_2.

If you intend to reject an insert or update based on this restriction, you can accomplish this with a level trigger.

Answer 2

This can be done by building a query that checks these lines and integrates it into the trigger. You cannot do this with a restriction and, of course, not with a unique restriction. A unique constraint forces strings to have unique values. This means that for a field or fields in a constraint, two rows cannot have the same value.

Answer 3

It's impossible. The unique restriction on id_1 and id_2 will force each row to have a different (unique) combination of id_1 and id_2 . This is the exact opposite of what you are describing.

You can fulfill your requirement with a trigger, but given the small amount of information you provide, I cannot say if this was the best solution.

A component foreign key can also be a solution, but I don’t know what id_1 and id_2 , so it’s very difficult to say.