Thread: Is this a bug? Deleting a column deletes the constraint.
I got bit by this to be sure, but is it a bug? I guess I'd hoped for a warning at the critical step (see "poof" below): create table tester (one int, two int, three int); alter table only tester add constraint no_dupes unique (one, two, three); insert into tester values(1,2,3); insert into tester values(1,2,4); insert into tester values(1,2,3); -- this gets denied by the constraint alter table tester add column new_three varchar(8); alter table tester rename column three to old_three; \d tester; -- the constraint moves to the new column name alter table tester rename column new_three to three; \d tester; -- the constraint remains alter table tester drop column old_three; \d tester; -- poof, the constraint is gone with no warning -- ---- Visit http://www.obviously.com/
Bryce Nesbitt <bryce1@obviously.com> writes:
> I got bit by this to be sure, but is it a bug? I guess I'd hoped for a
> warning at the critical step (see "poof" below):
> create table tester (one int, two int, three int);
> alter table only tester add constraint no_dupes unique (one, two, three);
> ...
> alter table tester drop column old_three;
> \d tester; -- poof, the constraint is
> gone with no warning
I don't think the renaming is relevant: you get the same with
regression=# create table tester (one int, two int, three int, unique (one,two,three));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tester_one_key" for table "tester"
CREATE TABLE
regression=# alter table tester drop column three;
ALTER TABLE
regression=# \d tester Table "public.tester"Column | Type | Modifiers
--------+---------+-----------one | integer | two | integer |
regression=#
It does seem like this is wrong, in view of SQL92's statement about
ALTER TABLE DROP COLUMN:
4) If RESTRICT is specified, then C shall not be referenced in the <query expression> of any view
descriptoror in the <search condition> of any constraint descriptor other than a table con- straint
descriptorthat contains references to no other column and that is included in the table descriptor of T.
IOW we should only allow unique constraints to be auto-dropped if
they reference just the one single column. Ick.
regards, tom lane
On Thu, 2006-10-12 at 01:25 -0400, Tom Lane wrote: > It does seem like this is wrong, in view of SQL92's statement about > ALTER TABLE DROP COLUMN: > > 4) If RESTRICT is specified, then C shall not be referenced in > the <query expression> of any view descriptor or in the <search > condition> of any constraint descriptor other than a table con- > straint descriptor that contains references to no other column > and that is included in the table descriptor of T. > > IOW we should only allow unique constraints to be auto-dropped if > they reference just the one single column. Ick. I didn't realize before that you can also drop all columns, leaving a table without *any* columns. Is that a SQL92 "feature"? Joe
Joe <dev@freedomcircle.net> writes:
> I didn't realize before that you can also drop all columns, leaving a
> table without *any* columns. Is that a SQL92 "feature"?
See the ALTER TABLE reference page:
ALTER TABLE DROP COLUMN can be used to drop the only column of a table, leaving a zero-column table. This is an
extensionof SQL, which disallows zero-column tables.
The CREATE TABLE reference page further amplifies:
PostgreSQL allows a table of no columns to be created (for example, CREATE TABLE foo();). This is an extension from
theSQL standard, which does not allow zero-column tables. Zero-column tables are not in themselves very useful, but
disallowingthem creates odd special cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
restriction.
regards, tom lane
dev@freedomcircle.net (Joe) writes: > On Thu, 2006-10-12 at 01:25 -0400, Tom Lane wrote: >> It does seem like this is wrong, in view of SQL92's statement about >> ALTER TABLE DROP COLUMN: >> >> 4) If RESTRICT is specified, then C shall not be referenced in >> the <query expression> of any view descriptor or in the <search >> condition> of any constraint descriptor other than a table con- >> straint descriptor that contains references to no other column >> and that is included in the table descriptor of T. >> >> IOW we should only allow unique constraints to be auto-dropped if >> they reference just the one single column. Ick. > > I didn't realize before that you can also drop all columns, leaving a > table without *any* columns. Is that a SQL92 "feature"? You can create a table without any columns. tutorial=# create table empty_table (); CREATE TABLE tutorial=# \d empty_table Table "public.empty_table"Column | Type | Modifiers --------+------+----------- tutorial=# select * from empty_table ; -- (0 rows) A table without columns is of pretty limited usefulness, until you add some columns, but having a form of "zero" seems not insensible... -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/lisp.html They are called computers simply because computation is the only significant job that has so far been given to them. -- Louis Ridenour
* Tom Lane: > The CREATE TABLE reference page further amplifies: > > PostgreSQL allows a table of no columns to be created (for example, > CREATE TABLE foo();). This is an extension from the SQL standard, which > does not allow zero-column tables. Zero-column tables are not in > themselves very useful, but disallowing them creates odd special cases > for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec > restriction. And you need the syntax for table partitioning. -- Florian Weimer <fweimer@bfk.de> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47 tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99