CODEWITHSUNDEEP
mysqlunique-constraint
gumape
gumape

Reputation: 2932

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.

Upvotes: 6

Views: 4513

Answers (3)

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121057

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.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116180

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.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108500

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.

Upvotes: 2

Related Questions