

ALTER COLUMN ... SET NOT NULL ... in order to mark the NOT NULL
constraint as local.
You suggested using ALTER TABLE ... RENAME CONSTRAINT, but renaming an
inherited constraint is not allowed.
#alter table notnull_tbl4_cld3 rename constraint
notnull_tbl4_a_not_null to a_nn;
ERROR:  cannot rename inherited constraint "notnull_tbl4_a_not_null"
... that goes back 13 years.
commit 39d74e346c083aa371ba64c4edb1332c40b56530
Author: Peter Eisentraut <[email-masked]>
Date:   Sat Mar 10 20:19:13 2012 +0200

    Add support for renaming constraints

    reviewed by Josh Berkus and Dimitri Fontaine
So I don't think we should change that behaviour.

Instead I chose to fix both the problems by
1. In AdjustNotNullInheritance(), if a NOT NULL constraint is
converted to local and the user has specified a name for it (ALTER
TABLE ... ADD CONSTRAINT ... NOT NULL ..), change the name alongwith
setting conislocal. The user who specified a non-default name in ADD
CONSTRAINT would expect the "local" constraint to be named
accordingly. If a local constraint is ADDed again with a different
name, right now the patch ignores the new name but that could be
changed to throw an error similar to ALTER TABLE ... RENAME CONSTAINT.
2. In determineNotNullFlags(), if a NOT NULL constraint is both
inherited and local, preserve its name even if it's default. That way,
it will be dumped as ALTER TABLE ... ADD CONSTRAINT ... NOT NULL ...
instead of ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. An exception
to this is a local NOT NULL column with default name for NOT NULL
constraint. Such NOT NULL constraints are specified in the CREATE
TABLE ... INHERITS .. command itself. That way they preserve their
default name.

The first change will preserve the given name of the child constraint.
Both changes together will preserve the default name of the child
constraint.
See the patch. I have also added some tests.

Alternate approach which I haven't tried, but did consider and left aside.
1. Allow RENAME CONSTAINT on a local constaint even if it's inherited.
Given that the behaviour is 13 years old, I am hesitant to change it.
2. Allow local and inherited NOT NULL constraints to co-exist
separately (similar to primary key constraint). I am not sure why we
don't allow this behaviour. AdjustNotNullInheritance() doesn't explain
the reason.

--
Best Wishes,
Ashutosh Bapat

--0000000000006d75d70627e367cf
Content-Type: text/x-patch; charset="US-ASCII";
	name="0001-Dump-and-restore-of-inherited-and-local-chi-20241127.patch"
Content-Disposition: attachment;
	filename="0001-Dump-and-restore-of-inherited-and-local-chi-20241127.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m3zt8wob0>
X-Attachment-Id: f_m3zt8wob0

--0000000000006d75d70627e367cf--