Subject: Re: NOT NULL NOT ENFORCED

From: jian he <[email-masked]>
Date: Tue, 3 Mar 2026 16:03:46 +0800

On Mon, Feb 23, 2026 at 11:50 PM Álvaro Herrera <[email-masked]> wrote:
>
> But what is NOT VALID NOT ENFORCED?  Unless I'm confused, a constraint
> can be in one of three cases,
>
> 1. the normal one (valid and enforced), which is dumped together with
>    the table,
> 2. the NOT ENFORCED one, for which there's no validity marking (these
>    are all in practice invalid, but we don't spell that out because it's
>    pointless to do so), which I argue should also be dumped together
>    with the table,
> 3. NOT VALID but enforced, which is dumped separately, because trying to
>    dump it together with the table just won't work.
>
> I understand that you want to treat 2 the same as 3, because it saves a
> dozen of pg_dump lines of code.  I think it's more user friendly to
> treat 2 the same as 1, and that the dozen lines of code are worth it.
>
Hi.

It turns out that the change for NOT ENFORCED NOT NULL is pg_dump.c is
not complicated.

CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED);
Now the pg_dump output is

CREATE TABLE public.ne_nn_tbl (
    x integer CONSTRAINT nn NOT NULL NOT ENFORCED
);

but the 002_pg_dump.pl is failing....

--
jian
https://www.enterprisedb.com/
From e5b51db39c1b11166ec9ffd3e352357e663e49a0 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 3 Mar 2026 16:02:19 +0800
Subject: [PATCH v4 1/1] NOT NULL NOT ENFORCED

This will remove sql_features.txt item F492: "Optional table constraint
enforcement" remarks: "except not-null constraints". See [1].

main points about NOT NULL NOT ENFORCED
* one column can have at most one NOT-NULL constraint, regardless constraints
  property (not enforced or enforced)
* If column already have not enforced not-null constraint then:
  ALTER TABLE ALTER COLUMN SET NOT NULL: error out, cannot validate not
  enforced not-null constraint
  ALTER TABLE ADD NOT NULL: error out, cannot add another not-null constraint,
  one column can only have one.

not null in partitioned table:
* If the partitioned table has an enforced not-null constraint, its partitions
cannot have not enforced.
* If the partitioned table has a NOT ENFORCED not-null constraint, its
partitions may have either ENFORCED or NOT ENFORCED not-null constraints, but
the constraint itself is still required.

not null in table inheritance:
OK: parent is not enforced, while child is enforced
NOT OK: parent is enforced, while child is not enforced
If a column inherits from multiple tables and the ancestor tables have
conflicting ENFORCED statuses, raise an error.

commitfest: https://commitfest.postgresql.org/patch/6029
reference:  https://git.postgresql.org/cgit/postgresql.git/commit/?id=a379061a22a8fdf421e1a457cc6af8503def6252
discussion: https://postgr.es/m/CACJufxFbH1_9BDow=[email-masked]
---
 doc/src/sgml/catalogs.sgml                    |   2 +-
 doc/src/sgml/ref/alter_table.sgml             |   4 +-
 doc/src/sgml/ref/create_table.sgml            |   4 +-
 src/backend/catalog/heap.c                    |  67 +++++--
 src/backend/catalog/pg_constraint.c           |  49 ++++--
 src/backend/catalog/sql_features.txt          |   2 +-
 src/backend/commands/tablecmds.c              | 129 ++++++++++----
 src/backend/parser/gram.y                     |   2 +-
 src/backend/parser/parse_utilcmd.c            |  87 ++++++---
 src/backend/utils/cache/relcache.c            |   6 +-
 src/bin/pg_dump/pg_dump.c                     |  39 ++++-
 src/bin/pg_dump/pg_dump.h                     |   4 +-
 src/bin/pg_dump/t/002_pg_dump.pl              |  32 ++++
 src/bin/psql/describe.c                       |  20 ++-
 src/include/access/tupdesc.h                  |   2 +-
 src/include/catalog/heap.h                    |   2 +-
 src/include/catalog/pg_constraint.h           |   3 +-
 src/test/regress/expected/constraints.out     | 165 ++++++++++++++++++
 .../regress/expected/create_table_like.out    |  22 +++
 src/test/regress/expected/inherit.out         | 126 +++++++++++++
 src/test/regress/sql/constraints.sql          |  90 ++++++++++
 src/test/regress/sql/create_table_like.sql    |  12 ++
 src/test/regress/sql/inherit.sql              |  70 ++++++++
 23 files changed, 831 insertions(+), 108 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e7067c84ece..0cba925e68b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1263,7 +1263,7 @@
        <structfield>attnotnull</structfield> <type>bool</type>
       </para>
       <para>
-       This column has a (possibly invalid) not-null constraint.
+       This column has an enforced (possibly invalid) not-null constraint.
       </para></entry>
      </row>
 
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index aab2c6eb19f..3e9bc4fb53a 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1688,8 +1688,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal>
     constraint requires scanning the table to verify that existing rows meet the
     constraint, but does not require a table rewrite.  If a <literal>CHECK</literal>
-    constraint is added as <literal>NOT ENFORCED</literal>, no verification will
-    be performed.
+    or <literal>NOT NULL</literal> constraint is added as <literal>NOT ENFORCED</literal>,
+    no verification will be performed.
    </para>
 
    <para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 982532fe725..947b107e216 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1426,8 +1426,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      </para>
 
      <para>
-      This is currently only supported for foreign key and <literal>CHECK</literal>
-      constraints.
+      This is currently only supported for foreign key, <literal>CHECK</literal>
+      and <literal>NOT NULL</literal> constraints.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 5748aa9a1a9..4d54c300034 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2253,7 +2253,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
 static Oid
 StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
 				bool is_validated, bool is_local, int inhcount,
-				bool is_no_inherit)
+				bool is_no_inherit, bool is_enforced)
 {
 	Oid			constrOid;
 
@@ -2265,7 +2265,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
 							  CONSTRAINT_NOTNULL,
 							  false,
 							  false,
-							  true, /* Is Enforced */
+							  is_enforced,	/* Is Enforced */
 							  is_validated,
 							  InvalidOid,
 							  RelationGetRelid(rel),
@@ -2627,17 +2627,19 @@ AddRelationNewConstraints(Relation rel,
 							   strVal(linitial(cdef->keys))));
 
 			Assert(cdef->initially_valid != cdef->skip_validation);
+			Assert(cdef->is_enforced || !cdef->initially_valid);
 
 			/*
 			 * If the column already has a not-null constraint, we don't want
 			 * to add another one; adjust inheritance status as needed.  This
 			 * also checks whether the existing constraint matches the
-			 * requested validity.
+			 * requested validity, enforceability.
 			 */
 			if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum,
 										 cdef->conname,
 										 is_local, cdef->is_no_inherit,
-										 cdef->skip_validation))
+										 cdef->skip_validation,
+										 cdef->is_enforced))
 				continue;
 
 			/*
@@ -2668,7 +2670,8 @@ AddRelationNewConstraints(Relation rel,
 								cdef->initially_valid,
 								is_local,
 								inhcount,
-								cdef->is_no_inherit);
+								cdef->is_no_inherit,
+								cdef->is_enforced);
 
 			nncooked = palloc_object(CookedConstraint);
 			nncooked->contype = CONSTR_NOTNULL;
@@ -2676,7 +2679,7 @@ AddRelationNewConstraints(Relation rel,
 			nncooked->name = nnname;
 			nncooked->attnum = colnum;
 			nncooked->expr = NULL;
-			nncooked->is_enforced = true;
+			nncooked->is_enforced = cdef->is_enforced;
 			nncooked->skip_validation = cdef->skip_validation;
 			nncooked->is_local = is_local;
 			nncooked->inhcount = inhcount;
@@ -2952,7 +2955,7 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 		/*
 		 * A column can only have one not-null constraint, so discard any
 		 * additional ones that appear for columns we already saw; but check
-		 * that the NO INHERIT flags match.
+		 * that the NO INHERIT, [NOT] ENFORCED flags match.
 		 */
 		for (int restpos = outerpos + 1; restpos < list_length(constraints);)
 		{
@@ -2968,6 +2971,12 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 							errmsg("conflicting NO INHERIT declaration for not-null constraint on column \"%s\"",
 								   strVal(linitial(constr->keys))));
 
+				if (other->is_enforced != constr->is_enforced)
+					ereport(ERROR,
+							errcode(ERRCODE_SYNTAX_ERROR),
+							errmsg("conflicting NOT ENFORCED declaration for not-null constraint on column \"%s\"",
+								   strVal(linitial(constr->keys))));
+
 				/*
 				 * Preserve constraint name if one is specified, but raise an
 				 * error if conflicting ones are specified.
@@ -3013,6 +3022,17 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 									strVal(linitial(constr->keys))),
 							 errdetail("The column has an inherited not-null constraint.")));
 
+				/*
+				 * If we get an ENFORCED constraint from the parent, having a
+				 * local NOT ENFORCED one doesn't work.
+				 */
+				if (old->is_enforced && !constr->is_enforced)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot define not-null constraint with NOT ENFORCED on column \"%s\"",
+								   strVal(linitial(constr->keys))),
+							errdetail("The column has an inherited ENFORCED not-null constraint."));
+
 				inhcount++;
 				old_notnulls = foreach_delete_current(old_notnulls, old);
 			}
