Bug #896: Column Constraint Not Working in ALTER TABLE ADD COLUMN? - Mailing list pgsql-bugs

Tara Piorkowski (tara@vilaj.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Column Constraint Not Working in ALTER TABLE ADD COLUMN?

Long Description
According to the docs, you should be able to put a column constraint into an ALTER TABLE ... ADD COLUMN ... command.
However,at least the REFERENCES option does not seem to work in this command. The example below will demonstrate the
problem.

I have tested the code below on both Mac OS X 10.2.3 and Debian Linux 3.0 (Woody) with a 2.4.18 kernel. Both versions
ofPostgreSQL are 7.3.2. I noticed the problem in 7.3 and 7.3.1 as well. I did not test 7.2.x for the problem. The
resultsare identical. 

Sample Code
--
-- create a master table which will be referenced by a subordinate
-- and put a single record in it, which will have a master_id of 1
--

CREATE TABLE master
        (master_id serial NOT NULL PRIMARY KEY,
         description text NOT NULL);

INSERT INTO master (description)
        VALUES ('Dummy data.');

--
-- create the first version of the subordinate table, which will be
-- immediately altered so that it has a column that references
-- the master_id column in the master table
--

CREATE TABLE subordinate
        (subordinate_id serial NOT NULL PRIMARY KEY);

ALTER TABLE subordinate
        ADD COLUMN master_id int REFERENCES master (master_id);

ALTER TABLE subordinate
        ALTER COLUMN master_id SET NOT NULL;

-- insert two values: the first should succeed,
-- the second should fail, but doesn't

INSERT INTO subordinate (master_id)
        VALUES (1);

INSERT INTO subordinate (master_id)
        VALUES (2);

--
-- drop subordinate and recreate it, then alter it with a
-- table constraint rather than a column constraint
--

DROP TABLE subordinate;

CREATE TABLE subordinate
        (subordinate_id serial NOT NULL PRIMARY KEY);

ALTER TABLE subordinate
        ADD COLUMN master_id int;

ALTER TABLE subordinate
        ALTER COLUMN master_id SET NOT NULL;

ALTER TABLE subordinate
        ADD FOREIGN KEY (master_id) REFERENCES master (master_id);

-- insert two values: the first should succeed,
-- the second should fail, and does

INSERT INTO subordinate (master_id)
        VALUES (1);

INSERT INTO subordinate (master_id)
        VALUES (2);

--
-- drop subordinate and recreate it, this time correctly
-- from the get-go using a column constraint
--

DROP TABLE subordinate;

CREATE TABLE subordinate
        (subordinate_id serial NOT NULL PRIMARY KEY,
         master_id int NOT NULL REFERENCES master (master_id));

-- insert two values: the first should succeed,
-- the second should fail, and does

INSERT INTO subordinate (master_id)
        VALUES (1);

INSERT INTO subordinate (master_id)
        VALUES (2);

--
-- clean everything up
--

DROP TABLE subordinate;

DROP TABLE master;

No file was uploaded with this report