NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commit74bd066

committed

Add test case for ON DELETE NO ACTION/RESTRICT

This was previously not covered at all; functionRI_FKey_restrict_del() was not exercised in the tests.Reviewed-by: Alvaro Herrera <alvherre@2ndquadrant.com>Reviewed-by: Mi Tar <mmitar@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/7ae17c95-0c99-d420-032a-c271f510112b@2ndquadrant.com/

1 parent90525d7 commit74bd066Copy full SHA for 74bd066

File tree

2 files changed

+12

-4

lines changed

src/test/regress
- expected
  - foreign_key.out
- sql
  - foreign_key.sql

2 files changed

+12

-4

lines changed

`‎src/test/regress/expected/foreign_key.out`

Lines changed: 8 additions & 2 deletions

Original file line number	Diff line number	Diff line change
`@@ -1339,7 +1339,7 @@ DETAIL: Key (f1)=(1) is still referenced from table "defc".`
`1339`	`1339`	`-- Test the difference between NO ACTION and RESTRICT`
`1340`	`1340`	`--`
`1341`	`1341`	`create temp table pp (f1 int primary key);`
`1342`		`-create temp table cc (f1 int references pp on update no action);`
	`1342`	`+create temp table cc (f1 int references pp on update no action on delete no action);`
`1343`	`1343`	`insert into pp values(12);`
`1344`	`1344`	`insert into pp values(11);`
`1345`	`1345`	`update pp set f1=f1+1;`
`@@ -1348,16 +1348,22 @@ update pp set f1=f1+1;`
`1348`	`1348`	`update pp set f1=f1+1; -- fail`
`1349`	`1349`	`ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"`
`1350`	`1350`	`DETAIL: Key (f1)=(13) is still referenced from table "cc".`
	`1351`	`+delete from pp where f1 = 13; -- fail`
	`1352`	`+ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"`
	`1353`	`+DETAIL: Key (f1)=(13) is still referenced from table "cc".`
`1351`	`1354`	`drop table pp, cc;`
`1352`	`1355`	`create temp table pp (f1 int primary key);`
`1353`		`-create temp table cc (f1 int references pp on update restrict);`
	`1356`	`+create temp table cc (f1 int references pp on update restrict on delete restrict);`
`1354`	`1357`	`insert into pp values(12);`
`1355`	`1358`	`insert into pp values(11);`
`1356`	`1359`	`update pp set f1=f1+1;`
`1357`	`1360`	`insert into cc values(13);`
`1358`	`1361`	`update pp set f1=f1+1; -- fail`
`1359`	`1362`	`ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"`
`1360`	`1363`	`DETAIL: Key (f1)=(13) is still referenced from table "cc".`
	`1364`	`+delete from pp where f1 = 13; -- fail`
	`1365`	`+ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"`
	`1366`	`+DETAIL: Key (f1)=(13) is still referenced from table "cc".`
`1361`	`1367`	`drop table pp, cc;`
`1362`	`1368`	`--`
`1363`	`1369`	`-- Test interaction of foreign-key optimization with rules (bug #14219)`

`‎src/test/regress/sql/foreign_key.sql`

Lines changed: 4 additions & 2 deletions

Original file line number	Diff line number	Diff line change
`@@ -992,22 +992,24 @@ delete from defp where f1 = 1; -- fail`
`992`	`992`	`-- Test the difference between NO ACTION and RESTRICT`
`993`	`993`	`--`
`994`	`994`	`create temp table pp (f1intprimary key);`
`995`		`-create temp table cc (f1intreferences pponupdate no action);`
	`995`	`+create temp table cc (f1intreferences pponupdate no actionon delete no action);`
`996`	`996`	`insert into ppvalues(12);`
`997`	`997`	`insert into ppvalues(11);`
`998`	`998`	`update ppset f1=f1+1;`
`999`	`999`	`insert into ccvalues(13);`
`1000`	`1000`	`update ppset f1=f1+1;`
`1001`	`1001`	`update ppset f1=f1+1;-- fail`
	`1002`	`+deletefrom ppwhere f1=13;-- fail`
`1002`	`1003`	`droptable pp, cc;`
`1003`	`1004`
`1004`	`1005`	`create temp table pp (f1intprimary key);`
`1005`		`-create temp table cc (f1intreferences pponupdate restrict);`
	`1006`	`+create temp table cc (f1intreferences pponupdate restricton delete restrict);`
`1006`	`1007`	`insert into ppvalues(12);`
`1007`	`1008`	`insert into ppvalues(11);`
`1008`	`1009`	`update ppset f1=f1+1;`
`1009`	`1010`	`insert into ccvalues(13);`
`1010`	`1011`	`update ppset f1=f1+1;-- fail`
	`1012`	`+deletefrom ppwhere f1=13;-- fail`
`1011`	`1013`	`droptable pp, cc;`
`1012`	`1014`
`1013`	`1015`	`--`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit74bd066

File tree

2 files changed

2 files changed

`‎src/test/regress/expected/foreign_key.out`

`‎src/test/regress/sql/foreign_key.sql`

0 commit comments