Conditional composite key in MySQL?

Question

So, I have this table with a composite key, basically "userID" - "data" should be unique (see my other question SQL table - semi-unique row? )

However, I was wondering if this was only possible if userID is not zero? By this I mean that 'userID' - 'data' must be unique to nonzero user identifiers?

Or am I barking the wrong tree?

thanks
Mala

Answer 1

SQL constraints apply to each row of the table. You cannot make them conditional based on specific data values.

However, if you can use NULL instead of zero, you can get around a unique constraint. A unique constraint allows multiple NULL entries. The reason is that uniqueness means that there are no two equal values. Equality means value1 = value2 must be true. But in SQL, NULL = NULL unknown, not true.

 CREATE TABLE MyTable (id SERIAL PRIMARY KEY, userid INT, data VARCHAR(64)); INSERT INTO MyTable (userid, data) VALUES ( 1, 'foo'); INSERT INTO MyTable (userid, data) VALUES ( 1, 'bar'); INSERT INTO MyTable (userid, data) VALUES (NULL, 'baz'); 

So far so good, now you might think that the following statements would violate a unique constraint, but they do not:

 INSERT INTO MyTable (userid, data) VALUES ( 1, 'baz'); INSERT INTO MyTable (userid, data) VALUES (NULL, 'foo'); INSERT INTO MyTable (userid, data) VALUES (NULL, 'baz'); INSERT INTO MyTable (userid, data) VALUES (NULL, 'baz');