CODEWITHSUNDEEP
postgresql
wutzebaer
wutzebaer

Reputation: 14865

update pg_constraint has no effect (postgres)

I tried to change all foreign keys in PostgreSQL at once to cascade on delete:

UPDATE pg_catalog.pg_constraint
SET confupdtype='c', confdeltype='c', confmatchtype='u'
WHERE connamespace=2200;

There are no errors, and when I inspect the tables with pgadmin, it looks right, but when I try to delete a referenced table-line it comes to a constraint error. Just the SQL statement works:

ALTER TABLE tblname
  DROP CONSTRAINT IF EXISTS  fk3e2e4a8ff123848a;

ALTER TABLE tblname
  ADD CONSTRAINT fk3e2e4a8ff123848a FOREIGN KEY (field)
      REFERENCES othertable (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

Any idea why changing pg_catalog.pg_constraint is not working? even restarting the service after didn't help.

Upvotes: 0

Views: 870

Answers (1)

Kuberchaun
Kuberchaun

Reputation: 30324

Really you shouldn't be updating pg_* tables.

Use a command like

ALTER TABLE YOURTABLE DISABLE TRIGGER;

Check this link out.

http://archives.postgresql.org/pgsql-general/2011-10/msg00802.php

Upvotes: 2

Related Questions