Michael Davis wrote:
> Does anyone know how to completely and accurately remove or drop a
> constraint, specifically a foreign key constraint? I tried to remove a
> constraint by deleting it's trigger from pg_triggers. This caused some
> undesirable side effects with other tables involved with the constraint. I
> have several tables that I need to change the column constraints and
> foreign key constraints on. Recreating (drop and create) the table every
> time I need to change a column constraint is a pain because all the objects
> that reference the table would also need to be recreated (i.e. views and
> triggers). How do production DBAs successfully make changes to their
> tables?
>
> FYI, I was able to alter table add the same constraint many times. Is this
> a problem? This created a new trigger in pg_triggers every time.
A bug in "cluster" will do this, if you cluster a table it will drop all
constraints and indexes.
One could use pg_dump, as:
pg_dump -a -t table database > table_data.sql
pg_dump -s -t table database > table_schema.sql
(edit table_schema.sql to remove constraint)
psql database < table_schema.sql
psql database < table_data.sql
