MySQL UNIQUE Constraint with a condition

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

Is it开发者_开发问答 possible?

Thanks.

There is no declarative constraint to support such a restriction. The scenario you describe does not satisfy the requirements for a unique constraint. (You can create the constraint, but you won't be able to add more than one row with identical values for id_1 and id_2.

If your intent is to reject an insert or an update based on this restriction, you might be able to accomplish this with a row-level trigger.

You could do that by building a query that checks these rows and integrate it in a trigger. You won't be able to do that with a constraint and certainly not with a unique constraint. A unique constraint forces rows to have unique values. That means that for the field or fields in the constraint no two rows can have the same value.

That is not possible. A unique constraint on id_1 and id_2 will force each row to have a different (unique) combination of id_1 and id_2. That's the exact opposite of what you are describing.

You could possibly enforce your requirement by using a trigger, but given the small amount of information provided by you, I can't say if it would be the best solution.

A composite foreign key could also be the solution, but I don't know what id_1 and id_2 refer to, so it's hard to say, really.