Conditional composite key in MySQL?

Question

Hi

So I have this table with a composite key, basically 'userID'-'data' must be unique (see my other question http://stackoverflow.com/questions/2026379/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

Answer 1

Well, I ended up using an out-of-the-box solution which doesn't exactly answer the question, but works for my needs, so if someone else comes up with a good answer I'll accept that.

What I did was:

• Instead of setting userID to zero, I negate it
• In my scripts I test for it with <=0 instead of ==0

Answer 2

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