@@ -3047,11 +3067,15 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 										   nnnames);
 		nnnames = lappend(nnnames, conname);
 
-		StoreRelNotNull(rel, conname,
-						attnum, true, true,
-						inhcount, constr->is_no_inherit);
+		Assert(constr->is_enforced || constr->skip_validation);
 
-		nncols = lappend_int(nncols, attnum);
+		StoreRelNotNull(rel, conname, attnum,
+						!constr->skip_validation,
+						true, inhcount, constr->is_no_inherit,
+						constr->is_enforced);
+
+		if (constr->is_enforced)
+			nncols = lappend_int(nncols, attnum);
 	}
 
 	/*
@@ -3096,6 +3120,18 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 					conname = other->name;
 
 				inhcount++;
+
+				/*
+				 * A column may inherit multiple NOT NULL constraints. If one
+				 * is marked NOT ENFORCED while another is ENFORCED, we
+				 * install the enforced constraint.
+				 */
+				if (other->is_enforced != cooked->is_enforced)
+				{
+					cooked->is_enforced = true;
+					cooked->skip_validation = false;
+				}
+
 				old_notnulls = list_delete_nth_cell(old_notnulls, restpos);
 			}
 			else
@@ -3126,10 +3162,13 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 		nnnames = lappend(nnnames, conname);
 
 		/* ignore the origin constraint's is_local and inhcount */
-		StoreRelNotNull(rel, conname, cooked->attnum, true,
-						false, inhcount, false);
+		StoreRelNotNull(rel, conname, cooked->attnum,
+						cooked->is_enforced ? true : false,
+						false, inhcount, false,
+						cooked->is_enforced);
 
-		nncols = lappend_int(nncols, cooked->attnum);
+		if (cooked->is_enforced)
+			nncols = lappend_int(nncols, cooked->attnum);
 	}
 
 	return nncols;
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index b12765ae691..9fd2a5fe2fc 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -100,9 +100,10 @@ CreateConstraintEntry(const char *constraintName,
 	ObjectAddresses *addrs_auto;
 	ObjectAddresses *addrs_normal;
 
-	/* Only CHECK or FOREIGN KEY constraint can be not enforced */
-	Assert(isEnforced || constraintType == CONSTRAINT_CHECK ||
-		   constraintType == CONSTRAINT_FOREIGN);
+	/* CHECK, FOREIGN KEY, NOT NULL constraint can be not enforced */
+	Assert(isEnforced || (constraintType == CONSTRAINT_CHECK ||
+						  constraintType == CONSTRAINT_FOREIGN ||
+						  constraintType == CONSTRAINT_NOTNULL));
 	/* NOT ENFORCED constraint must be NOT VALID */
 	Assert(isEnforced || !isValidated);
 
@@ -580,8 +581,8 @@ ChooseConstraintName(const char *name1, const char *name2,
 }
 
 /*
- * Find and return a copy of the pg_constraint tuple that implements a
- * (possibly not valid) not-null constraint for the given column of the
+ * Find and return a copy of the pg_constraint tuple that implements a (possibly
+ * not valid or not enforced) not-null constraint for the given column of the
  * given relation.  If no such constraint exists, return NULL.
  *
  * XXX This would be easier if we had pg_attribute.notnullconstr with the OID
@@ -634,8 +635,8 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
 
 /*
  * Find and return a copy of the pg_constraint tuple that implements a
- * (possibly not valid) not-null constraint for the given column of the
- * given relation.
+ * (possibly not valid or not enforced) not-null constraint for the given column
+ * of the given relation.
  * If no such column or no such constraint exists, return NULL.
  */
 HeapTuple
@@ -728,8 +729,8 @@ extractNotNullColumn(HeapTuple constrTup)
  * If no not-null constraint is found for the column, return false.
  * Caller can create one.
  *
- * If a constraint exists but the connoinherit flag is not what the caller
- * wants, throw an error about the incompatibility.  If the desired
+ * If a constraint exists but the connoinherit, conenforced flag is not what the
+ * caller wants, throw an error about the incompatibility.  If the desired
  * constraint is valid but the existing constraint is not valid, also
  * throw an error about that (the opposite case is acceptable).  If
  * the proposed constraint has a different name, also throw an error.
@@ -740,7 +741,8 @@ extractNotNullColumn(HeapTuple constrTup)
  */
 bool
 AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
-						 bool is_local, bool is_no_inherit, bool is_notvalid)
+						 bool is_local, bool is_no_inherit, bool is_notvalid,
+						 bool is_enforced)
 {
 	HeapTuple	tup;
 
@@ -770,7 +772,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
 		 * Throw an error if the existing constraint is NOT VALID and caller
 		 * wants a valid one.
 		 */
-		if (!is_notvalid && !conform->convalidated)
+		if (!is_notvalid && !conform->convalidated && conform->conenforced)
 			ereport(ERROR,
 					errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
 					errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"",
@@ -794,6 +796,26 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
 					errdetail("A not-null constraint named \"%s\" already exists for this column.",
 							  NameStr(conform->conname)));
 
+		/*
+		 * If the ENFORCED status we're asked for doesn't match what the
+		 * existing constraint has, then throw an error.
+		 */
+		if (is_enforced != conform->conenforced)
+		{
+			if (is_enforced)
+				ereport(ERROR,
+						errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+						errmsg("cannot change not enforced NOT NULL constraint \"%s\" on relation \"%s\" to enforced",
+							   NameStr(conform->conname), get_rel_name(relid)),
+						errhint("You might need to ensure the existing constraint is enforced."));
+			else
+				ereport(ERROR,
+						errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+						errmsg("cannot change enforced NOT NULL constraint \"%s\" on relation \"%s\" to not enforced",
+							   NameStr(conform->conname), get_rel_name(relid)),
+						errhint("You might need to ensure the existing constraint is not enforced."));
+		}
+
 		if (!is_local)
 		{
 			if (pg_add_s16_overflow(conform->coninhcount, 1,
@@ -824,6 +846,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
  * RelationGetNotNullConstraints
  *		Return the list of not-null constraints for the given rel
  *
+ * The returned not-null constraints possibly not enforced!
  * Caller can request cooked constraints, or raw.
  *
  * This is seldom needed, so we just scan pg_constraint each time.
@@ -870,7 +893,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
 			cooked->name = pstrdup(NameStr(conForm->conname));
 			cooked->attnum = colnum;
 			cooked->expr = NULL;
-			cooked->is_enforced = true;
+			cooked->is_enforced = conForm->conenforced;
 			cooked->skip_validation = !conForm->convalidated;
 			cooked->is_local = true;
 			cooked->inhcount = 0;
@@ -890,7 +913,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
 			constr->location = -1;
 			constr->keys = list_make1(makeString(get_attname(relid, colnum,
 															 false)));
-			constr->is_enforced = true;
+			constr->is_enforced = conForm->conenforced;
 			constr->skip_validation = !conForm->convalidated;
 			constr->initially_valid = conForm->convalidated;
 			constr->is_no_inherit = conForm->connoinherit;
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 3a8ad201607..73620edf04e 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -281,7 +281,7 @@ F461	Named character sets			NO
 F471	Scalar subquery values			YES	
 F481	Expanded NULL predicate			YES	
 F491	Constraint management			YES	
-F492	Optional table constraint enforcement			YES	except not-null constraints
+F492	Optional table constraint enforcement			YES	
 F501	Features and conformance views			YES	
 F501	Features and conformance views	01	SQL_FEATURES view	YES	
 F501	Features and conformance views	02	SQL_SIZING view	YES	
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b04b0dbd2a0..8d03fce97fa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -506,7 +506,8 @@ static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
 static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
 									  char *conName, char *colName,
 									  bool recurse, bool recursing,
-									  LOCKMODE lockmode);
+									  LOCKMODE lockmode,
+									  bool is_enforced);
 static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
 static bool ConstraintImpliedByRelConstraint(Relation scanrel,
 											 List *testConstraint, List *provenConstraint);
@@ -2759,13 +2760,16 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
 		inherited_defaults = cols_with_defaults = NIL;
 
 		/*
-		 * Request attnotnull on columns that have a not-null constraint
-		 * that's not marked NO INHERIT (even if not valid).
+		 * Request attnotnull on columns that have an enforced not-null
+		 * constraint that's not marked NO INHERIT (even if not valid).
 		 */
 		nnconstrs = RelationGetNotNullConstraints(RelationGetRelid(relation),
 												  true, false);
 		foreach_ptr(CookedConstraint, cc, nnconstrs)
-			nncols = bms_add_member(nncols, cc->attnum);
+		{
+			if (cc->is_enforced)
+				nncols = bms_add_member(nncols, cc->attnum);
+		}
 
 		for (AttrNumber parent_attno = 1; parent_attno <= tupleDesc->natts;
 			 parent_attno++)
@@ -5433,7 +5437,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 			break;
 		case AT_SetNotNull:		/* ALTER COLUMN SET NOT NULL */
 			address = ATExecSetNotNull(wqueue, rel, NULL, cmd->name,
-									   cmd->recurse, false, lockmode);
+									   cmd->recurse, false, lockmode, true);
 			break;
 		case AT_SetExpression:
 			address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode);
