pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs this a bug? Deleting a column deletes the constraint.
6+ messages / 5 participants
[nested] [flat]
* Is this a bug? Deleting a column deletes the constraint.
@ 2006-10-12 05:11 Bryce Nesbitt <bryce1@obviously.com>
2006-10-12 05:25 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bryce Nesbitt @ 2006-10-12 05:11 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is this a bug? Deleting a column deletes the constraint.
2006-10-12 05:11 Is this a bug? Deleting a column deletes the constraint. Bryce Nesbitt <bryce1@obviously.com>
@ 2006-10-12 05:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 09:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-10-12 05:25 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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 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.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is this a bug? Deleting a column deletes the constraint.
2006-10-12 05:11 Is this a bug? Deleting a column deletes the constraint. Bryce Nesbitt <bryce1@obviously.com>
2006-10-12 05:25 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-12 09:39 ` Joe <dev@freedomcircle.net>
2006-10-12 14:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 14:56 ` Re: Is this a bug? Deleting a column deletes the constraint. Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 6+ messages in thread
From: Joe @ 2006-10-12 09:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is this a bug? Deleting a column deletes the constraint.
2006-10-12 05:11 Is this a bug? Deleting a column deletes the constraint. Bryce Nesbitt <bryce1@obviously.com>
2006-10-12 05:25 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 09:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Joe <dev@freedomcircle.net>
@ 2006-10-12 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 16:19 ` Re: Is this a bug? Deleting a column deletes the constraint. Florian Weimer <fweimer@bfk.de>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-10-12 14:39 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
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 extension of 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 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.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is this a bug? Deleting a column deletes the constraint.
2006-10-12 05:11 Is this a bug? Deleting a column deletes the constraint. Bryce Nesbitt <bryce1@obviously.com>
2006-10-12 05:25 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 09:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Joe <dev@freedomcircle.net>
2006-10-12 14:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-12 16:19 ` Florian Weimer <fweimer@bfk.de>
0 siblings, 0 replies; 6+ messages in thread
From: Florian Weimer @ 2006-10-12 16:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe <dev@freedomcircle.net>; Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
* 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is this a bug? Deleting a column deletes the constraint.
2006-10-12 05:11 Is this a bug? Deleting a column deletes the constraint. Bryce Nesbitt <bryce1@obviously.com>
2006-10-12 05:25 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 09:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Joe <dev@freedomcircle.net>
@ 2006-10-12 14:56 ` Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Browne @ 2006-10-12 14:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-10-12 16:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-12 05:11 Is this a bug? Deleting a column deletes the constraint. Bryce Nesbitt <bryce1@obviously.com>
2006-10-12 05:25 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 09:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Joe <dev@freedomcircle.net>
2006-10-12 14:39 ` Re: Is this a bug? Deleting a column deletes the constraint. Tom Lane <tgl@sss.pgh.pa.us>
2006-10-12 16:19 ` Re: Is this a bug? Deleting a column deletes the constraint. Florian Weimer <fweimer@bfk.de>
2006-10-12 14:56 ` Re: Is this a bug? Deleting a column deletes the constraint. Chris Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox