Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit304e9f0

Browse files
committed
Increase test coverage in RI_Initial_Check()
This covers the special error handling of FKCONSTR_MATCH_FULL.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 parent45ed6e1 commit304e9f0

File tree

2 files changed

+34
-0
lines changed

2 files changed

+34
-0
lines changed

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -339,6 +339,18 @@ SELECT * FROM PKTABLE;
339339
0 | Test4
340340
(4 rows)
341341

342+
DROP TABLE FKTABLE;
343+
DROP TABLE PKTABLE;
344+
--
345+
-- Check initial check upon ALTER TABLE
346+
--
347+
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, PRIMARY KEY(ptest1, ptest2) );
348+
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int );
349+
INSERT INTO PKTABLE VALUES (1, 2);
350+
INSERT INTO FKTABLE VALUES (1, NULL);
351+
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL;
352+
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
353+
DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
342354
DROP TABLE FKTABLE;
343355
DROP TABLE PKTABLE;
344356
-- MATCH SIMPLE
@@ -1607,6 +1619,11 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL);
16071619
INSERT INTO fk_notpartitioned_pk VALUES (1, 2);
16081620
CREATE TABLE fk_partitioned_fk_full (x int, y int) PARTITION BY RANGE (x);
16091621
CREATE TABLE fk_partitioned_fk_full_1 PARTITION OF fk_partitioned_fk_full DEFAULT;
1622+
INSERT INTO fk_partitioned_fk_full VALUES (1, NULL);
1623+
ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL; -- fails
1624+
ERROR: insert or update on table "fk_partitioned_fk_full" violates foreign key constraint "fk_partitioned_fk_full_x_fkey"
1625+
DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
1626+
TRUNCATE fk_partitioned_fk_full;
16101627
ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL;
16111628
INSERT INTO fk_partitioned_fk_full VALUES (1, NULL); -- fails
16121629
ERROR: insert or update on table "fk_partitioned_fk_full_1" violates foreign key constraint "fk_partitioned_fk_full_x_fkey"

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -219,6 +219,20 @@ SELECT * FROM PKTABLE;
219219
DROPTABLE FKTABLE;
220220
DROPTABLE PKTABLE;
221221

222+
--
223+
-- Check initial check upon ALTER TABLE
224+
--
225+
CREATETABLEPKTABLE ( ptest1int, ptest2int,PRIMARY KEY(ptest1, ptest2) );
226+
CREATETABLEFKTABLE ( ftest1int, ftest2int );
227+
228+
INSERT INTO PKTABLEVALUES (1,2);
229+
INSERT INTO FKTABLEVALUES (1,NULL);
230+
231+
ALTERTABLE FKTABLE ADDFOREIGN KEY(ftest1, ftest2)REFERENCES PKTABLE MATCH FULL;
232+
233+
DROPTABLE FKTABLE;
234+
DROPTABLE PKTABLE;
235+
222236

223237
-- MATCH SIMPLE
224238

@@ -1214,6 +1228,9 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL);
12141228
INSERT INTO fk_notpartitioned_pkVALUES (1,2);
12151229
CREATETABLEfk_partitioned_fk_full (xint, yint) PARTITION BY RANGE (x);
12161230
CREATETABLEfk_partitioned_fk_full_1 PARTITION OF fk_partitioned_fk_full DEFAULT;
1231+
INSERT INTO fk_partitioned_fk_fullVALUES (1,NULL);
1232+
ALTERTABLE fk_partitioned_fk_full ADDFOREIGN KEY (x, y)REFERENCES fk_notpartitioned_pk MATCH FULL;-- fails
1233+
TRUNCATE fk_partitioned_fk_full;
12171234
ALTERTABLE fk_partitioned_fk_full ADDFOREIGN KEY (x, y)REFERENCES fk_notpartitioned_pk MATCH FULL;
12181235
INSERT INTO fk_partitioned_fk_fullVALUES (1,NULL);-- fails
12191236
DROPTABLE fk_partitioned_fk_full;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp