Check Constraints and Foreign Keys
The primary place in which SQL three-valued logic intersects with SQL Data Definition Language (DDL) is in the form of check constraints. A check constraint placed on a column operates under a slightly different set of rules than those for the DML WHERE
clause. While a DML WHERE
clause must evaluate to True for a row, a check constraint must not evaluate to False. (From a logic perspective, the designated values are True and Unknown.) This means that a check constraint will succeed if the result of the check is either True or Unknown. The following example table with a check constraint will prohibit any integer values from being inserted into column i, but will allow Null to be inserted since the result of the check will always evaluate to Unknown for Nulls.
Because of the change in designated values relative to the WHERE clause, from a logic perspective the law of excluded middle is a tautology for CHECK constraints, meaning CHECK (p OR NOT p) 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 NOT NULL
constraint can be applied, as shown in the example below. The NOT NULL
constraint is semantically equivalent to a check constraint with an IS NOT NULL
predicate.
By default check constraints against foreign keys succeed if any of the fields in such keys are Null. For example the table
CREATE TABLE Books ( title VARCHAR(100), author_last VARCHAR(20), author_first VARCHAR(20), FOREIGN KEY (author_last, author_first) REFERENCES Authors(last_name, first_name));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 ('Doe', 'John'), then ('Smith', NULL) would satisfy the foreign key constraint. SQL-92 added two extra options for narrowing down the matches in such cases. If MATCH PARTIAL
is added after the REFERENCES
declaration then any non-null must match the foreign key, e. g. ('Doe', NULL) would still match, but ('Smith', NULL) would not. Finally, if MATCH FULL
is added then ('Smith', NULL) would not match the constraint either, but (NULL, NULL) would still match it.
Read more about this topic: Null (SQL)
Famous quotes containing the words check, constraints, foreign and/or keys:
“When you go out, check the look of the sky; when you come in, check the looks on peoples faces.”
—Chinese proverb.
“Play is a major avenue for learning to manage anxiety. It gives the child a safe space where she can experiment at will, suspending the rules and constraints of physical and social reality. In play, the child becomes master rather than subject.... Play allows the child to transcend passivity and to become the active doer of what happens around her.”
—Alicia F. Lieberman (20th century)
“I am tired of loving a foreign muse.”
—Stephen Vincent Benét (18981943)
“without luggage or defenses,
giving up my car keys and my cash,
keeping only a pack of Salem cigarettes
the way a child holds on to a toy.
I signed myself in where a stranger
puts the inked-in Xs”
—Anne Sexton (19281974)