Thread: Is this a bug? Deleting a column deletes the constraint.

Is this a bug? Deleting a column deletes the constraint.

From
Bryce Nesbitt
Date:
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/



Re: Is this a bug? Deleting a column deletes the constraint.

From
Tom Lane
Date:
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


Re: Is this a bug? Deleting a column deletes the constraint.

From
Joe
Date:
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



Re: Is this a bug? Deleting a column deletes the constraint.

From
Tom Lane
Date:
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


Re: Is this a bug? Deleting a column deletes the constraint.

From
Chris Browne
Date:
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


Re: Is this a bug? Deleting a column deletes the constraint.

From
Florian Weimer
Date:
* 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