NOT ENFORCED constraint feature

Hi,

In SQL Standard 2023, a table's check or referential constraint can be
either ENFORCED or NOT ENFORCED.

Currently, when a DML statement is executed, all enforced constraints
are validated. If any constraint is violated, an exception is raised,
and the SQL transaction is rolled back. These are referred to as
ENFORCED constraints.

On the other hand, a NOT ENFORCED constraint is a rule defined in the
database but not checked when data is inserted or updated. This can
help speed up large data imports, improve performance when strict
validation isn't required, or handle cases where constraints are
enforced externally (e.g., by application logic). It also allows the
rule to be documented without enforcing it during normal operations.

The attached patch proposes adding the ability to define CHECK and
FOREIGN KEY constraints as NOT ENFORCED. If neither ENFORCED nor
NOT ENFORCED is explicitly specified when defining a constraint, the
default setting is that the constraint is ENFORCED. Note that this
addition differs from the properties of NOT VALID and DEFERRABLE
constraints, which skip checks only for existing data and determine
when to perform checks, respectively. In contrast, NOT ENFORCED
completely skips the checks altogether.

Adding NOT ENFORCED to CHECK constraints is simple, see 0001 patch,
but implementing it for FOREIGN KEY constraints requires more code due
to triggers, see 0002 - 0005 patches. There are various approaches for
implementing NOT ENFORCED foreign keys, what I thought of:

1. When defining a NOT ENFORCED foreign key, skip the creation of
triggers used for referential integrity check, while defining an
ENFORCED foreign key, remain the same as the current behaviour. If an
existing foreign key is changed to NOT ENFORCED, the triggers are
dropped, and when switching it back to ENFORCED, the triggers are
recreated.

2. Another approach could be to create the NOT ENFORCED constraint
with the triggers as usual, but disable those triggers by updating the
pg_trigger catalog so that they are never executed for the check. And
enable them when the constraint is changed back to ENFORCED.

3. Similarly, a final approach would involve updating the logic where
trigger execution is decided and skipping the execution if the
constraint is not enforced, rather than modifying the pg_trigger
catalog.

In the attached patch, the first approach has been implemented. This
requires more code changes but prevents unused triggers from being
left in the database and avoids the need for changes all over the
place to skip trigger execution, which could be missed in future code
additions.

The ALTER CONSTRAINT operation in the patch added code to handle
dropping and recreating triggers. An alternative approach would be to
simplify the process by dropping and recreating the FK constraint,
which would automatically handle skipping or creating triggers for NOT
ENFORCED or ENFORCED FK constraints. However, I wasn't sure if this
was the right approach, as I couldn't find any existing ALTER
operations that follow this pattern.

Also note that the existing CHECK constraints currently do not support
ALTER operations. This functionality may be essential for modifying a
constraint's enforcement status; otherwise, users must drop and
recreate the CHECK constraint to change its enforceability. I have not
yet begun work on this, as it would involve significant code
refactoring and updates to the documentation. I plan to start this
once we finalise the design and reach a common understanding regarding
this proposal.

Any comments, suggestions, or assistance would be greatly appreciated.
Thank you.

--
Regards,
Amul Sul
EDB: http://www.enterprisedb.com