@@ -7776,6 +7780,8 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
  *
  * Return the address of the modified column.  If the column was already
  * nullable, InvalidObjectAddress is returned.
+ *
+ * This will drop the not enforced not-null constraint too.
  */
 static ObjectAddress
 ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
@@ -7804,13 +7810,6 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
 	ObjectAddressSubSet(address, RelationRelationId,
 						RelationGetRelid(rel), attnum);
 
-	/* If the column is already nullable there's nothing to do. */
-	if (!attTup->attnotnull)
-	{
-		table_close(attr_rel, RowExclusiveLock);
-		return InvalidObjectAddress;
-	}
-
 	/* Prevent them from altering a system attribute */
 	if (attnum <= 0)
 		ereport(ERROR,
@@ -7849,8 +7848,17 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
 	 */
 	conTup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
 	if (conTup == NULL)
-		elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
-			 colName, RelationGetRelationName(rel));
+	{
+		if (attTup->attnotnull)
+			elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
+				 colName, RelationGetRelationName(rel));
+		else
+		{
+			/* Skip if no NOT NULL constraint exists (including NOT ENFORCED). */
+			table_close(attr_rel, RowExclusiveLock);
+			return InvalidObjectAddress;
+		}
+	}
 
 	/* The normal case: we have a pg_constraint row, remove it */
 	dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
@@ -7947,10 +7955,14 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
  *
  * We must recurse to child tables during execution, rather than using
  * ALTER TABLE's normal prep-time recursion.
+ *
+ * When the is_enforced flag is false, the newly added NOT NULL constraint will
+ * be not enforced.  This supports potential future syntax such as
+ * ALTER TABLE ALTER COLUMN SET NOT NULL NOT ENFORCED.
  */
 static ObjectAddress
 ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
-				 bool recurse, bool recursing, LOCKMODE lockmode)
+				 bool recurse, bool recursing, LOCKMODE lockmode, bool is_enforced)
 {
 	HeapTuple	tuple;
 	AttrNumber	attnum;
@@ -7985,7 +7997,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 				 errmsg("cannot alter system column \"%s\"",
 						colName)));
 
-	/* See if there's already a constraint */
+	/* See if there's already a constraint. It maybe not enforced! */
 	tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
 	if (HeapTupleIsValid(tuple))
 	{
@@ -8002,6 +8014,13 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 						   NameStr(conForm->conname),
 						   RelationGetRelationName(rel)));
 
+		if (is_enforced && !conForm->conenforced)
+			ereport(ERROR,
+					errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					errmsg("cannot validate NOT ENFORCED constraint \"%s\" on relation \"%s\"",
+						   NameStr(conForm->conname),
+						   RelationGetRelationName(rel)));
+
 		/*
 		 * If we find an appropriate constraint, we're almost done, but just
 		 * need to change some properties on it: if we're recursing, increment
@@ -8021,7 +8040,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 			conForm->conislocal = true;
 			changed = true;
 		}
-		else if (!conForm->convalidated)
+		else if (is_enforced && !conForm->convalidated && conForm->conenforced)
 		{
 			/*
 			 * Flip attnotnull and convalidated, and also validate the
@@ -8082,6 +8101,9 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 	constraint = makeNotNullConstraint(makeString(colName));
 	constraint->is_no_inherit = is_no_inherit;
 	constraint->conname = conName;
+	constraint->is_enforced = is_enforced;
+	constraint->initially_valid = is_enforced;
+	constraint->skip_validation = !is_enforced;
 
 	/* and do it */
 	cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint),
@@ -8093,7 +8115,8 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 							  RelationGetRelid(rel), attnum);
 
 	/* Mark pg_attribute.attnotnull for the column and queue validation */
-	set_attnotnull(wqueue, rel, attnum, true, true);
+	if (ccon->is_enforced)
+		set_attnotnull(wqueue, rel, attnum, true, true);
 
 	/*
 	 * Recurse to propagate the constraint to children that don't have one.
@@ -8112,7 +8135,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 			CommandCounterIncrement();
 
 			ATExecSetNotNull(wqueue, childrel, conName, colName,
-							 recurse, true, lockmode);
+							 recurse, true, lockmode, is_enforced);
 			table_close(childrel, NoLock);
 		}
 	}
@@ -9621,7 +9644,7 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
 						"ALTER TABLE ... ALTER CONSTRAINT ... INHERIT"));
 
 	/* an unvalidated constraint is no good */
-	if (!conForm->convalidated)
+	if (!conForm->convalidated && conForm->conenforced)
 		ereport(ERROR,
 				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
 				errmsg("cannot create primary key on column \"%s\"", colname),
@@ -9631,6 +9654,17 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
 						  get_rel_name(conForm->conrelid), "NOT VALID"),
 				errhint("You might need to validate it using %s.",
 						"ALTER TABLE ... VALIDATE CONSTRAINT"));
+
+	/* a not enforced constraint is no good */
+	if (!conForm->conenforced)
+		ereport(ERROR,
+				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				errmsg("cannot create primary key on column \"%s\"", colname),
+		/*- translator: fourth %s is a constraint characteristic such as NOT ENFORCED */
+				errdetail("The constraint \"%s\" on column \"%s\" of table \"%s\", marked %s, is incompatible with a primary key.",
+						  NameStr(conForm->conname), colname,
+						  get_rel_name(conForm->conrelid), "NOT ENFORCED"),
+				errhint("You might need to ensure the existing constraint is enforced."));
 }
 
 /*
@@ -10000,9 +10034,9 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		 * If adding a valid not-null constraint, set the pg_attribute flag
 		 * and tell phase 3 to verify existing rows, if needed.  For an
 		 * invalid constraint, just set attnotnull, without queueing
-		 * verification.
+		 * verification. No need to set attnotnull for not enforced not-null.
 		 */
-		if (constr->contype == CONSTR_NOTNULL)
+		if (constr->contype == CONSTR_NOTNULL && ccon->is_enforced)
 			set_attnotnull(wqueue, rel, ccon->attnum,
 						   !constr->skip_validation,
 						   !constr->skip_validation);
@@ -12700,7 +12734,9 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon,
 			Relation	childrel = table_open(childoid, NoLock);
 
 			addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
-									colName, true, true, lockmode);
+									colName, true, true,
+									lockmode,
+									currcon->conenforced);
 			if (OidIsValid(addr.objectId))
 				CommandCounterIncrement();
 			table_close(childrel, NoLock);
