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

Commit208755e

Browse files
alvherrepull[bot]
authored andcommitted
Add test for inherited CHECK constraint drop
This code is insufficiently covered by tests, so add a few small testcases to immortalize its behavior before it gets rewritten completely bythe project to catalog NOT NULL constraints.
1 parent1750234 commit208755e

File tree

2 files changed

+148
-0
lines changed

2 files changed

+148
-0
lines changed

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

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1283,6 +1283,115 @@ order by 1, 2;
12831283

12841284
drop table p1 cascade;
12851285
NOTICE: drop cascades to table p1_c1
1286+
--
1287+
-- Test DROP behavior of multiply-defined CHECK constraints
1288+
--
1289+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1290+
create table p1_c1 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1291+
NOTICE: merging column "f1" with inherited definition
1292+
NOTICE: merging constraint "f1_pos" with inherited definition
1293+
alter table p1_c1 drop constraint f1_pos;
1294+
ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1"
1295+
alter table p1 drop constraint f1_pos;
1296+
\d p1_c1
1297+
Table "public.p1_c1"
1298+
Column | Type | Collation | Nullable | Default
1299+
--------+---------+-----------+----------+---------
1300+
f1 | integer | | |
1301+
Check constraints:
1302+
"f1_pos" CHECK (f1 > 0)
1303+
Inherits: p1
1304+
1305+
drop table p1 cascade;
1306+
NOTICE: drop cascades to table p1_c1
1307+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1308+
create table p2(f1 int constraint f1_pos CHECK (f1 > 0));
1309+
create table p1p2_c1 (f1 int) inherits (p1, p2);
1310+
NOTICE: merging multiple inherited definitions of column "f1"
1311+
NOTICE: merging column "f1" with inherited definition
1312+
create table p1p2_c2 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1, p2);
1313+
NOTICE: merging multiple inherited definitions of column "f1"
1314+
NOTICE: merging column "f1" with inherited definition
1315+
NOTICE: merging constraint "f1_pos" with inherited definition
1316+
alter table p2 drop constraint f1_pos;
1317+
alter table p1 drop constraint f1_pos;
1318+
\d p1p2_c*
1319+
Table "public.p1p2_c1"
1320+
Column | Type | Collation | Nullable | Default
1321+
--------+---------+-----------+----------+---------
1322+
f1 | integer | | |
1323+
Inherits: p1,
1324+
p2
1325+
1326+
Table "public.p1p2_c2"
1327+
Column | Type | Collation | Nullable | Default
1328+
--------+---------+-----------+----------+---------
1329+
f1 | integer | | |
1330+
Check constraints:
1331+
"f1_pos" CHECK (f1 > 0)
1332+
Inherits: p1,
1333+
p2
1334+
1335+
drop table p1, p2 cascade;
1336+
NOTICE: drop cascades to 2 other objects
1337+
DETAIL: drop cascades to table p1p2_c1
1338+
drop cascades to table p1p2_c2
1339+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1340+
create table p1_c1() inherits (p1);
1341+
create table p1_c2() inherits (p1);
1342+
create table p1_c1c2() inherits (p1_c1, p1_c2);
1343+
NOTICE: merging multiple inherited definitions of column "f1"
1344+
\d p1_c1c2
1345+
Table "public.p1_c1c2"
1346+
Column | Type | Collation | Nullable | Default
1347+
--------+---------+-----------+----------+---------
1348+
f1 | integer | | |
1349+
Check constraints:
1350+
"f1_pos" CHECK (f1 > 0)
1351+
Inherits: p1_c1,
1352+
p1_c2
1353+
1354+
alter table p1 drop constraint f1_pos;
1355+
\d p1_c1c2
1356+
Table "public.p1_c1c2"
1357+
Column | Type | Collation | Nullable | Default
1358+
--------+---------+-----------+----------+---------
1359+
f1 | integer | | |
1360+
Inherits: p1_c1,
1361+
p1_c2
1362+
1363+
drop table p1 cascade;
1364+
NOTICE: drop cascades to 3 other objects
1365+
DETAIL: drop cascades to table p1_c1
1366+
drop cascades to table p1_c2
1367+
drop cascades to table p1_c1c2
1368+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1369+
create table p1_c1() inherits (p1);
1370+
create table p1_c2(constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1371+
NOTICE: merging constraint "f1_pos" with inherited definition
1372+
create table p1_c1c2() inherits (p1_c1, p1_c2, p1);
1373+
NOTICE: merging multiple inherited definitions of column "f1"
1374+
NOTICE: merging multiple inherited definitions of column "f1"
1375+
alter table p1_c2 drop constraint f1_pos;
1376+
ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c2"
1377+
alter table p1 drop constraint f1_pos;
1378+
alter table p1_c1c2 drop constraint f1_pos;
1379+
ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1c2"
1380+
alter table p1_c2 drop constraint f1_pos;
1381+
\d p1_c1c2
1382+
Table "public.p1_c1c2"
1383+
Column | Type | Collation | Nullable | Default
1384+
--------+---------+-----------+----------+---------
1385+
f1 | integer | | |
1386+
Inherits: p1_c1,
1387+
p1_c2,
1388+
p1
1389+
1390+
drop table p1 cascade;
1391+
NOTICE: drop cascades to 3 other objects
1392+
DETAIL: drop cascades to table p1_c1
1393+
drop cascades to table p1_c2
1394+
drop cascades to table p1_c1c2
12861395
-- Test that a valid child can have not-valid parent, but not vice versa
12871396
create table invalid_check_con(f1 int);
12881397
create table invalid_check_con_child() inherits(invalid_check_con);

‎src/test/regress/sql/inherit.sql‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -443,6 +443,45 @@ order by 1, 2;
443443

444444
droptable p1 cascade;
445445

446+
--
447+
-- Test DROP behavior of multiply-defined CHECK constraints
448+
--
449+
createtablep1(f1intconstraint f1_posCHECK (f1>0));
450+
createtablep1_c1 (f1intconstraint f1_posCHECK (f1>0)) inherits (p1);
451+
altertable p1_c1 dropconstraint f1_pos;
452+
altertable p1 dropconstraint f1_pos;
453+
\d p1_c1
454+
droptable p1 cascade;
455+
456+
createtablep1(f1intconstraint f1_posCHECK (f1>0));
457+
createtablep2(f1intconstraint f1_posCHECK (f1>0));
458+
createtablep1p2_c1 (f1int) inherits (p1, p2);
459+
createtablep1p2_c2 (f1intconstraint f1_posCHECK (f1>0)) inherits (p1, p2);
460+
altertable p2 dropconstraint f1_pos;
461+
altertable p1 dropconstraint f1_pos;
462+
\d p1p2_c*
463+
droptable p1, p2 cascade;
464+
465+
createtablep1(f1intconstraint f1_posCHECK (f1>0));
466+
createtablep1_c1() inherits (p1);
467+
createtablep1_c2() inherits (p1);
468+
createtablep1_c1c2() inherits (p1_c1, p1_c2);
469+
\d p1_c1c2
470+
altertable p1 dropconstraint f1_pos;
471+
\d p1_c1c2
472+
droptable p1 cascade;
473+
474+
createtablep1(f1intconstraint f1_posCHECK (f1>0));
475+
createtablep1_c1() inherits (p1);
476+
createtablep1_c2(constraint f1_posCHECK (f1>0)) inherits (p1);
477+
createtablep1_c1c2() inherits (p1_c1, p1_c2, p1);
478+
altertable p1_c2 dropconstraint f1_pos;
479+
altertable p1 dropconstraint f1_pos;
480+
altertable p1_c1c2 dropconstraint f1_pos;
481+
altertable p1_c2 dropconstraint f1_pos;
482+
\d p1_c1c2
483+
droptable p1 cascade;
484+
446485
-- Test that a valid child can have not-valid parent, but not vice versa
447486
createtableinvalid_check_con(f1int);
448487
createtableinvalid_check_con_child() inherits(invalid_check_con);

0 commit comments

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