On 2023-05-25 08:10:42 -0500, Ron wrote: > (You can create the FKs ahead of time, but use the NOT VALID clause; then, > after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.) I don't think this will work: | Normally, [ADD CONSTRAINT] will cause a scan of the table to verify | that all existing rows in the table satisfy the new constraint. But if | the NOT VALID option is used, this potentially-lengthy scan is | skipped. The constraint will still be enforced against subsequent | inserts or updates (https://www.postgresql.org/docs/current/sql-altertable.html) However, you can define a constraint as DEFERRABLE, and then defer checking until commit: hjp=> create table master(id int primary key, t text); CREATE TABLE hjp=> create table detail(id int primary key, master int references master DEFERRABLE INITIALLY IMMEDIATE, t text); CREATE TABLE hjp=> begin; BEGIN hjp=*> set constraints detail_master_fkey deferred; SET CONSTRAINTS hjp=*> insert into detail values(1, 1, '1/1'); INSERT 0 1 hjp=*> insert into detail values(2, 1, '1/2'); INSERT 0 1 hjp=*> insert into detail values(3, 2, '2/1'); INSERT 0 1 hjp=*> insert into detail values(4, 3, '3/1'); INSERT 0 1 hjp=*> insert into master values(1, '1'); INSERT 0 1 hjp=*> insert into master values(2, '2'); INSERT 0 1 -- We haven't inserted a master record with id 3 yet, so the commit will -- fail: hjp=*> commit; ERROR: insert or update on table "detail" violates foreign key constraint "detail_master_fkey" DETAIL: Key (master)=(3) is not present in table "master". (You can also reenable the constraint explicitely before the end of a transaction with SET CONSTRAINTS ... IMMEDIATE) hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@xxxxxx | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