@@ -17568,9 +17604,31 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
 			if (parent_att->attnotnull && !child_att->attnotnull)
 			{
 				HeapTuple	contup;
+				HeapTuple	childcontup;
 
+				childcontup = findNotNullConstraintAttnum(RelationGetRelid(child_rel),
+														  child_att->attnum);
 				contup = findNotNullConstraintAttnum(RelationGetRelid(parent_rel),
 													 parent_att->attnum);
+
+				if (HeapTupleIsValid(childcontup) && HeapTupleIsValid(contup))
+				{
+					Form_pg_constraint child_con = (Form_pg_constraint) GETSTRUCT(childcontup);
+					Form_pg_constraint parent_con = (Form_pg_constraint) GETSTRUCT(contup);
+
+					Assert(parent_con->conenforced);
+					Assert(!child_con->conenforced);
+
+					/*
+					 * An unenforced NOT NULL constraint on the child cannot
+					 * be merged with an enforced constraint on the parent.
+					 */
+					ereport(ERROR,
+							errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+							errmsg("constraint \"%s\" conflicts with NOT ENFORCED constraint on child table \"%s\"",
+								   NameStr(child_con->conname), RelationGetRelationName(child_rel)));
+				}
+
 				if (HeapTupleIsValid(contup) &&
 					!((Form_pg_constraint) GETSTRUCT(contup))->connoinherit)
 					ereport(ERROR,
@@ -17822,13 +17880,24 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
 		if (!found)
 		{
 			if (parent_con->contype == CONSTRAINT_NOTNULL)
-				ereport(ERROR,
-						errcode(ERRCODE_DATATYPE_MISMATCH),
-						errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL",
-							   get_attname(parent_relid,
-										   extractNotNullColumn(parent_tuple),
-										   false),
-							   RelationGetRelationName(child_rel)));
+			{
+				if (parent_con->conenforced)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL",
+								   get_attname(parent_relid,
+											   extractNotNullColumn(parent_tuple),
+											   false),
+								   RelationGetRelationName(child_rel)));
+				else
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL NOT ENFORCED",
+								   get_attname(parent_relid,
+											   extractNotNullColumn(parent_tuple),
+											   false),
+								   RelationGetRelationName(child_rel)));
+			}
 
 			ereport(ERROR,
 					(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c567252acc4..cda99ca22c2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4339,7 +4339,7 @@ ConstraintElem:
 					n->location = @1;
 					n->keys = list_make1(makeString($3));
 					processCASbits($4, @4, "NOT NULL",
-								   NULL, NULL, NULL, &n->skip_validation,
+								   NULL, NULL, &n->is_enforced, &n->skip_validation,
 								   &n->is_no_inherit, yyscanner);
 					n->initially_valid = !n->skip_validation;
 					$$ = (Node *) n;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index cc244c49e9e..a1bef78e875 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -320,6 +320,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	{
 		char	   *colname = strVal(linitial(nn->keys));
 
+		/* Don't set is_not_null to true for not enforced not-null */
+		if (!nn->is_enforced)
+			continue;
+
 		foreach_node(ColumnDef, cd, cxt.columns)
 		{
 			/* not our column? */
@@ -600,6 +604,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 	bool		saw_generated;
 	bool		need_notnull = false;
 	bool		disallow_noinherit_notnull = false;
+	bool		disallow_notenforced_notnull = false;
 	Constraint *notnull_constraint = NULL;
 
 	cxt->columns = lappend(cxt->columns, column);
@@ -700,6 +705,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		/* have a not-null constraint added later */
 		need_notnull = true;
 		disallow_noinherit_notnull = true;
+		disallow_notenforced_notnull = true;
 	}
 
 	/* Process column constraints, if any... */
@@ -716,8 +722,12 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 	 * disallow it here as well.  Maybe AddRelationNotNullConstraints can be
 	 * improved someday, so that it doesn't complain, and then we can remove
 	 * the restriction for SERIAL and IDENTITY here as well.
+	 *
+	 * Also check if the added NOT NULL constraint is prohibited from being
+	 * NOT ENFORCED. This restriction applies to PRIMARY KEY, IDENTITY, and
+	 * SERIAL columns.
 	 */
-	if (!disallow_noinherit_notnull)
+	if (!disallow_noinherit_notnull || !disallow_notenforced_notnull)
 	{
 		foreach_node(Constraint, constraint, column->constraints)
 		{
@@ -726,6 +736,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 				case CONSTR_IDENTITY:
 				case CONSTR_PRIMARY:
 					disallow_noinherit_notnull = true;
+					disallow_notenforced_notnull = true;
 					break;
 				default:
 					break;
@@ -776,6 +787,12 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 							errmsg("conflicting NO INHERIT declarations for not-null constraints on column \"%s\"",
 								   column->colname));
 
+				if (disallow_notenforced_notnull && !constraint->is_enforced)
+					ereport(ERROR,
+							errcode(ERRCODE_SYNTAX_ERROR),
+							errmsg("conflicting NOT ENFORCED declarations for not-null constraints on column \"%s\"",
+								   column->colname));
+
 				/*
 				 * If this is the first time we see this column being marked
 				 * not-null, add the constraint entry and keep track of it.
@@ -795,6 +812,15 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 
 					constraint->keys = list_make1(makeString(column->colname));
 					notnull_constraint = constraint;
+
+					/*
+					 * NOT ENFORCED not-null does not indicate data are all
+					 * not-null, therefore cannot set the column's
+					 * pg_attribute.attnotnull to true.
+					 */
+					if (!constraint->is_enforced)
+						column->is_not_null = false;
+
 					cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
 				}
 				else if (notnull_constraint)
@@ -1134,6 +1160,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 	AclResult	aclresult;
 	char	   *comment;
 	ParseCallbackState pcbstate;
+	List	   *lst = NIL;
 
 	setup_parser_errposition_callback(&pcbstate, cxt->pstate,
 									  table_like_clause->relation->location);
@@ -1275,33 +1302,41 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 	 * Reproduce not-null constraints, if any, by copying them.  We do this
 	 * regardless of options given.
 	 */
-	if (tupleDesc->constr && tupleDesc->constr->has_not_null)
-	{
-		List	   *lst;
+	lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
+										true);
+	cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
 
-		lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
-											true);
-		cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
+	/*
+	 * When creating a new relation, marking the not-null constraint as not
+	 * valid doesn't make sense, so we treat it as valid unconditionally.
+	 */
+	foreach_node(Constraint, nnconstr, lst)
+	{
+		if (nnconstr->is_enforced)
+		{
+			nnconstr->skip_validation = false;
+			nnconstr->initially_valid = true;
+		}
+	}
 
-		/* Copy comments on not-null constraints */
-		if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
+	/* Copy comments on not-null constraints */
+	if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
+	{
+		foreach_node(Constraint, nnconstr, lst)
 		{
-			foreach_node(Constraint, nnconstr, lst)
+			if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation),
+																  nnconstr->conname, false),
+									  ConstraintRelationId,
+									  0)) != NULL)
 			{
-				if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation),
-																	  nnconstr->conname, false),
-										  ConstraintRelationId,
-										  0)) != NULL)
-				{
-					CommentStmt *stmt = makeNode(CommentStmt);
+				CommentStmt *stmt = makeNode(CommentStmt);
 
-					stmt->objtype = OBJECT_TABCONSTRAINT;
-					stmt->object = (Node *) list_make3(makeString(cxt->relation->schemaname),
-													   makeString(cxt->relation->relname),
-													   makeString(nnconstr->conname));
-					stmt->comment = comment;
-					cxt->alist = lappend(cxt->alist, stmt);
-				}
+				stmt->objtype = OBJECT_TABCONSTRAINT;
+				stmt->object = (Node *) list_make3(makeString(cxt->relation->schemaname),
+												   makeString(cxt->relation->relname),
+												   makeString(nnconstr->conname));
+				stmt->comment = comment;
+				cxt->alist = lappend(cxt->alist, stmt);
 			}
 		}
 	}
