-- Delete a row from PK TABLE (should fail)

DELETE FROM PKTABLE WHERE ptest1=1;

ERROR: update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"

DETAIL: Key (ptest1)=(1) is still referenced from table "fktable".

-- Delete a row from PK TABLE (should succeed)

DELETE FROM PKTABLE WHERE ptest1=5;

-- Update a row from PK TABLE (should fail)

UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2;

ERROR: update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"

DETAIL: Key (ptest1)=(2) is still referenced from table "fktable".

-- Update a row from PK TABLE (should succeed)

UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4;

-- Try to update something that should fail

UPDATE PKTABLE set ptest2=5 where ptest2=2;

ERROR: update or delete on "pktable" violates foreign key constraint "constrname3" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "constrname3" on table "fktable"

DETAIL: Key (ptest1,ptest2,ptest3)=(1,2,3) is still referenced from table "fktable".

-- Try to update something that should succeed

UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;

-- Try to delete something that should fail

DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3;

ERROR: update or delete on "pktable" violates foreign key constraint "constrname3" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "constrname3" on table "fktable"

DETAIL: Key (ptest1,ptest2,ptest3)=(1,2,3) is still referenced from table "fktable".

-- Try to delete something that should work

DELETE FROM PKTABLE where ptest1=2;

insert into fktable(ftest1) values (3);

-- let's try removing a row that should fail from pktable

delete from pktable where base1>2;

ERROR: update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"

DETAIL: Key (base1)=(3) is still referenced from table "fktable".

-- okay, let's try updating all of the base1 values to *4

-- which should fail.

update pktable set base1=base1*4;

ERROR: update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"

DETAIL: Key (base1)=(3) is still referenced from table "fktable".

-- okay, let's try an update that should work.

update pktable set base1=base1*4 where base1<3;

insert into fktable(ftest1, ftest2) values (3, 1);

-- let's try removing a row that should fail from pktable

delete from pktable where base1>2;

ERROR: update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"

DETAIL: Key (base1,ptest1)=(3,1) is still referenced from table "fktable".

-- okay, let's try updating all of the base1 values to *4

-- which should fail.

update pktable set base1=base1*4;

ERROR: update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" on "fktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"

DETAIL: Key (base1,ptest1)=(3,1) is still referenced from table "fktable".

-- okay, let's try an update that should work.

update pktable set base1=base1*4 where base1<3;

DETAIL: Key (base2,ptest2)=(3,2) is not present in table "pktable".

-- fails (2,2) is being referenced

delete from pktable where base1=2;

ERROR: update or delete on "pktable" violates foreign key constraint "pktable_base2_fkey" on "pktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "pktable_base2_fkey" on table "pktable"

DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table "pktable".

-- fails (1,1) is being referenced (twice)

update pktable set base1=3 where base1=1;

ERROR: update or delete on "pktable" violates foreign key constraint "pktable_base2_fkey" on "pktable"

ERROR: update or delete ontable"pktable" violates foreign key constraint "pktable_base2_fkey" on table "pktable"

DETAIL: Key (base1,ptest1)=(1,1) is still referenced from table "pktable".

-- this sequence of two deletes will work, since after the first there will be no (2,*) references

delete from pktable where base2=2;