foreign_key.out

PostgreSQL7.4.6 for Linux

(5 rows)

-- 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"
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"
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;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      1 |      2 |      3 | test1
      1 |      3 |      3 | test2
      1 |      3 |      4 | test3
(3 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
      1 |      2 |      3 |      1
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
(5 rows)

DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- cascade update/delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int,  CONSTRAINT constrname3
			FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
			ON DELETE CASCADE ON UPDATE CASCADE);
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
ERROR:  insert or update on table "fktable" violates foreign key constraint "constrname3"
DETAIL:  Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
-- Show FKTABLE
SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
      1 |      2 |      3 |      1
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
(5 rows)

-- Try to update something that will cascade
UPDATE PKTABLE set ptest2=5 where ptest2=2;
-- Try to update something that should not cascade
UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |      4 |      5 | test4
      1 |      5 |      3 | test1
      1 |      3 |      3 | test2
      1 |      3 |      4 | test3
(4 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      1 |      5 |      3 |      1
(5 rows)

-- Try to delete something that should cascade
DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |      4 |      5 | test4
      1 |      3 |      3 | test2
      1 |      3 |      4 | test3
(3 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
(4 rows)

-- Try to delete something that should not have a cascade
DELETE FROM PKTABLE where ptest1=2;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      1 |      3 |      3 | test2
      1 |      3 |      4 | test3
(2 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
(4 rows)

DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- set null update / set default delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int,  CONSTRAINT constrname3
			FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
			ON DELETE SET DEFAULT ON UPDATE SET NULL);
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 
INSERT INTO FKTABLE VALUES (2, 3, 4, 1); 
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
ERROR:  insert or update on table "fktable" violates foreign key constraint "constrname3"
DETAIL:  Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
-- Show FKTABLE
SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
      1 |      2 |      3 |      1
      2 |      3 |      4 |      1
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
(6 rows)

-- Try to update something that will set null
UPDATE PKTABLE set ptest2=5 where ptest2=2;
-- Try to update something that should not set null
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |      3 |      4 | test3
      2 |      4 |      5 | test4
      1 |      5 |      3 | test1
      1 |      2 |      3 | test2
(4 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
      2 |      3 |      4 |      1
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      1 |        |      3 |      1
(6 rows)

-- Try to delete something that should set default
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |      4 |      5 | test4
      1 |      5 |      3 | test1
      1 |      2 |      3 | test2
(3 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      1 |        |      3 |      1
      0 |        |        |      1
(6 rows)

-- Try to delete something that should not set default
DELETE FROM PKTABLE where ptest2=5;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |      4 |      5 | test4
      1 |      2 |      3 | test2
(2 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      1 |        |      3 |      1
      0 |        |        |      1
(6 rows)

DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- set default update / set null delete
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int,  CONSTRAINT constrname3
			FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
			ON DELETE SET NULL ON UPDATE SET DEFAULT);
-- Insert Primary Key values
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
-- Insert Foreign Key values
INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 
INSERT INTO FKTABLE VALUES (2, 3, 4, 1); 
INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
-- Insert a failed values
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
ERROR:  insert or update on table "fktable" violates foreign key constraint "constrname3"
DETAIL:  Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
-- Show FKTABLE
SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
      1 |      2 |      3 |      1
      2 |      3 |      4 |      1
      2 |      4 |      5 |      1
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
(7 rows)

-- Try to update something that will fail
UPDATE PKTABLE set ptest2=5 where ptest2=2;
ERROR:  insert or update on table "fktable" violates foreign key constraint "constrname3"
DETAIL:  Key (ftest1,ftest2,ftest3)=(1,-1,3) is not present in table "pktable".
-- Try to update something that will set default
UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
UPDATE PKTABLE set ptest2=10 where ptest2=4;
-- Try to update something that should not set default
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |      3 |      4 | test3
      2 |     -1 |      5 | test5
      0 |      5 |     10 | test1
      2 |     10 |      5 | test4
      1 |      2 |      3 | test2
(5 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
      2 |      3 |      4 |      1
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      0 |     -1 |        |      1
      2 |     -1 |      5 |      1
(7 rows)

-- Try to delete something that should set null
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |     -1 |      5 | test5
      0 |      5 |     10 | test1
      2 |     10 |      5 | test4
      1 |      2 |      3 | test2
(4 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2
      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      0 |     -1 |        |      1
      2 |     -1 |      5 |      1
        |        |        |      1
(7 rows)

-- Try to delete something that should not set null
DELETE FROM PKTABLE where ptest2=5;
-- Show PKTABLE and FKTABLE
SELECT * from PKTABLE;
 ptest1 | ptest2 | ptest3 | ptest4 
--------+--------+--------+--------
      2 |     -1 |      5 | test5
      2 |     10 |      5 | test4
      1 |      2 |      3 | test2
(3 rows)

SELECT * from FKTABLE;
 ftest1 | ftest2 | ftest3 | ftest4 
--------+--------+--------+--------
        |      2 |      3 |      2