Table Constraints with NULL values

From: "David Allardyce" <dave(at)pod13(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Table Constraints with NULL values
Date: 2001-10-19 02:56:53
Message-ID: 026601c15849$b4f38680$6100a8c0@pod13.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Aasmund Midttun Godal 2001-10-19 03:01:49 oid's in views.
Previous Message Aasmund Midttun Godal 2001-10-19 01:51:18 Access to functions