Null (SQL): Difference between revisions

Revision as of 15:00, 22 May 2021

Revision as of 07:43, 20 July 2021

Line 439:

</syntaxhighlight>

Because of the change in designated values relative to the {{mono|WHERE}} clause, from a logic perspective the law of excluded middle is a tautology for {{mono|CHECK}} constraints, meaning <code>CHECK (''p'' OR NOT ''p'')</code> always succeeds. Furthermore, assuming Nulls are to be interpreted as existing but unknown values, some pathological CHECKs like the one above allow insertion of Nulls that could never be replaced by any non-null value.

In order to constrain a column to reject Nulls, the <code>NOT NULL</code> constraint can be applied, as shown in the example below. The <code>NOT NULL</code> constraint is semantically equivalent to a [[check constraint]] with an <code>IS NOT NULL</code> predicate.

Line 458:

</syntaxhighlight>

would allow insertion of rows where author_last or author_first are {{NULL}} irrespective of how the table Authors is defined or what it contains. More precisely, a null in any of these fields would allow any value in the other one, even on that is not found in Authors table. For example, if Authors contained only {{code|('Doe', 'John')}}, then {{code|('Smith', NULL)}} would satisfy the foreign key constraint. [[SQL-92]] added two extra options for narrowing down the matches in such cases. If <code>MATCH PARTIAL</code> is added after the <code>REFERENCES</code> declaration then any non-null must match the foreign key, e. g. {{code|('Doe', NULL)}} would still match, but {{code|('Smith', NULL)}} would not. Finally, if <code>MATCH FULL</code> is added then {{code|('Smith', NULL)}} would not match the constraint either, but {{code|(NULL, NULL)}} would still match it.

==Outer joins==