It appears that Postgres will allow any INSERT, despite a multiple-column
constraint, if any of the values INSERTed are NULL. If I read the included
excerpt correctly (there are like three negatives in the second sentence,
sheesh :) ), multiple NULL values for a column are acceptable or, in other
words, are not a violation of UNIQUEness.
However, shouldn't any values that are not NULL violate the constraint if
the same values exist already?
As an example, into the table definition at the bottom of this message... This should be acceptable. INSERT INTO
ao_functions(name, skill, arg1, arg2, arg3) VALUES (NULL,
NULL, NULL, NULL, NULL); INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES (NULL,
NULL, NULL, NULL, NULL);
But this should not... INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('Hit', 1, -1, -1, 91); INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('Hit', 1, -1, -1, 91); ERROR: Cannot insert a duplicate key into unique index unique_aofunction
Why does this succeed? INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('TauntNPC', 1, NULL, NULL, NULL); INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('TauntNPC', 1, NULL, NULL, NULL);
-- Excerpt from the Postgres CREATE TABLE documentation ---
...
UNIQUE Constraint
...
The column definitions of the specified columns do not have to include a NOT
NULL constraint to be included in a UNIQUE constraint. Having more than one
null value in a column without a NOT NULL constraint, does not violate a
UNIQUE constraint. (This deviates from the SQL92 definition, but is a more
sensible convention. See the section on compatibility for more details.)
...
--- End of Excerpt --
CREATE TABLE ao_functions ( id SERIAL CONSTRAINT funckey PRIMARY KEY, name
CHARACTER(25), skill INTEGER NULL, arg1 CHARACTER VARYING(100) NULL DEFAULT NULL,
arg2 CHARACTER VARYING(100) NULL DEFAULT NULL, arg3 CHARACTER VARYING(100) NULL DEFAULT
NULL, CONSTRAINT unique_aofunction UNIQUE (name, skill, arg1, arg2, arg3)
);
David Allardyce
