Hi,
Upstream commit 14e87ffa5c543b5f30ead7413084c25f7735039f
<https://github.com/postgres/postgres/commit/14e87ffa5c543b5f30ead7413084c25f7735039f>
added the support for named NOT NULL constraints which are INHERIT by
default.
We can declare those as NO INHERIT which means those constraints will not
be inherited to child tables and after this state, we don't have the
functionality to change the state back to INHERIT.
This patch adds this support where named NOT NULL constraint defined as NO
INHERIT can be changed to INHERIT.
For this, introduced the new syntax something like -
ALTER TABLE <tabname> ALTER CONSTRAINT <constrname> INHERIT;
Once the not null constraints are altered to INHERIT from NO INHERIT,
recurse to all children and propagate the constraint if it doesn't exist.
Alvaro stated that allowing a not null constraint state to be modified from
INHERIT to NO INHERIT is going to be quite problematic because of the
number of weird cases to avoid, so for now that support is not added.
Please share your thoughts on the same.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 6098ebe..348ac38 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,7 +58,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -108,7 +108,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [INHERIT]
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -553,7 +553,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key and not null constraints may be
+ altered. Only Not null constraints can be modified to INHERIT from
+ NO INHERIT which creates constraints in all inherited childrens and
+ validates the same.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ccd9645..360fcdb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,7 +389,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved,
LOCKMODE lockmode);
-static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
+static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
@@ -5400,7 +5400,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
- address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
+ address = ATExecAlterConstraint(wqueue, rel, cmd, false, false,
+ lockmode);
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(wqueue, rel, cmd->name, cmd->recurse,
@@ -11622,8 +11623,8 @@ GetForeignKeyCheckTriggers(Relation trigrel,
* InvalidObjectAddress.
*/
static ObjectAddress
-ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
- bool recursing, LOCKMODE lockmode)
+ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode)
{
Constraint *cmdcon;
Relation conrel;
@@ -11666,7 +11667,74 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
cmdcon->conname, RelationGetRelationName(rel))));
+ contuple = heap_copytuple(contuple);
+
currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ /* Not null constraint */
+ if (cmdcon->contype == CONSTR_NOTNULL)
+ {
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a not null constraint",
+ cmdcon->conname, RelationGetRelationName(rel))));
+
+ address = InvalidObjectAddress;
+
+ /* Return if constraint is already marked as INHERIT. */
+ if (!currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ CommandCounterIncrement();
+
+ ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, true, true, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
if (currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -11742,6 +11810,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
systable_endscan(scan);
+ heap_freetuple(contuple);
+
table_close(tgrel, RowExclusiveLock);
table_close(conrel, RowExclusiveLock);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 67eb963..24c50a1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2661,6 +2661,20 @@ alter_table_cmd:
NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT INHERIT*/
+ | ALTER CONSTRAINT name INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ Constraint *c = makeNode(Constraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->contype = CONSTR_NOTNULL;
+ c->conname = $3;
+ c->is_no_inherit = false;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index bb81f6d..74b6ea2 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2666,6 +2666,383 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Negative scenarios for alter constraint .. inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check inherit;
+ERROR: constraint "part1_f1_check" of relation "part1" is not a not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table foo alter constraint part1_id_not_nul inherit;
+ERROR: constraint "part1_id_not_nul" of relation "foo" does not exist
+drop table part1;
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index f51c70d..ba0f997 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1046,6 +1046,118 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Negative scenarios for alter constraint .. inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table foo alter constraint part1_id_not_nul inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
