Constraints

Early on I said a relational database “can reject a row that is missing a course number.” I never explained how. This section explains how.

A constraint is a rule attached to a column, or to a table, that the database checks every time a row is written. If a row breaks the rule, the database refuses to store it. You have already met two constraints: a primary key must be unique, and a foreign key must point at a row that exists. Those are set in place as you mark a column as a primary key or as a foreign key. There are other constraints, too.

Data type. Every column is declared to hold one kind of value, and the database checks that a written value matches. The credits column holds a number. Try to write four instead of 4 and the database refuses to store it.

Not null. A column can be marked as required. The course name column in the courses table can carry this constraint. Try to insert a course with no name, and the database refuses to store it.

Uniqueness. A primary key is always unique, but a column does not have to be a primary key to carry this rule. Take the email column in the professors table. The professor ID is the primary key, not the email, but nothing about that stops two professors from sharing an email by mistake. Mark the email column unique, and the database refuses to insert a second professor with an email already in use, even though email is not what the table is keyed on.

Check constraints. Some rules are not about presence, type, or uniqueness, but about the value itself. Credits should be a small positive number, not zero and not negative. A check constraint can express such a rule. Once set, try to insert a course with 0 credits, and the database refuses it.

You can also enforce these rules at the application level, before the data reaches the database. But enforcing them in the database gives you a stronger guarantee: it holds no matter which application writes to the database.