foreign_key.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 1,300 行 · 第 1/4 页

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, explicitly 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".
-- explicitly 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, explicitly 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 fail
-- varchar does not promote to real
ALTER TABLE fktable ADD CONSTRAINT fk_2_3
FOREIGN KEY (x2) REFERENCES pktable(id3);
ERROR:  foreign key constraint "fk_2_3" cannot be implemented
DETAIL:  Key columns "x2" and "id3" are of incompatible types: character varying and real.
-- nor to int4
ALTER TABLE fktable ADD CONSTRAINT fk_2_1
FOREIGN KEY (x2) REFERENCES pktable(id1);
ERROR:  foreign key constraint "fk_2_1" cannot be implemented
DETAIL:  Key columns "x2" and "id1" are of incompatible types: character varying and integer.
-- real does not promote to int4
ALTER TABLE fktable ADD CONSTRAINT fk_3_1
FOREIGN KEY (x3) REFERENCES pktable(id1);
ERROR:  foreign key constraint "fk_3_1" cannot be implemented
DETAIL:  Key columns "x3" and "id1" are of incompatible types: real and integer.
-- int4 does not promote to text
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
ERROR:  foreign key constraint "fk_1_2" cannot be implemented
DETAIL:  Key columns "x1" and "id2" are of incompatible types: integer and character varying.
-- should succeed
-- 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 integer opfamily 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
-- these should work
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);
-- these should fail
ALTER TABLE fktable ADD CONSTRAINT fk_123_231
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
ERROR:  foreign key constraint "fk_123_231" cannot be implemented
DETAIL:  Key columns "x1" and "id2" are of incompatible types: integer and character varying.
ALTER TABLE fktable ADD CONSTRAINT fk_241_132
FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
ERROR:  foreign key constraint "fk_241_132" cannot be implemented
DETAIL:  Key columns "x2" and "id1" are of incompatible types: character varying and integer.
DROP TABLE pktable, fktable CASCADE;
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 fktable_x1_fkey on table fktable
NOTICE:  drop cascades to constraint fk_4_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 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".
-- check same case when insert is in a different subtransaction than update
BEGIN;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
-- UPDATE will be in a subxact
SAVEPOINT savept1;
-- 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".
BEGIN;
-- INSERT will be in a subxact
SAVEPOINT savept1;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
RELEASE SAVEPOINT savept1;
-- 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".
BEGIN;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
-- UPDATE will be in a subxact
SAVEPOINT savept1;
-- don't change FK
UPDATE fktable SET id = id + 1;
-- Roll back the UPDATE
ROLLBACK TO savept1;
-- 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".
-- test order of firing of FK triggers when several RI-induced changes need to
-- be made to the same row.  This was broken by subtransaction-related
-- changes in 8.0.
CREATE TEMP TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
INSERT INTO users VALUES (1, 'Jozko');
INSERT INTO users VALUES (2, 'Ferko');
INSERT INTO users VALUES (3, 'Samko');
CREATE TEMP TABLE tasks (
  id INT PRIMARY KEY,
  owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
  worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
  checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tasks_pkey" for table "tasks"
INSERT INTO tasks VALUES (1,1,NULL,NULL);
INSERT INTO tasks VALUES (2,2,2,NULL);
INSERT INTO tasks VALUES (3,3,3,3);
SELECT * FROM tasks;
 id | owner | worker | checked_by 
----+-------+--------+------------
  1 |     1 |        |           
  2 |     2 |      2 |           
  3 |     3 |      3 |          3
(3 rows)

UPDATE users SET id = 4 WHERE id = 3;
SELECT * FROM tasks;
 id | owner | worker | checked_by 
----+-------+--------+------------
  1 |     1 |        |           
  2 |     2 |      2 |           
  3 |     4 |      4 |          4
(3 rows)

DELETE FROM users WHERE id = 4;
SELECT * FROM tasks;
 id | owner | worker | checked_by 
----+-------+--------+------------
  1 |     1 |        |           
  2 |     2 |      2 |           
  3 |       |        |           
(3 rows)

-- could fail with only 2 changes to make, if row was already updated
BEGIN;
UPDATE tasks set id=id WHERE id=2;
SELECT * FROM tasks;
 id | owner | worker | checked_by 
----+-------+--------+------------
  1 |     1 |        |           
  3 |       |        |           
  2 |     2 |      2 |           
(3 rows)

DELETE FROM users WHERE id = 2;
SELECT * FROM tasks;
 id | owner | worker | checked_by 
----+-------+--------+------------
  1 |     1 |        |           
  3 |       |        |           
  2 |       |        |           
(3 rows)

COMMIT;