ALTER TABLE problems
Hello ,
I had found today that when I tries to add column to a table with simultaneous creating
foreign key for this column, no constraints really created with no any errors messages,
but a column really added. See example bellow.
isbs=# \d priceValues
[skip]
tdirid | integer |
ttid | integer |
t_vgwid | integer |
Foreign Key constraints: $3 FOREIGN KEY (ttid) REFERENCES teltypes(ttid) ON UPDATE NO ACTION ON DELETE SET
NULL,
$2 FOREIGN KEY (t_vgwid) REFERENCES voipgateways(vgwid) ON UPDATE NO ACTION ON DELETE SET NULL,
$1 FOREIGN KEY (tdirid) REFERENCES teldirection(tdirid) ON UPDATE NO ACTION ON DELETE SET NULL
isbs=# alter table priceValues add column o_vgwid int4 REFERENCES voipgateways(vgwid) ON DELETE SET NULL;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ALTER TABLE
isbs=# \d priceValues
[skip]
tdirid | integer |
ttid | integer |
t_vgwid | integer |
o_vgwid | integer |
Foreign Key constraints: $3 FOREIGN KEY (ttid) REFERENCES teltypes(ttid) ON UPDATE NO ACTION ON DELETE SET
NULL,
$2 FOREIGN KEY (t_vgwid) REFERENCES voipgateways(vgwid) ON UPDATE NO ACTION ON DELETE SET NULL,
$1 FOREIGN KEY (tdirid) REFERENCES teldirection(tdirid) ON UPDATE NO ACTION ON DELETE SET NULL
(note: NO FOREIGN KEYS FOR o_vgwid PostgreSQL had created!!!)
isbs=# alter table priceValues add foreign key (o_vgwid) REFERENCES voipgateways(vgwid) ON DELETE SET NULL;
isbs=# \d priceValues
[skip]
tdirid | integer |
ttid | integer |
t_vgwid | integer |
o_vgwid | integer |
Foreign Key constraints: $4 FOREIGN KEY (o_vgwid) REFERENCES voipgateways(vgwid) ON UPDATE NO ACTION ON DELETE
SET NULL,
$3 FOREIGN KEY (ttid) REFERENCES teltypes(ttid) ON UPDATE NO ACTION ON DELETE SET NULL,
$2 FOREIGN KEY (t_vgwid) REFERENCES voipgateways(vgwid) ON UPDATE NO ACTION ON DELETE SET NULL,
$1 FOREIGN KEY (tdirid) REFERENCES teldirection(tdirid) ON UPDATE NO ACTION ON DELETE SET NULL
(note: only after I had manually created contraint, all ok)
I had tried this some times, and all times I get the same result. Is it bug or I not doing all
properly ? It's not a creatic bug since I can create foreign key manually...
Thanks a lot for any comments.
isbs=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4
(1 row)
--
best regards,
Ruslan A Dautkhanov rusland@scn.ru
Ruslan A Dautkhanov <rusland@scn.ru> writes:
I had found today that when I tries to add column to a table with
simultaneous creating foreign key for this column, no constraints
really created with no any errors messages, but a column really
added.
Yeah, this is a known bug in 7.3.*: ALTER TABLE ADD COLUMN fails to
process REFERENCES clauses. There is a fix in place for 7.3.3.
regards, tom lane