Conditional composite key in MySQL?

Question

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

However, I was wondering if it was possible to make this only come into effect when userID is not zero? By that I mean, 'userID'-'data' must be unique for non-zero userIDs?

Or am I barking up the wrong tree?

Thanks
Mala

Photophilous

Answer 1

SQL constraints apply to every row in the table. You can't make them conditional based on certain data values.

However, if you could use NULL instead of zero, you can get around the unique constraint. A unique constraint allows multiple entries that have NULL. The reason is that uniqueness means no two equal values can exist. Equality means value1 = value2 must be true. But in SQL, NULL = NULL is 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 the following statements would violate the unique constraint, but they don't:

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');

Concertize

awesome, thanks! As i said i used a different solution, but you gave me a perfect answer to my question and I will doubtless use it in the future =) – Photophilous