fishScript.com Script con annotazioni in italiano e inglese

PostgreSQL

Home >PostgreSQL > Posgresql how disable and renable foreign constraints

I had to recover old table data, to do it quickly that I want to delete first the data contained and load from a backup. Cannot just delete the data because the table was linked with several foreign keys. In this case, the backup data are consistent with all the foreign keys, I just needed to disable the foreign keys temporarily to delete the rows. In PostgreSQL, the command: ALTER TABLE t DISABLE TRIGGER ALL; does not just disable all the triggeres table but also the foreign key constraints

-- Parent referenced table 
pgdb=# create table r (c char primary key );

-- Children referencing table 
pgdb=# create table t (c char primary key );
pgdb=# alter table  t add  FOREIGN KEY (c) REFERENCES r;

-- Due the foreign constraint can  insert on children table(t) only if the value is in the referenced table (r)
pgdb=# insert into r values ('a');
pgdb=# insert into t values ('a');

-- Obviously, cannot insert or delete data not in the parent table
pgdb=#  insert into t values ('b');
ERROR:  insert or update on table "t" violates foreign key constraint "t_c_fkey"
DETAIL:  Key (c)=(b) is not present in table "r".

-- In fact, nor deleting
pgdb=# delete from r;
ERROR:  update or delete on table "r" violates foreign key constraint "t_c_fkey" on table "t"
DETAIL:  Key (c)=(a) is still referenced from table "t".

-- 
pgdb=# ALTER TABLE  r  DISABLE TRIGGER ALL;
ALTER TABLE
pgdb=# delete from r;
DELETE 1

-- don't forget to renable it ...
pgdb=# ALTER TABLE  r  ENABLE TRIGGER ALL ;

Warning: include(ads/text468x15.html): failed to open stream: No such file or directory in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Warning: include(): Failed opening 'ads/text468x15.html' for inclusion (include_path='.;C:\php\pear') in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Tutorial

PostgreSQL determine database uptime [PostgreSQL]

Postgres check database is Master or Replica ways to check database replication role [PostgreSQL]

Ordering by highest or lowest value of multiple columns PostgreSQL functions in order by clause [PostgreSQL]

Posgresql how disable and renable foreign constraints forcing data into a table ignoring foreign keys (or trigger) [PostgreSQL]
Comandi

date and timestamp simple Using date and timestamp [PostgreSQL]

Add time to a date date function operation [PostgreSQL]

How set a default schema working away in your postgreSQL session [PostgreSQL]

postgres tip: how re-execute a query every n second a nice postgres shell command [PostgreSQL]

Grouping data by the week example date_trunc function, trimming days by the week [PostgreSQL]

How get PosgreSQL table size pg_total_relation_size function [PostgreSQL]

PostgreSQL determine database size pg_database_size function returns database size [PostgreSQL]

PostgreSQL enable autostart at reboot on Centos / Redhat Configure autostart after reboot [PostgreSQL]

Argomenti

PostgreSQL

Cerca