foreign_key.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

-- which should fail.
update pktable set base1=base1*4;
ERROR:  update or delete on "pktable" violates foreign key constraint "fktable_ftest1_fkey" 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 "pktable_base2_fkey"
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"
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"
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:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "base1" are of incompatible types: cidr and integer.
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1));
ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "base1" are of incompatible types: cidr and integer.
-- let's mix up which columns reference which
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable);
ERROR:  foreign key constraint "fktable_ftest2_fkey" cannot be implemented
DETAIL:  Key columns "ftest2" and "base1" are of incompatible types: inet and integer.
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
ERROR:  foreign key constraint "fktable_ftest2_fkey" cannot be implemented
DETAIL:  Key columns "ftest2" and "base1" are of incompatible types: inet and integer.
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: integer and inet.
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:  foreign key constraint "pktable_base2_fkey" cannot be implemented
DETAIL:  Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet.
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:  foreign key constraint "pktable_base2_fkey" cannot be implemented
DETAIL:  Key columns "base2" and "ptest1" are of incompatible types: integer and inet.
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:  foreign key constraint "pktable_ptest2_fkey" cannot be implemented
DETAIL:  Key columns "ptest2" and "base1" are of incompatible types: inet and integer.
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:  foreign key constraint "pktable_ptest2_fkey" cannot be implemented
DETAIL:  Key columns "ptest2" and "base1" are of incompatible types: inet and integer.
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 "fktable_fk_fkey"
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 "fktable_fk_fkey"
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 "fktable_fk_fkey"
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 "fktable_fk_fkey"
DETAIL:  Key (fk)=(200) is not present in table "pktable".
DROP TABLE pktable, fktable CASCADE;
NOTICE:  drop cascades to constraint fktable_fk_fkey on table fktable
-- test notice about expensive referential integrity checks,
-- where the index cannot be used because of type incompatibilities.
CREATE TEMP TABLE pktable (
        id1     INT4 PRIMARY KEY,
        id2     VARCHAR(4) UNIQUE,
        id3     REAL UNIQUE,
        UNIQUE(id1, id2, id3)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pktable_id2_key" for table "pktable"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pktable_id3_key" for table "pktable"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pktable_id1_key" for table "pktable"
CREATE TEMP TABLE fktable (
        x1      INT4 REFERENCES pktable(id1),
        x2      VARCHAR(4) REFERENCES pktable(id2),
        x3      REAL REFERENCES pktable(id3),
        x4      TEXT,
        x5      INT2
);
-- check individual constraints with alter table.
-- should generate warnings
ALTER TABLE fktable ADD CONSTRAINT fk_2_3
FOREIGN KEY (x2) REFERENCES pktable(id3);
WARNING:  foreign key constraint "fk_2_3" will require costly sequential scans
DETAIL:  Key columns "x2" and "id3" are of different types: character varying and real.
ALTER TABLE fktable ADD CONSTRAINT fk_2_1
FOREIGN KEY (x2) REFERENCES pktable(id1);
WARNING:  foreign key constraint "fk_2_1" will require costly sequential scans
DETAIL:  Key columns "x2" and "id1" are of different types: character varying and integer.
ALTER TABLE fktable ADD CONSTRAINT fk_3_1
FOREIGN KEY (x3) REFERENCES pktable(id1);
WARNING:  foreign key constraint "fk_3_1" will require costly sequential scans
DETAIL:  Key columns "x3" and "id1" are of different types: real and integer.
-- should NOT generate warnings
-- int4 promotes to text, so this is ok
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
-- int4 promotes to real
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
FOREIGN KEY (x1) REFERENCES pktable(id3);
-- text is compatible with varchar
ALTER TABLE fktable ADD CONSTRAINT fk_4_2
FOREIGN KEY (x4) REFERENCES pktable(id2);
-- int2 is part of int4 opclass as of 8.0
ALTER TABLE fktable ADD CONSTRAINT fk_5_1
FOREIGN KEY (x5) REFERENCES pktable(id1);
-- check multikey cases, especially out-of-order column lists
-- no warnings here
ALTER TABLE fktable ADD CONSTRAINT fk_123_123
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3);
ALTER TABLE fktable ADD CONSTRAINT fk_213_213
FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3);
ALTER TABLE fktable ADD CONSTRAINT fk_253_213
FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3);
-- warnings here
ALTER TABLE fktable ADD CONSTRAINT fk_123_231
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
WARNING:  foreign key constraint "fk_123_231" will require costly sequential scans
DETAIL:  Key columns "x2" and "id3" are of different types: character varying and real.
WARNING:  foreign key constraint "fk_123_231" will require costly sequential scans
DETAIL:  Key columns "x3" and "id1" are of different types: real and integer.
ALTER TABLE fktable ADD CONSTRAINT fk_241_132
FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
WARNING:  foreign key constraint "fk_241_132" will require costly sequential scans
DETAIL:  Key columns "x2" and "id1" are of different types: character varying and integer.
WARNING:  foreign key constraint "fk_241_132" will require costly sequential scans
DETAIL:  Key columns "x4" and "id3" are of different types: text and real.
DROP TABLE pktable, fktable CASCADE;
NOTICE:  drop cascades to constraint fk_241_132 on table fktable
NOTICE:  drop cascades to constraint fk_123_231 on table fktable
NOTICE:  drop cascades to constraint fk_253_213 on table fktable
NOTICE:  drop cascades to constraint fk_213_213 on table fktable
NOTICE:  drop cascades to constraint fk_123_123 on table fktable
NOTICE:  drop cascades to constraint fk_5_1 on table fktable
NOTICE:  drop cascades to constraint fk_3_1 on table fktable
NOTICE:  drop cascades to constraint fk_2_1 on table fktable
NOTICE:  drop cascades to constraint fktable_x1_fkey on table fktable
NOTICE:  drop cascades to constraint fk_4_2 on table fktable
NOTICE:  drop cascades to constraint fk_1_2 on table fktable
NOTICE:  drop cascades to constraint fktable_x2_fkey on table fktable
NOTICE:  drop cascades to constraint fk_1_3 on table fktable
NOTICE:  drop cascades to constraint fk_2_3 on table fktable
NOTICE:  drop cascades to constraint fktable_x3_fkey on table fktable
-- test a tricky case: we can elide firing the FK check trigger during
-- an UPDATE if the UPDATE did not change the foreign key
-- field. However, we can't do this if our transaction was the one that
-- created the updated row and the trigger is deferred, since our UPDATE
-- will have invalidated the original newly-inserted tuple, and therefore
-- cause the on-INSERT RI trigger not to be fired.
CREATE TEMP TABLE pktable (
    id int primary key,
    other int
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TEMP TABLE fktable (
    id int primary key,
    fk int references pktable deferrable initially deferred
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
INSERT INTO pktable VALUES (5, 10);
BEGIN;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
-- don't change FK
UPDATE fktable SET id = id + 1;
-- should catch error from initial INSERT
COMMIT;
ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
DETAIL:  Key (fk)=(20) is not present in table "pktable".