Subject Unique Constraints and NULLs
Author Scott Morgan
Are NULLs considered values for unique constraints on a table?

e.g.

CREATE TABLE blah (
col1 INT NOT NULL,
col2 INT,

CONSTRAINT unq_blah UNIQUE(col1, col2)
);

INSERT INTO blah (col1, col2) (123, 321);
INSERT INTO blah (col1, col2) (123, 999); /* okay */
INSERT INTO blah (col1, col2) (123, 321); /* error */

INSERT INTO blah (col1, col2) (123, NULL);
INSERT INTO blah (col1, col2) (123, NULL); /* okay? error? */

I'd like that NULL example to be an error, does the UNIQUE constraint do
that, or do I have to use a CHECK constraint? If it does require the
CHECK, are there any performance tips for that setup?

Scott