3

Is there any way in mySQL to specify UNIQUE constraint on multiple columns with a constraint of a specific value of a particular column?

Example:

A B
......
1 0
1 1
1 2
2 1
3 0
3 2

in the above table, if B is 0, then it can have the same set of values(row) in the table (duplicate), and if B is 1, then it should not accept the same set of A,B pair. Eg: in this case, it should accept any number of (A,B) = (1,0), (3,0) pairs, since B is 0, but it should not accept duplication of (1,1), (1,2), (2,1), (3,2).

Improve this question

1 Answer 1

Reset to default
1

MySQL allows duplicates already with multi-column UNIQUE constraints if any of the column values in the constraint are NULL. Perhaps you could replace 0 with NULL.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ya, this works in mySQL as it accepts multiple NULLs in unique columns. But, is there any generic logic which works in any database?
@Sudheer, it works that way because the SQL standard says that NULLs should operate that way. Therefore, constraints in other SQL compliant database systems should behave similarly. Are you really using more than one SQL database system? We started a project 12 years ago worried about database compatibility and never switched from MySQL.
Marcus, I am supposed to give generic implementation which could work/easily convertible in any database. But if I used this concept, it violates "easily convertible" when we need to use this in Oracle or DB2.
Are you sure it's not implemented the same way in Oracle and DB2?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.