If we drop a column we cascade that drop to all indexes and all
constraints that mention that column, even if they include other
columns also. We might expect that indexes should be dropped
automatically, but the latter behavior for constraints seems like a
bug, since it can silently remove constraints that might still be
valid without the dropped column. (Example below). This is even more
surprising if the user specifies RESTRICT explicitly. I note that this
is acting as documented, it's just the docs don't explain the full
implications, so I'm guessing we didn't think about this before.
The effect of accidentally removing a constraint can be fairly
dramatic, for example, tables suddenly start refusing updates/deletes
if they are part of a publication, or partitioning schemes that depend
upon check constraints can suddenly stop working. As well as the more
obvious loss of protection from bad input data.
ISTM that we should refuse to drop constraints, if the constraint is
also dependent upon other columns that will remain in the table,
unless the user requests CASCADE.
- - -
create table abc (a int, b int, c int, primary key (a,b,c), check (a >
5 and b is not null and c > 10));
create index bc on abc (b, c);
\d abc
Table "public.abc"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | not null |
c | integer | | not null |
Indexes:
"abc_pkey" PRIMARY KEY, btree (a, b, c)
"bc" btree (b, c)
Check constraints:
"abc_c_check" CHECK (c > 9)
"abc_check" CHECK (a > 5 AND b IS NOT NULL AND c > 10)
alter table abc drop column c restrict;
\d abc
Table "public.abc"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | not null |
Noting that all constraints have been removed, not just the ones
wholly dependent on dropped columns.
--
Simon Riggs http://www.EnterpriseDB.com/
