It is not possible to remove the "test1_t1_key" constraint because the "test2_t1_fkey" internally references it: <<< postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key; ERROR: cannot drop constraint test1_t1_key on table test1 because other objects depend on it DETAIL: constraint test2_t1_fkey on table test2 depends on index test1_t1_key HINT: Use DROP ... CASCADE to drop the dependent objects too.
Why not CASCADE?:
In fact, CASCADE is not enough, because I don't know how the test2_t1_fkey is built : does it use the test1_pkey primary key or the test1_t1_key unique key? I am sure this information can be found in system catalogs, but I find it safer to explicitely delete then recreate the foreign constraint.
Sylvain
This query might help you determine which key is referenced.
The key information is confkey, which is reported as key position in the referenced table.
Refer to system catalogs description in documentaion for more info.
SELECT cn.conname, CASE WHEN cn.contype = 'c' THEN 'check' WHEN cn.contype = 'f' THEN 'foreign key' WHEN cn.contype = 'p' THEN 'primary key' WHEN cn.contype = 'u' THEN 'unique' WHEN cn.contype = 't' THEN 'trigger' WHEN cn.contype = 'x' THEN 'exclusion' END as type, cn.condeferrable, CASE WHEN cn.conrelid > 0 THEN (SELECT nspname || '.' || relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.oid = cn.conrelid) ELSE '' END as table, confkey, consrc FROM pg_constraint cn ORDER BY 1;
--
Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.