Subject | Unique Constraints and NULLs |
---|---|
Author | Scott Morgan |
Post date | 2019-07-12T13:15:57Z |
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
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