@@ -4310,7 +4345,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
 			case CONSTR_ATTR_ENFORCED:
 				if (lastprimarycon == NULL ||
 					(lastprimarycon->contype != CONSTR_CHECK &&
-					 lastprimarycon->contype != CONSTR_FOREIGN))
+					 lastprimarycon->contype != CONSTR_FOREIGN &&
+					 lastprimarycon->contype != CONSTR_NOTNULL))
 					ereport(ERROR,
 							(errcode(ERRCODE_SYNTAX_ERROR),
 							 errmsg("misplaced ENFORCED clause"),
@@ -4327,7 +4363,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
 			case CONSTR_ATTR_NOT_ENFORCED:
 				if (lastprimarycon == NULL ||
 					(lastprimarycon->contype != CONSTR_CHECK &&
-					 lastprimarycon->contype != CONSTR_FOREIGN))
+					 lastprimarycon->contype != CONSTR_FOREIGN &&
+					 lastprimarycon->contype != CONSTR_NOTNULL))
 					ereport(ERROR,
 							(errcode(ERRCODE_SYNTAX_ERROR),
 							 errmsg("misplaced NOT ENFORCED clause"),
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 6b634c9fff1..b9a72a892b3 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4618,8 +4618,8 @@ CheckNNConstraintFetch(Relation relation)
 		bool		isnull;
 
 		/*
-		 * If this is a not-null constraint, then only look at it if it's
-		 * invalid, and if so, mark the TupleDesc entry as known invalid.
+		 * If this is an enforced not-null constraint, then only look at it if
+		 * it's invalid, and if so, mark the TupleDesc entry as known invalid.
 		 * Otherwise move on.  We'll mark any remaining columns that are still
 		 * in UNKNOWN state as known valid later.  This allows us not to have
 		 * to extract the attnum from this constraint tuple in the vast
@@ -4627,7 +4627,7 @@ CheckNNConstraintFetch(Relation relation)
 		 */
 		if (conform->contype == CONSTRAINT_NOTNULL)
 		{
-			if (!conform->convalidated)
+			if (!conform->convalidated && conform->conenforced)
 			{
 				AttrNumber	attnum;
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6df79067db5..f3262da75d5 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -361,6 +361,7 @@ static void determineNotNullFlags(Archive *fout, PGresult *res, int r,
 								  int i_notnull_invalidoid,
 								  int i_notnull_noinherit,
 								  int i_notnull_islocal,
+								  int i_notnull_enforced,
 								  PQExpBuffer *invalidnotnulloids);
 static char *format_function_arguments(const FuncInfo *finfo, const char *funcargs,
 									   bool is_agg);
@@ -9264,6 +9265,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	int			i_notnull_comment;
 	int			i_notnull_noinherit;
 	int			i_notnull_islocal;
+	int			i_notnull_enforced;
 	int			i_notnull_invalidoid;
 	int			i_attoptions;
 	int			i_attcollation;
@@ -9355,12 +9357,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 
 	/*
 	 * Find out any NOT NULL markings for each column.  In 18 and up we read
-	 * pg_constraint to obtain the constraint name, and for valid constraints
-	 * also pg_description to obtain its comment.  notnull_noinherit is set
-	 * according to the NO INHERIT property.  For versions prior to 18, we
-	 * store an empty string as the name when a constraint is marked as
-	 * attnotnull (this cues dumpTableSchema to print the NOT NULL clause
-	 * without a name); also, such cases are never NO INHERIT.
+	 * pg_constraint to obtain the constraint name, and for valid and not
+	 * enforced constraints also pg_description to obtain its comment.
+	 * notnull_noinherit is set according to the NO INHERIT property.  For
+	 * versions prior to 18, we store an empty string as the name when a
+	 * constraint is marked as attnotnull (this cues dumpTableSchema to print
+	 * the NOT NULL clause without a name); also, such cases are never NO
+	 * INHERIT.
 	 *
 	 * For invalid constraints, we need to store their OIDs for processing
 	 * elsewhere, so we bring the pg_constraint.oid value when the constraint
@@ -9374,9 +9377,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	if (fout->remoteVersion >= 180000)
 		appendPQExpBufferStr(q,
 							 "co.conname AS notnull_name,\n"
-							 "CASE WHEN co.convalidated THEN pt.description"
+							 "CASE WHEN (NOT co.conenforced OR co.convalidated) THEN pt.description"
 							 " ELSE NULL END AS notnull_comment,\n"
-							 "CASE WHEN NOT co.convalidated THEN co.oid "
+							 "CASE WHEN (NOT co.convalidated AND co.conenforced) THEN co.oid "
 							 "ELSE NULL END AS notnull_invalidoid,\n"
 							 "co.connoinherit AS notnull_noinherit,\n"
 							 "co.conislocal AS notnull_islocal,\n");
@@ -9391,6 +9394,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 							 "     ELSE false\n"
 							 "END AS notnull_islocal,\n");
 
+	if (fout->remoteVersion >= 190000)
+		appendPQExpBufferStr(q, "co.conenforced AS notnull_enforced,\n");
+	else
+		appendPQExpBufferStr(q, "true AS notnull_enforced,\n");
+
 	if (fout->remoteVersion >= 140000)
 		appendPQExpBufferStr(q,
 							 "a.attcompression AS attcompression,\n");
@@ -9478,6 +9486,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	i_notnull_invalidoid = PQfnumber(res, "notnull_invalidoid");
 	i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
 	i_notnull_islocal = PQfnumber(res, "notnull_islocal");
+	i_notnull_enforced = PQfnumber(res, "notnull_enforced");
 	i_attoptions = PQfnumber(res, "attoptions");
 	i_attcollation = PQfnumber(res, "attcollation");
 	i_attcompression = PQfnumber(res, "attcompression");
@@ -9550,6 +9559,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 		tbinfo->notnull_invalid = pg_malloc_array(bool, numatts);
 		tbinfo->notnull_noinh = pg_malloc_array(bool, numatts);
 		tbinfo->notnull_islocal = pg_malloc_array(bool, numatts);
+		tbinfo->notnull_enforced = pg_malloc_array(bool, numatts);
 		tbinfo->attrdefs = pg_malloc_array(AttrDefInfo *, numatts);
 		hasdefaults = false;
 
@@ -9584,6 +9594,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 								  i_notnull_invalidoid,
 								  i_notnull_noinherit,
 								  i_notnull_islocal,
+								  i_notnull_enforced,
 								  &invalidnotnulloids);
 
 			tbinfo->notnull_comment[j] = PQgetisnull(res, r, i_notnull_comment) ?
@@ -10032,10 +10043,16 @@ determineNotNullFlags(Archive *fout, PGresult *res, int r,
 					  int i_notnull_invalidoid,
 					  int i_notnull_noinherit,
 					  int i_notnull_islocal,
+					  int i_notnull_enforced,
 					  PQExpBuffer *invalidnotnulloids)
 {
 	DumpOptions *dopt = fout->dopt;
 
+	if (fout->remoteVersion >= 190000)
+		tbinfo->notnull_enforced[j] = PQgetvalue(res, r, i_notnull_enforced)[0] == 't';
+	else
+		tbinfo->notnull_enforced[j] = true;
+
 	/*
 	 * If this not-null constraint is not valid, list its OID in
 	 * invalidnotnulloids and do nothing further.  It'll be processed
@@ -17377,6 +17394,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 
 						if (tbinfo->notnull_noinh[j])
 							appendPQExpBufferStr(q, " NO INHERIT");
+
+						if (!tbinfo->notnull_enforced[j])
+							appendPQExpBufferStr(q, " NOT ENFORCED");
 					}
 
 					/* Add collation if not default for the type */
@@ -17424,6 +17444,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 
 					if (tbinfo->notnull_noinh[j])
 						appendPQExpBufferStr(q, " NO INHERIT");
+
+					if (!tbinfo->notnull_enforced[j])
+						appendPQExpBufferStr(q, " NOT ENFORCED");
 				}
 			}
 
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 6deceef23f3..79426b761a1 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -373,9 +373,11 @@ typedef struct _tableInfo
 									 * empty string, unnamed constraint
 									 * (pre-v17) */
 	char	  **notnull_comment;	/* comment thereof */
-	bool	   *notnull_invalid;	/* true for NOT NULL NOT VALID */
+	bool	   *notnull_invalid;	/* true for NOT NULL NOT VALID. Note It's
+									 * false for NOT NULL NOT ENFORCED ! */
 	bool	   *notnull_noinh;	/* NOT NULL is NO INHERIT */
 	bool	   *notnull_islocal;	/* true if NOT NULL has local definition */
+	bool	   *notnull_enforced;	/* true if NOT NULL NOT ENFORCED */
 	struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
 	struct _constraintInfo *checkexprs; /* CHECK constraints */
 	struct _relStatsInfo *stats;	/* only set for matviews */
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index f15bd06adcc..aa3c05fa41d 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -968,6 +968,38 @@ my %tests = (
 		},
 	},
 
+	'CONSTRAINT NOT NULL NOT ENFORCED' => {
+		create_sql => 'CREATE TABLE dump_test.test_table_nn0 (
+							col1 int, CONSTRAINT nn NOT NULL col1 NOT ENFORCED);
+						COMMENT ON CONSTRAINT nn ON dump_test.test_table_nn0 IS \'nn comment is not enfoced\';',
+		regexp => qr/^
+			\QCREATE TABLE dump_test.test_table_nn0 (\E\n
+			\s+\Qcol1 integer CONSTRAINT nn NOT NULL col1 NOT ENFORCED)\E$
+			/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			only_dump_measurement => 1,
+			binary_upgrade => 1,
+		},
+	},
+
+	# This constraint is valid therefore it goes in SECTION_PRE_DATA
+	'COMMENT ON CONSTRAINT ON test_table_chld2' => {
+		regexp => qr/^
+		\QCOMMENT ON CONSTRAINT nn ON dump_test.test_table_nn0 IS\E
+		/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			only_dump_measurement => 1,
+		},
+	},
+
 	'CONSTRAINT NOT NULL / NOT VALID' => {
 		create_sql => 'CREATE TABLE dump_test.test_table_nn (
 							col1 int);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ab13c90ed33..6174de7b3bd 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3142,7 +3142,14 @@ describeOneTableDetails(const char *schemaname,
 			printfPQExpBuffer(&buf,
 							  "SELECT c.conname, a.attname, c.connoinherit,\n"
 							  "  c.conislocal, c.coninhcount <> 0,\n"
-							  "  c.convalidated\n"
+							  "  c.convalidated,\n");
+
+			if (pset.sversion >= 190000)
+				appendPQExpBufferStr(&buf, "c.conenforced\n");
+			else
+				appendPQExpBufferStr(&buf, "true AS conenforced\n");
+
+			appendPQExpBuffer(&buf,
 							  "FROM pg_catalog.pg_constraint c JOIN\n"
 							  "  pg_catalog.pg_attribute a ON\n"
 							  "    (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n"
@@ -3166,15 +3173,20 @@ describeOneTableDetails(const char *schemaname,
 				bool		islocal = PQgetvalue(result, i, 3)[0] == 't';
 				bool		inherited = PQgetvalue(result, i, 4)[0] == 't';
 				bool		validated = PQgetvalue(result, i, 5)[0] == 't';
+				bool		enforced = PQgetvalue(result, i, 6)[0] == 't';
 
-				printfPQExpBuffer(&buf, "    \"%s\" NOT NULL \"%s\"%s%s",
+				printfPQExpBuffer(&buf, "    \"%s\" NOT NULL \"%s\"%s",
 								  PQgetvalue(result, i, 0),
 								  PQgetvalue(result, i, 1),
 								  PQgetvalue(result, i, 2)[0] == 't' ?
 								  " NO INHERIT" :
 								  islocal && inherited ? _(" (local, inherited)") :
-								  inherited ? _(" (inherited)") : "",
-								  !validated ? " NOT VALID" : "");
+								  inherited ? _(" (inherited)") : "");
+
+				if (!enforced)
+					appendPQExpBufferStr(&buf, " NOT ENFORCED");
+				else if (!validated)
+					appendPQExpBufferStr(&buf, " NOT VALID");
 
 				printTableAddFooter(&cont, buf.data);
 			}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index d46cdbf7a3c..abc877e9856 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -42,7 +42,7 @@ typedef struct TupleConstr
 	struct AttrMissing *missing;	/* missing attributes values, NULL if none */
 	uint16		num_defval;
 	uint16		num_check;
-	bool		has_not_null;	/* any not-null, including not valid ones */
+	bool		has_not_null;	/* any enforced not-null, including not valid ones */
 	bool		has_generated_stored;
 	bool		has_generated_virtual;
 } TupleConstr;
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 6c9ac812aa0..344d4bc5390 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -40,7 +40,7 @@ typedef struct CookedConstraint
 	char	   *name;			/* name, or NULL if none */
 	AttrNumber	attnum;			/* which attr (only for NOTNULL, DEFAULT) */
 	Node	   *expr;			/* transformed default or check expr */
-	bool		is_enforced;	/* is enforced? (only for CHECK) */
+	bool		is_enforced;	/* is enforced? (for NOT NULL and CHECK) */
 	bool		skip_validation;	/* skip validation? (only for CHECK) */
 	bool		is_local;		/* constraint has local (non-inherited) def */
 	int16		inhcount;		/* number of times constraint is inherited */
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 1b7fedf1750..a60c0d5b59f 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -268,7 +268,8 @@ extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
 extern HeapTuple findDomainNotNullConstraint(Oid typid);
 extern AttrNumber extractNotNullColumn(HeapTuple constrTup);
 extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
-									 bool is_local, bool is_no_inherit, bool is_notvalid);
+									 bool is_local, bool is_no_inherit, bool is_notvalid,
+									 bool is_enforced);
 extern List *RelationGetNotNullConstraints(Oid relid, bool cooked,
 										   bool include_noinh);
 
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a6fa9cacb72..324b88411c1 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -969,33 +969,139 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
 -- error cases:
 create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null);
 ERROR:  conflicting not-null constraint names "foo" and "bar"
