Thread: Bug #896: Column Constraint Not Working in ALTER TABLE ADD COLUMN?
Bug #896: Column Constraint Not Working in ALTER TABLE ADD COLUMN?
From
pgsql-bugs@postgresql.org
Date:
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
pgsql-bugs@postgresql.org writes:
> Column Constraint Not Working in ALTER TABLE ADD COLUMN?
Yeah, the parser was losing foreign key constraints in this case :-(
Patch against 7.3 is attached.
regards, tom lane
*** src/backend/parser/analyze.c.orig Mon Feb 10 23:13:39 2003
--- src/backend/parser/analyze.c Thu Feb 13 17:41:13 2003
***************
*** 111,117 ****
static void transformIndexConstraints(ParseState *pstate,
CreateStmtContext *cxt);
static void transformFKConstraints(ParseState *pstate,
! CreateStmtContext *cxt);
static void applyColumnNames(List *dst, List *src);
static List *getSetColTypes(ParseState *pstate, Node *node);
static void transformForUpdate(Query *qry, List *forUpdate);
--- 111,118 ----
static void transformIndexConstraints(ParseState *pstate,
CreateStmtContext *cxt);
static void transformFKConstraints(ParseState *pstate,
! CreateStmtContext *cxt,
! bool isAddConstraint);
static void applyColumnNames(List *dst, List *src);
static List *getSetColTypes(ParseState *pstate, Node *node);
static void transformForUpdate(Query *qry, List *forUpdate);
***************
*** 770,776 ****
/*
* Postprocess foreign-key constraints.
*/
! transformFKConstraints(pstate, &cxt);
/*
* Output results.
--- 771,777 ----
/*
* Postprocess foreign-key constraints.
*/
! transformFKConstraints(pstate, &cxt, false);
/*
* Output results.
***************
*** 1296,1302 ****
}
static void
! transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt)
{
if (cxt->fkconstraints == NIL)
return;
--- 1297,1304 ----
}
static void
! transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt,
! bool isAddConstraint)
{
if (cxt->fkconstraints == NIL)
return;
***************
*** 1305,1320 ****
cxt->stmtType);
/*
! * For ALTER TABLE ADD CONSTRAINT, nothing to do. For CREATE TABLE,
! * gin up an ALTER TABLE ADD CONSTRAINT command to execute after
! * the basic CREATE TABLE is complete.
*
* Note: the ADD CONSTRAINT command must also execute after any index
* creation commands. Thus, this should run after
* transformIndexConstraints, so that the CREATE INDEX commands are
* already in cxt->alist.
*/
! if (strcmp(cxt->stmtType, "CREATE TABLE") == 0)
{
AlterTableStmt *alterstmt = makeNode(AlterTableStmt);
List *fkclist;
--- 1307,1322 ----
cxt->stmtType);
/*
! * For ALTER TABLE ADD CONSTRAINT, nothing to do. For CREATE TABLE or
! * ALTER TABLE ADD COLUMN, gin up an ALTER TABLE ADD CONSTRAINT command
! * to execute after the basic command is complete.
*
* Note: the ADD CONSTRAINT command must also execute after any index
* creation commands. Thus, this should run after
* transformIndexConstraints, so that the CREATE INDEX commands are
* already in cxt->alist.
*/
! if (!isAddConstraint)
{
AlterTableStmt *alterstmt = makeNode(AlterTableStmt);
List *fkclist;
***************
*** 2253,2259 ****
(ColumnDef *) stmt->def);
transformIndexConstraints(pstate, &cxt);
! transformFKConstraints(pstate, &cxt);
((ColumnDef *) stmt->def)->constraints = cxt.ckconstraints;
*extras_before = nconc(*extras_before, cxt.blist);
--- 2255,2261 ----
(ColumnDef *) stmt->def);
transformIndexConstraints(pstate, &cxt);
! transformFKConstraints(pstate, &cxt, false);
((ColumnDef *) stmt->def)->constraints = cxt.ckconstraints;
*extras_before = nconc(*extras_before, cxt.blist);
***************
*** 2290,2298 ****
elog(ERROR, "Unexpected node type in ALTER TABLE ADD CONSTRAINT");
transformIndexConstraints(pstate, &cxt);
! transformFKConstraints(pstate, &cxt);
Assert(cxt.columns == NIL);
stmt->def = (Node *) nconc(cxt.ckconstraints, cxt.fkconstraints);
*extras_before = nconc(*extras_before, cxt.blist);
*extras_after = nconc(cxt.alist, *extras_after);
--- 2292,2301 ----
elog(ERROR, "Unexpected node type in ALTER TABLE ADD CONSTRAINT");
transformIndexConstraints(pstate, &cxt);
! transformFKConstraints(pstate, &cxt, true);
Assert(cxt.columns == NIL);
+ /* fkconstraints should be put into my own stmt in this case */
stmt->def = (Node *) nconc(cxt.ckconstraints, cxt.fkconstraints);
*extras_before = nconc(*extras_before, cxt.blist);
*extras_after = nconc(cxt.alist, *extras_after);