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

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