*** ./expected/alter_table.out Thu May 10 05:02:48 2001 --- ./results/alter_table.out Thu May 10 05:09:16 2001 *************** *** 347,372 **** CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: Unable to identify an operator '=' for types 'cidr' and 'int4' ! You will have to retype this query using an explicit cast -- Again, so should this... DROP TABLE FKTABLE; CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: Unable to identify an operator '=' for types 'cidr' and 'int4' ! You will have to retype this query using an explicit cast -- This fails because we mixed up the column ordering DROP TABLE FKTABLE; CREATE TABLE FKTABLE (ftest1 int, ftest2 text); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: Unable to identify an operator '=' for types 'int4' and 'text' ! You will have to retype this query using an explicit cast -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: Unable to identify an operator '=' for types 'text' and 'int4' ! You will have to retype this query using an explicit cast DROP TABLE FKTABLE; DROP TABLE PKTABLE; --- 347,369 ---- CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: RelationClearRelation: relation 144096 deleted while still in use -- Again, so should this... DROP TABLE FKTABLE; CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: Relation 'pktable' does not exist -- This fails because we mixed up the column ordering DROP TABLE FKTABLE; CREATE TABLE FKTABLE (ftest1 int, ftest2 text); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ! ERROR: RelationClearRelation: relation 144277 deleted while still in use DROP TABLE FKTABLE; + ERROR: table "fktable" does not exist DROP TABLE PKTABLE; ====================================================================== *** ./expected/foreign_key.out Thu May 10 05:02:48 2001 --- ./results/foreign_key.out Thu May 10 05:09:18 2001 *************** *** 574,697 **** -- 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); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- 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: constrname3 referential integrity violation - key referenced from fktable not found in 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: constrname3 referential integrity violation - key referenced from fktable not found in 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 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 0 | -1 | | 1 ! 2 | -1 | 5 | 1 ! | | | 1 ! (7 rows) ! DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: columns referenced in foreign key constraint not found. --- 574,660 ---- -- 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' + ERROR: Cannot insert a duplicate key into unique index pg_class_relname_index 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); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5'); + ERROR: INSERT has more expressions than target columns -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 3, 4, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 4, 5, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); + ERROR: INSERT has more expressions than target columns INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); + ERROR: INSERT has more expressions than target columns INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); + ERROR: INSERT has more expressions than target columns -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: INSERT has more expressions than target columns -- Show FKTABLE SELECT * from FKTABLE; ! ftest1 ! -------- ! (0 rows) -- Try to update something that will fail UPDATE PKTABLE set ptest2=5 where ptest2=2; ! ERROR: RelationClearRelation: relation 143823 deleted while still in use -- Try to update something that will set default UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2; + ERROR: Relation 'pktable' does not exist UPDATE PKTABLE set ptest2=10 where ptest2=4; + ERROR: Relation 'pktable' does not exist -- Try to update something that should not set default UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; + ERROR: Relation 'pktable' does not exist -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ERROR: Relation 'pktable' does not exist SELECT * from FKTABLE; ! ERROR: RelationClearRelation: relation 144013 deleted while still in use -- Try to delete something that should set null DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; + ERROR: Relation 'pktable' does not exist -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ERROR: Relation 'pktable' does not exist SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should not set null DELETE FROM PKTABLE where ptest2=5; + ERROR: Relation 'pktable' does not exist -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ERROR: Relation 'pktable' does not exist SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; + ERROR: table "pktable" does not exist CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Cannot insert a duplicate key into unique index pg_class_relname_index CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: columns referenced in foreign key constraint not found. *************** *** 733,748 **** -- because varchar=int does exist CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE FKTABLE; - NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" - NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" -- As should this CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; -- Two columns, two tables CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' --- 696,711 ---- -- because varchar=int does exist CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Relation 'fktable' already exists DROP TABLE FKTABLE; -- As should this CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: RelationClearRelation: relation 144234 deleted while still in use DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; + ERROR: table "pktable" does not exist -- Two columns, two tables CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' ======================================================================