+create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced);
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "a"
 create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null);
 ERROR:  conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a serial constraint foo not null not enforced);
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "a"
 create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a);
 ERROR:  conflicting not-null constraint names "foo" and "bar"
+create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a);
 ERROR:  conflicting not-null constraint names "foo" and "bar"
+create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a serial, constraint foo not null a no inherit);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a serial, constraint foo not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a serial not null no inherit);
 ERROR:  conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a serial not null not enforced);
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "a"
 create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a);
 ERROR:  conflicting not-null constraint names "foo" and "foo2"
+create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a int primary key constraint foo not null no inherit);
 ERROR:  conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a int primary key constraint foo not null not enforced);
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "a"
 create table notnull_tbl_fail (a int not null no inherit primary key);
 ERROR:  conflicting NO INHERIT declarations for not-null constraints on column "a"
 create table notnull_tbl_fail (a int primary key, not null a no inherit);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int primary key, not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a int, primary key(a), not null a no inherit);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int, primary key(a), not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a not enforced);
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "a"
 create table notnull_tbl_fail (a int generated by default as identity not null no inherit);
 ERROR:  conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a int generated by default as identity not null not enforced);
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "a"
+alter table notnull_tbl1 add column b int not null not enforced; --ok
+alter table notnull_tbl1 alter column b add generated always as identity;
+ERROR:  column "b" of relation "notnull_tbl1" must be declared NOT NULL before identity can be added
+alter table notnull_tbl1 add column c int not null not enforced, alter column c add generated always as identity;
+ERROR:  column "c" of relation "notnull_tbl1" must be declared NOT NULL before identity can be added
+alter table notnull_tbl1 add column c int generated always as identity not null not enforced;
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "c"
+alter table notnull_tbl1 add column c serial not null not enforced;
+ERROR:  conflicting NOT ENFORCED declarations for not-null constraints on column "c"
+alter table notnull_tbl1 add column c serial, add constraint notnull_tbl1_c_not_null not null c not enforced;
+ERROR:  cannot change enforced NOT NULL constraint "notnull_tbl1_c_not_null" on relation "notnull_tbl1" to not enforced
+HINT:  You might need to ensure the existing constraint is not enforced.
 drop table notnull_tbl1;
+-- NOT NULL NOT ENFORCED
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); -- error
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "x"
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED);
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+  pg_get_constraintdef   
+-------------------------
+ NOT NULL x NOT ENFORCED
+(1 row)
+
+INSERT INTO ne_nn_tbl VALUES (NULL); -- ok
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; -- error
+ERROR:  cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl"
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; -- error
+ERROR:  cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl"
+ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; -- error
+ERROR:  cannot validate NOT ENFORCED constraint
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED;  -- error
+ERROR:  cannot create not-null constraint "nn_enforced1" on column "x" of table "ne_nn_tbl"
+DETAIL:  A not-null constraint named "nn" already exists for this column.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED;  -- no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID NOT ENFORCED; -- no-op, because NOT ENFORCED imply NOT VALID.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED NO INHERIT; -- error
+ERROR:  cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "ne_nn_tbl"
+HINT:  You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID; -- error, because NOT VALID imply ENFORCED
+ERROR:  cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x ENFORCED;  -- error
+ERROR:  cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x; -- error
+ERROR:  cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; -- error
+ERROR:  cannot validate NOT ENFORCED constraint "nn" on relation "ne_nn_tbl"
+\d+ ne_nn_tbl
+                                 Table "public.ne_nn_tbl"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ x      | integer |           |          |         | plain   |              | 
+Not-null constraints:
+    "nn" NOT NULL "x" NOT ENFORCED
+
+TRUNCATE ne_nn_tbl;
+-- error, cannot use not enforced not-null constaint for primary key
+ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x);
+ERROR:  cannot create primary key on column "x"
+DETAIL:  The constraint "nn" on column "x" of table "ne_nn_tbl", marked NOT ENFORCED, is incompatible with a primary key.
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; -- error
+ERROR:  multiple ENFORCED/NOT ENFORCED clauses not allowed
+LINE 1: ...E ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED;
+                                                              ^
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; -- ok
+ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; -- ok
+\d+ ne_nn_tbl
+                                 Table "public.ne_nn_tbl"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ x      | integer |           |          |         | plain   |              | 
+ x1     | integer |           |          |         | plain   |              | 
+ y      | integer |           | not null |         | plain   |              | 
+Not-null constraints:
+    "nn" NOT NULL "x" NO INHERIT NOT ENFORCED
+    "ne_nn_tbl_x1_not_null" NOT NULL "x1" NOT ENFORCED
+    "ne_nn_tbl_y_not_null" NOT NULL "y"
+
 -- NOT NULL NO INHERIT
 CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT);
 CREATE TABLE ATACC2 () INHERITS (ATACC1);
@@ -1680,6 +1786,65 @@ COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_parent2 IS 'this const
 COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_child2 IS 'this constraint is valid';
 DEALLOCATE get_nnconstraint_info;
 -- end NOT NULL NOT VALID
+-- Verify NOT NULL ENFORCED NOT ENFORCED
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT  conrelid::regclass as relname, conname, convalidated, conislocal, coninhcount, conenforced
+FROM    pg_constraint
+WHERE   conrelid = ANY($1)
+ORDER BY conrelid::regclass::text COLLATE "C", conname;
+-- partitioned table have enforced not-null, then partitions cannot have not enforced not-null
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int, CONSTRAINT nn1 NOT NULL a NOT ENFORCED);
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); -- error
+ERROR:  constraint "nn1" conflicts with NOT ENFORCED constraint on child table "pp_nn_1"
+DROP TABLE pp_nn, pp_nn_1;
+CREATE TABLE nn_enforced_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a);
+CREATE TABLE nn_enforced_tbl1_1 PARTITION OF nn_enforced_tbl1 FOR VALUES IN (1,2);
+-- if partitioned table not-null is not enforced, then partitions can have enforced
+-- or not enforced not-null
+CREATE TABLE nn_enforced_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int);
+ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_2 FOR VALUES IN (3,4); -- ok
+CREATE TABLE nn_enforced_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED);
+ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_3 FOR VALUES IN (NULL,5);
+CREATE TABLE nn_enforced_tbl1_4(a int, b int);
+ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_4 FOR VALUES IN (6); -- error
+ERROR:  column "a" in child table "nn_enforced_tbl1_4" must be marked NOT NULL NOT ENFORCED
+DROP TABLE nn_enforced_tbl1_4;
+EXECUTE get_nnconstraint_info('{nn_enforced_tbl1, nn_enforced_tbl1_1, nn_enforced_tbl1_2, nn_enforced_tbl1_3}');
+      relname       | conname | convalidated | conislocal | coninhcount | conenforced 
+--------------------+---------+--------------+------------+-------------+-------------
+ nn_enforced_tbl1   | nn0     | f            | t          |           0 | f
+ nn_enforced_tbl1_1 | nn0     | f            | f          |           1 | f
+ nn_enforced_tbl1_2 | nn1     | t            | f          |           1 | t
+ nn_enforced_tbl1_3 | nn2     | f            | f          |           1 | f
+(4 rows)
+
+ALTER TABLE nn_enforced_tbl1 ALTER COLUMN a SET NOT NULL; -- error, cannot validate not-enforced
+ERROR:  cannot validate NOT ENFORCED constraint "nn0" on relation "nn_enforced_tbl1"
+ALTER TABLE nn_enforced_tbl1 VALIDATE CONSTRAINT nn0;   -- error, cannot validate not-enforced
+ERROR:  cannot validate NOT ENFORCED constraint
+-- nn_enforced_tbl1 is used for pg_upgrade tests
+-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade.
+CREATE TABLE nn_notenforced (a int, b int, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+INSERT INTO nn_notenforced VALUES (NULL, 1);
+EXECUTE get_nnconstraint_info('{nn_notenforced}');
+    relname     | conname | convalidated | conislocal | coninhcount | conenforced 
+----------------+---------+--------------+------------+-------------+-------------
+ nn_notenforced | nn      | f            | t          |           0 | f
+(1 row)
+
+-- Inherit test for pg_upgrade
+CREATE TABLE notenforced_nn_parent (a int);
+CREATE TABLE notenforced_nn_child () INHERITS (notenforced_nn_parent);
+ALTER TABLE notenforced_nn_child ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}');
+       relname        | conname | convalidated | conislocal | coninhcount | conenforced 
+----------------------+---------+--------------+------------+-------------+-------------
+ notenforced_nn_child | nn      | f            | t          |           0 | f
+(1 row)
+
+DEALLOCATE get_nnconstraint_info;
+-- end NOT NULL NOT ENFORCED
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..013ff7984ea 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -317,6 +317,28 @@ Referenced by:
     TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
 
 DROP TABLE inhz;
