Re: DEFERRABLE NOT NULL constraint

Andreas Joseph Krogh wrote:
> It's currently (9.2) not possible to define DEFERRABLE NOT NULL constraints. Meaning the following is
> not valid:
>
> CREATE TABLE my_table(
> id varchar PRIMARY KEY,
> stuff_id BIGINT NOT NULL DEFERRABLE INITIALLY DEFERRED
> );
>
> While it's possible to define a trigger to enforce this, like this:
>
> CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm_relation DEFERRABLE INITIALLY
> DEFERRED
> FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();
>
> And have the my_table_check_stuff_id_nn_tf() raise an exception if "stuff_id" is null.
>
> Having deferrable constraints on FKs and UKs is really nice and when working with ORMs it's almost
> impossible to not use this feature.
>
> Are there any plans to make NOT NULL constraints deferrable so one can avoid the trigger
> "boilerplate"?

Not that I know of.

There's an entry in the TODO list that recognizes that it would
be desirable to make NOT NULL a regular constraint (you can do
that today by using CHECK (col IS NOT NULL) instead).

But CHECK constraints are also not deferrable...

Yours,
Laurenz Albe