ALTER TABLE problems

Started by Ruslan A Dautkhanovalmost 23 years ago2 messagesbugs
Jump to latest
#1Ruslan A Dautkhanov
rusland@scn.ru

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ruslan A Dautkhanov (#1)
Re: ALTER TABLE problems

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