+-- not null not enforced constraint
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test';
+CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS);
+\d+ not_enforced_nn_copy
+                          Table "public.not_enforced_nn_copy"
+ Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+------+-----------+----------+---------+----------+--------------+-------------
+ a      | text |           |          |         | extended |              | 
+Not-null constraints:
+    "nn" NOT NULL "a" NOT ENFORCED
+
+SELECT conname, description
+FROM  pg_description, pg_constraint c
+WHERE classoid = 'pg_constraint'::regclass
+AND   objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass
+ORDER BY conname COLLATE "C";
+ conname |                  description                  
+---------+-----------------------------------------------
+ nn      | not enforced not null constraint comment test
+(1 row)
+
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
 	b text CHECK (length(b) > 100) NOT ENFORCED);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..d822b2dcaf0 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1442,6 +1442,132 @@ alter table p1_c1 inherit p1;
 ERROR:  constraint "p1_a_check" conflicts with NOT ENFORCED constraint on child table "p1_c1"
 drop table p1, p1_c1;
 --
+-- Similarly, check the merging of existing constraints; a parent not-null constraint
+-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
+-- reverse is not allowed.
+--
+create table p1(f1 int constraint p1_a_nn not null);
+create table p1_c1(f1 int constraint p1_c1_nn not null not enforced);
+alter table p1_c1 inherit p1; -- error
+ERROR:  constraint "p1_c1_nn" conflicts with NOT ENFORCED constraint on child table "p1_c1"
+create table p1_c2(f1 int not null not enforced) inherits(p1); -- error
+NOTICE:  merging column "f1" with inherited definition
+ERROR:  cannot define not-null constraint with NOT ENFORCED on column "f1"
+DETAIL:  The column has an inherited ENFORCED not-null constraint.
+create table p1_c2(f1 int not null not enforced) inherits(p1_c1); -- ok
+NOTICE:  merging column "f1" with inherited definition
+select conenforced from pg_constraint where conrelid = 'p1_c2'::regclass and contype = 'n';
+ conenforced 
+-------------
+ f
+(1 row)
+
+drop table if exists p1, p1_c1, p1_c2;
+create table p1(f1 int);
+create table p1_c1() inherits(p1);
+alter table p1 add constraint p1_nn_1 not null f1 not enforced;
+alter table p1_c1 add constraint p1_nn_1 not null f1 enforced; -- error, column f1 already have not enforced
+ERROR:  cannot change not enforced NOT NULL constraint "p1_nn_1" on relation "p1_c1" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+-- not allowed: child is not enforced, parent is enforced
+alter table p1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_x not null f1 not enforced;
+alter table p1 add constraint nn not null f1 enforced; -- error
+ERROR:  cannot change not enforced NOT NULL constraint "nn_x" on relation "p1_c1" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+alter table p1_c1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_v not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; -- error
+ERROR:  cannot change enforced NOT NULL constraint "nn_v" on relation "p1_c1" to not enforced
+HINT:  You might need to ensure the existing constraint is not enforced.
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
+create table p1_nn(f1 int constraint p1_nn_a_nn not null not enforced);
+create table p1_nn_c3(f1 int);
+alter table p1_nn_c3 inherit p1_nn; -- error, because p1_nn_c3 does not have not-null constraint
+ERROR:  column "f1" in child table "p1_nn_c3" must be marked NOT NULL NOT ENFORCED
+drop table p1_nn_c3;
+create table p1_nn_c1(f1 int constraint p1_nn_c1_a_nn not null);
+alter table p1_nn_c1 inherit p1_nn; -- it's ok for parent not-null is not enforced while child is enforced
+create table p1_nn_c2() inherits(p1_nn, p1_nn_c1); -- merged multiple not-null constraints produce an enforced one
+NOTICE:  merging multiple inherited definitions of column "f1"
+\d+ p1_nn_c2
+                                 Table "public.p1_nn_c2"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ f1     | integer |           | not null |         | plain   |              | 
+Not-null constraints:
+    "p1_nn_a_nn" NOT NULL "f1" (inherited)
+Inherits: p1_nn,
+          p1_nn_c1
+
+create table p1_nn_c4(f1 int not null not enforced) inherits(p1_nn, p1_nn_c1); -- error, parent (p1_nn_c1) have enforced
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging column "f1" with inherited definition
+ERROR:  cannot define not-null constraint with NOT ENFORCED on column "f1"
+DETAIL:  The column has an inherited ENFORCED not-null constraint.
+-- merged multiple not-null constraints produce an enforced one, below two will be success.
+create table p1_nn_c4(f1 int not null) inherits(p1_nn, p1_nn_c1);
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging column "f1" with inherited definition
+create table p1_nn_c5(f1 int) inherits(p1_nn, p1_nn_c1);
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging column "f1" with inherited definition
+select  conrelid::regclass, conname, conenforced, convalidated, coninhcount
+from    pg_constraint
+where   conrelid::regclass::text = ANY ('{p1_nn, p1_nn_c1, p1_nn_c2, p1_nn_c4, p1_nn_c5}') and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+ conrelid |       conname        | conenforced | convalidated | coninhcount 
+----------+----------------------+-------------+--------------+-------------
+ p1_nn    | p1_nn_a_nn           | f           | f            |           0
+ p1_nn_c2 | p1_nn_a_nn           | t           | t            |           2
+ p1_nn_c5 | p1_nn_a_nn           | t           | t            |           2
+ p1_nn_c1 | p1_nn_c1_a_nn        | t           | t            |           1
+ p1_nn_c4 | p1_nn_c4_f1_not_null | t           | t            |           2
+(5 rows)
+
+-- nn_enforced_tbl1 is used for pg_upgrade tests, so don't drop it
+-- Test ALTER CONSTRAINT INHERIT for not enforced not null
+create table inh_nn1 (f1 int, constraint nn not null f1 not enforced no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2);
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging column "f1" with inherited definition
+create table inh_nn4 (f1 int) inherits (inh_nn1, inh_nn2, inh_nn3);
+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"
+NOTICE:  merging column "f1" with inherited definition
+alter table inh_nn2 add constraint nn2 not null f1;
+alter table inh_nn1 alter constraint nn inherit;
+select  conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal
+from    pg_constraint
+where   conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+and     contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+ conrelid | conname | conkey | conenforced | convalidated | coninhcount | connoinherit | conislocal 
+----------+---------+--------+-------------+--------------+-------------+--------------+------------
+ inh_nn1  | nn      |      1 | f           | f            |           0 | f            | t
+ inh_nn2  | nn2     |      1 | t           | t            |           1 | f            | t
+ inh_nn3  | nn2     |      1 | t           | t            |           2 | f            | f
+ inh_nn4  | nn2     |      1 | t           | t            |           3 | f            | f
+(4 rows)
+
+drop table inh_nn1 cascade;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table inh_nn2
+drop cascades to table inh_nn3
+drop cascades to table inh_nn4
+create table inh_nn1 (f1 int, constraint nn not null f1 no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn2 not null f1 not enforced;
+-- error, parent not-null is enforcecd, child not-null cannot be not enforced
+alter table inh_nn1 alter constraint nn inherit;
+ERROR:  cannot validate NOT ENFORCED constraint "nn2" on relation "inh_nn2"
+drop table inh_nn1 cascade;
+NOTICE:  drop cascades to table inh_nn2
+--
 -- Test DROP behavior of multiply-defined CHECK constraints
 --
 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index b7f6efdd814..73b10cb178a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -668,22 +668,71 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
 
 -- error cases:
 create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null);
+create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced);
 create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null);
+create table notnull_tbl_fail (a serial constraint foo not null not enforced);
 create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a not enforced);
 create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a);
