Conditional composite key in MySQL?

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 开发者_如何学JAVAup the wrong tree?

Thanks

Mala

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