foreign_key.out

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

 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".
CONTEXT:  SQL statement "UPDATE ONLY "public"."fktable" SET "ftest2" = DEFAULT WHERE "ftest1" = $1 AND "ftest2" = $2 AND "ftest3" = $3"
-- 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;
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:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
-- This should also fail for the same reason, but here we
-- give the column name
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1));
ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
-- This should succeed (with a warning), even though they are different types
-- because int=varchar does exist
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable);
WARNING:  foreign key constraint "fktable_ftest1_fkey" will require costly sequential scans
DETAIL:  Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
DROP TABLE FKTABLE;
-- As should this
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
WARNING:  foreign key constraint "fktable_ftest1_fkey" will require costly sequential scans
DETAIL:  Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
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:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
-- Again, so should this...
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
-- This fails because we mixed up the column ordering
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 "ptest1" are of incompatible types: inet and integer.
-- As does this...
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
ERROR:  foreign key constraint "fktable_ftest2_fkey" cannot be implemented
DETAIL:  Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
-- And again..
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
-- 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:  foreign key constraint "pktable_ptest3_fkey" cannot be implemented
DETAIL:  Key columns "ptest3" and "ptest2" are of incompatible types: integer and inet.
-- 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:  foreign key constraint "pktable_ptest4_fkey" cannot be implemented
DETAIL:  Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer.
-- 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:  foreign key constraint "pktable_ptest4_fkey" cannot be implemented
DETAIL:  Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer.
--
-- 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 "fktable_ftest1_fkey"
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 "fktable_ftest1_fkey" 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 "fktable_ftest1_fkey" 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 "fktable_ftest1_fkey"
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 "fktable_ftest1_fkey" 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