+create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a not enforced);
 create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a);
+create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a not enforced);
 create table notnull_tbl_fail (a serial, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a serial, constraint foo not null a not enforced);
 create table notnull_tbl_fail (a serial not null no inherit);
+create table notnull_tbl_fail (a serial not null not enforced);
 create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a);
+create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a not enforced);
 create table notnull_tbl_fail (a int primary key constraint foo not null no inherit);
+create table notnull_tbl_fail (a int primary key constraint foo not null not enforced);
 create table notnull_tbl_fail (a int not null no inherit primary key);
 create table notnull_tbl_fail (a int primary key, not null a no inherit);
+create table notnull_tbl_fail (a int primary key, not null a not enforced);
 create table notnull_tbl_fail (a int, primary key(a), not null a no inherit);
+create table notnull_tbl_fail (a int, primary key(a), not null a not enforced);
 create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a not enforced);
 create table notnull_tbl_fail (a int generated by default as identity not null no inherit);
 
+create table notnull_tbl_fail (a int generated by default as identity not null not enforced);
+alter table notnull_tbl1 add column b int not null not enforced; --ok
+alter table notnull_tbl1 alter column b add generated always as identity;
+alter table notnull_tbl1 add column c int not null not enforced, alter column c add generated always as identity;
+alter table notnull_tbl1 add column c int generated always as identity not null not enforced;
+alter table notnull_tbl1 add column c serial not null not enforced;
+alter table notnull_tbl1 add column c serial, add constraint notnull_tbl1_c_not_null not null c not enforced;
+
 drop table notnull_tbl1;
 
+-- NOT NULL NOT ENFORCED
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); -- error
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED);
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+INSERT INTO ne_nn_tbl VALUES (NULL); -- ok
+
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; -- error
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; -- error
+ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; -- error
+
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED;  -- error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED;  -- no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID NOT ENFORCED; -- no-op, because NOT ENFORCED imply NOT VALID.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED NO INHERIT; -- error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID; -- error, because NOT VALID imply ENFORCED
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x ENFORCED;  -- error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x; -- error
+ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; -- error
+\d+ ne_nn_tbl
+
+TRUNCATE ne_nn_tbl;
+-- error, cannot use not enforced not-null constaint for primary key
+ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x);
+ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; -- error
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; -- ok
+ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; -- ok
+\d+ ne_nn_tbl
+
 -- NOT NULL NO INHERIT
 CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT);
 CREATE TABLE ATACC2 () INHERITS (ATACC1);
@@ -1015,6 +1064,47 @@ DEALLOCATE get_nnconstraint_info;
 
 -- end NOT NULL NOT VALID
 
+-- Verify NOT NULL ENFORCED NOT ENFORCED
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT  conrelid::regclass as relname, conname, convalidated, conislocal, coninhcount, conenforced
+FROM    pg_constraint
+WHERE   conrelid = ANY($1)
+ORDER BY conrelid::regclass::text COLLATE "C", conname;
+
+-- partitioned table have enforced not-null, then partitions cannot have not enforced not-null
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int, CONSTRAINT nn1 NOT NULL a NOT ENFORCED);
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); -- error
+DROP TABLE pp_nn, pp_nn_1;
+
+CREATE TABLE nn_enforced_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a);
+CREATE TABLE nn_enforced_tbl1_1 PARTITION OF nn_enforced_tbl1 FOR VALUES IN (1,2);
+-- if partitioned table not-null is not enforced, then partitions can have enforced
+-- or not enforced not-null
+CREATE TABLE nn_enforced_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int);
+ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_2 FOR VALUES IN (3,4); -- ok
+CREATE TABLE nn_enforced_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED);
+ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_3 FOR VALUES IN (NULL,5);
+CREATE TABLE nn_enforced_tbl1_4(a int, b int);
+ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_4 FOR VALUES IN (6); -- error
+DROP TABLE nn_enforced_tbl1_4;
+EXECUTE get_nnconstraint_info('{nn_enforced_tbl1, nn_enforced_tbl1_1, nn_enforced_tbl1_2, nn_enforced_tbl1_3}');
+ALTER TABLE nn_enforced_tbl1 ALTER COLUMN a SET NOT NULL; -- error, cannot validate not-enforced
+ALTER TABLE nn_enforced_tbl1 VALIDATE CONSTRAINT nn0;   -- error, cannot validate not-enforced
+-- nn_enforced_tbl1 is used for pg_upgrade tests
+
+-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade.
+CREATE TABLE nn_notenforced (a int, b int, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+INSERT INTO nn_notenforced VALUES (NULL, 1);
+EXECUTE get_nnconstraint_info('{nn_notenforced}');
+
+-- Inherit test for pg_upgrade
+CREATE TABLE notenforced_nn_parent (a int);
+CREATE TABLE notenforced_nn_child () INHERITS (notenforced_nn_parent);
+ALTER TABLE notenforced_nn_child ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}');
+DEALLOCATE get_nnconstraint_info;
+-- end NOT NULL NOT ENFORCED
 
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..d0d860045c2 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -127,6 +127,18 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
 \d inhz
 DROP TABLE inhz;
 
+-- not null not enforced constraint
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test';
+CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS);
+\d+ not_enforced_nn_copy
+
+SELECT conname, description
+FROM  pg_description, pg_constraint c
+WHERE classoid = 'pg_constraint'::regclass
+AND   objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass
+ORDER BY conname COLLATE "C";
+
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
 	b text CHECK (length(b) > 100) NOT ENFORCED);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..4d2bad8d190 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -527,6 +527,76 @@ create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 alter table p1_c1 inherit p1;
 drop table p1, p1_c1;
 
+--
+-- Similarly, check the merging of existing constraints; a parent not-null constraint
+-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
+-- reverse is not allowed.
+--
+create table p1(f1 int constraint p1_a_nn not null);
+create table p1_c1(f1 int constraint p1_c1_nn not null not enforced);
+alter table p1_c1 inherit p1; -- error
+create table p1_c2(f1 int not null not enforced) inherits(p1); -- error
+create table p1_c2(f1 int not null not enforced) inherits(p1_c1); -- ok
+select conenforced from pg_constraint where conrelid = 'p1_c2'::regclass and contype = 'n';
+drop table if exists p1, p1_c1, p1_c2;
+
+create table p1(f1 int);
+create table p1_c1() inherits(p1);
+alter table p1 add constraint p1_nn_1 not null f1 not enforced;
+alter table p1_c1 add constraint p1_nn_1 not null f1 enforced; -- error, column f1 already have not enforced
+
+-- not allowed: child is not enforced, parent is enforced
+alter table p1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_x not null f1 not enforced;
+alter table p1 add constraint nn not null f1 enforced; -- error
+
+alter table p1_c1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_v not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; -- error
+drop table p1 cascade;
+
+
+create table p1_nn(f1 int constraint p1_nn_a_nn not null not enforced);
+create table p1_nn_c3(f1 int);
+alter table p1_nn_c3 inherit p1_nn; -- error, because p1_nn_c3 does not have not-null constraint
+drop table p1_nn_c3;
+create table p1_nn_c1(f1 int constraint p1_nn_c1_a_nn not null);
+alter table p1_nn_c1 inherit p1_nn; -- it's ok for parent not-null is not enforced while child is enforced
+create table p1_nn_c2() inherits(p1_nn, p1_nn_c1); -- merged multiple not-null constraints produce an enforced one
+\d+ p1_nn_c2
+
+create table p1_nn_c4(f1 int not null not enforced) inherits(p1_nn, p1_nn_c1); -- error, parent (p1_nn_c1) have enforced
+-- merged multiple not-null constraints produce an enforced one, below two will be success.
+create table p1_nn_c4(f1 int not null) inherits(p1_nn, p1_nn_c1);
+create table p1_nn_c5(f1 int) inherits(p1_nn, p1_nn_c1);
+select  conrelid::regclass, conname, conenforced, convalidated, coninhcount
+from    pg_constraint
+where   conrelid::regclass::text = ANY ('{p1_nn, p1_nn_c1, p1_nn_c2, p1_nn_c4, p1_nn_c5}') and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+-- nn_enforced_tbl1 is used for pg_upgrade tests, so don't drop it
+
+-- Test ALTER CONSTRAINT INHERIT for not enforced not null
+create table inh_nn1 (f1 int, constraint nn not null f1 not enforced no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2);
+create table inh_nn4 (f1 int) inherits (inh_nn1, inh_nn2, inh_nn3);
+alter table inh_nn2 add constraint nn2 not null f1;
+alter table inh_nn1 alter constraint nn inherit;
+
+select  conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal
+from    pg_constraint
+where   conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+and     contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+
+drop table inh_nn1 cascade;
+create table inh_nn1 (f1 int, constraint nn not null f1 no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn2 not null f1 not enforced;
+-- error, parent not-null is enforcecd, child not-null cannot be not enforced
+alter table inh_nn1 alter constraint nn inherit;
+drop table inh_nn1 cascade;
+
 --
 -- Test DROP behavior of multiply-defined CHECK constraints
 --
-- 
2.34.1