foreign_key.out

PostgreSQL7.4.6 for Linux

      2 |        |      3 |      3
        |      2 |      7 |      4
        |      3 |      4 |      5
      0 |     -1 |        |      1
      2 |     -1 |      5 |      1
        |        |        |      1
(7 rows)

DROP TABLE FKTABLE;
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);
ERROR:  column "ftest2" referenced in foreign key constraint does not exist
CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
ERROR:  column "ptest2" referenced in foreign key constraint does not exist
DROP TABLE FKTABLE_FAIL1;
ERROR:  table "fktable_fail1" does not exist
DROP TABLE FKTABLE_FAIL2;
ERROR:  table "fktable_fail2" does not exist
DROP TABLE PKTABLE;
-- Test for referencing column number smaller than referenced constraint
CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pktable_ptest1_key" for table "pktable"
CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
ERROR:  there is no unique constraint matching given keys for referenced table "pktable"
DROP TABLE FKTABLE_FAIL1;
ERROR:  table "fktable_fail1" does not exist
DROP TABLE PKTABLE;
--
-- Tests for mismatched types
--
-- Basic one column, two table setup 
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
-- This next should fail, because inet=int does not exist
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable);
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- This should also fail for the same reason, but here we
-- give the column name
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1));
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- This should succeed, even though they are different types
-- because varchar=int does exist
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable);
DROP TABLE FKTABLE;
-- As should this
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- Two columns, two tables
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
-- This should fail, because we just chose really odd types
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable);
ERROR:  operator does not exist: cidr = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- Again, so should this...
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
ERROR:  operator does not exist: cidr = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- This fails because we mixed up the column ordering
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- As does this...
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- And again..
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
ERROR:  operator does not exist: integer = inet
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- This works...
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
DROP TABLE FKTABLE;
-- As does this
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- Two columns, same table
-- Make sure this still works...
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable(ptest1, ptest2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
DROP TABLE PKTABLE;
-- And this, 
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
DROP TABLE PKTABLE;
-- This shouldn't (mixed up columns)
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable(ptest2, ptest1));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: integer = inet
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
ptest3) REFERENCES pktable(ptest1, ptest2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- Not this one either... Same as the last one except we didn't defined the columns being referenced.
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
ptest3) REFERENCES pktable);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
--
-- Now some cases with inheritance
-- Basic 2 table case: 1 column of matching types.
create table pktable_base (base1 int not null);
create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pktable_base1_key" for table "pktable"
create table fktable (ftest1 int references pktable(base1));
-- now some ins, upd, del
insert into pktable(base1) values (1);
insert into pktable(base1) values (2);
--  let's insert a non-existant fktable value
insert into fktable(ftest1) values (3);
ERROR:  insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL:  Key (ftest1)=(3) is not present in table "pktable".
--  let's make a valid row for that
insert into pktable(base1) values (3);
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 "$1" on "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 "$1" on "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;
-- and a delete that should work
delete from pktable where base1>3;
-- cleanup
drop table fktable;
delete from pktable;
-- Now 2 columns 2 tables, matching types
create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1));
-- now some ins, upd, del
insert into pktable(base1, ptest1) values (1, 1);
insert into pktable(base1, ptest1) values (2, 2);
--  let's insert a non-existant fktable value
insert into fktable(ftest1, ftest2) values (3, 1);
ERROR:  insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL:  Key (ftest1,ftest2)=(3,1) is not present in table "pktable".
--  let's make a valid row for that
insert into pktable(base1,ptest1) values (3, 1);
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 "$1" on "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 "$1" on "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;
-- and a delete that should work
delete from pktable where base1>3;
-- cleanup
drop table fktable;
drop table pktable;
drop table pktable_base;
-- Now we'll do one all in 1 table with 2 columns of matching types
create table pktable_base(base1 int not null, base2 int);
create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references
                                             pktable(base1, ptest1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1);
insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1);
insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1);
insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2);
-- fails (3,2) isn't in base1, ptest1
insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2);
ERROR:  insert or update on table "pktable" violates foreign key constraint "$1"
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 "$1" on "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 "$1" on "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;
delete from pktable where base1=2;
drop table pktable;
drop table pktable_base;
-- 2 columns (2 tables), mismatched types
create table pktable_base(base1 int not null);
create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
-- just generally bad types (with and without column references on the referenced table)
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
ERROR:  operator does not exist: cidr = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1));
ERROR:  operator does not exist: cidr = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
-- let's mix up which columns reference which
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable);
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
ERROR:  operator does not exist: integer = inet
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
drop table pktable;
drop table pktable_base;
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
                                             pktable(base1, ptest1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: inet[] = inet
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
                                             pktable(ptest1, base1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: integer = inet
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
                                             pktable(base1, ptest1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
                                             pktable(base1, ptest1)) inherits (pktable_base);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
ERROR:  operator does not exist: inet = integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
drop table pktable;
ERROR:  table "pktable" does not exist
drop table pktable_base;
--
-- Deferrable constraints
--		(right now, only FOREIGN KEY constraints can be deferred)
--
-- deferrable, explicitely deferred
CREATE TABLE pktable (
	id		INT4 PRIMARY KEY,
	other	INT4
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE fktable (
	id		INT4 PRIMARY KEY,
	fk		INT4 REFERENCES pktable DEFERRABLE
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
-- default to immediate: should fail
INSERT INTO fktable VALUES (5, 10);
ERROR:  insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL:  Key (fk)=(10) is not present in table "pktable".
-- explicitely defer the constraint
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO fktable VALUES (10, 15);
INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
COMMIT;
DROP TABLE fktable, pktable;
-- deferrable, initially deferred
CREATE TABLE pktable (
	id		INT4 PRIMARY KEY,
	other	INT4
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE fktable (
	id		INT4 PRIMARY KEY,
	fk		INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
-- default to deferred, should succeed
BEGIN;
INSERT INTO fktable VALUES (100, 200);
INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
COMMIT;
-- default to deferred, explicitely make immediate
BEGIN;
SET CONSTRAINTS ALL IMMEDIATE;
-- should fail
INSERT INTO fktable VALUES (500, 1000);
ERROR:  insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL:  Key (fk)=(1000) is not present in table "pktable".
COMMIT;
DROP TABLE fktable, pktable;
-- tricky behavior: according to SQL99, if a deferred constraint is set
-- to 'immediate' mode, it should be checked for validity *immediately*,
-- not when the current transaction commits (i.e. the mode change applies
-- retroactively)
CREATE TABLE pktable (
	id		INT4 PRIMARY KEY,
	other	INT4
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE fktable (
	id		INT4 PRIMARY KEY,
	fk		INT4 REFERENCES pktable DEFERRABLE
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- should succeed, for now
INSERT INTO fktable VALUES (1000, 2000);
-- should cause transaction abort, due to preceding error
SET CONSTRAINTS ALL IMMEDIATE;
ERROR:  insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL:  Key (fk)=(2000) is not present in table "pktable".
INSERT INTO pktable VALUES (2000, 3); -- too late
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
DROP TABLE fktable, pktable;
-- deferrable, initially deferred
CREATE TABLE pktable (
	id		INT4 PRIMARY KEY,
	other	INT4
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TABLE fktable (
	id		INT4 PRIMARY KEY,
	fk		INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
BEGIN;
-- no error here
INSERT INTO fktable VALUES (100, 200);
-- error here on commit
COMMIT;
ERROR:  insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL:  Key (fk)=(200) is not present in table "pktable".