Constraints

A data type says what kind of value a column may hold; a constraint goes further and restricts which values are acceptable. Constraints are certain types of restrictions placed on the data values that an attribute can have, and their purpose is to ensure the accuracy and reliability of the data. A table protected by well-chosen constraints simply cannot accumulate certain kinds of bad data — a missing roll number, a duplicate admission number — because the RDBMS rejects the offending entry at the moment of insertion.

Constraints are optional by design: it is not mandatory to define a constraint for each attribute of a table. You apply one only where the design demands a rule.

The commonly used SQL constraints (the book's Table 8.2) are:

A few of these reward a closer look.

NULL is not zero and not a blank string — it stands for information that is missing, unknown, or not applicable. NOT NULL therefore forces a value to be supplied for that column in every row.

DEFAULT is the gentlest constraint: rather than rejecting anything, it fills in a pre-decided value when the user supplies none.

PRIMARY KEY is the constraint that gives a table its identity: the column so marked distinguishes every row from every other row.

FOREIGN KEY is what links tables together — a column in one table that refers to the primary key of another table, so that the two relations stay consistent with each other. …