CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 2 of 5 · 1 min read

The constraints already seen

PRIMARY KEY, FOREIGN KEY and NOT NULL, named as one family.

Think of a form with some fields marked required. One field must be an ID nobody else shares. Some fields must point at an entry that already exists elsewhere.

In plain words: PRIMARY KEY requires a column, or set of columns, to be unique and never NULL, identifying each row. FOREIGN KEY requires a column's value to match an existing row in another table, or be NULL if nullable. NOT NULL simply forbids a column from ever being left blank.

A worked example. orders.customer_id INTEGER NOT NULL REFERENCES customers(customer_id) combines NOT NULL with a foreign key in one declaration.

The key lines. Already shown in earlier modules; repeated here as one list:

customer_id INTEGER PRIMARY KEY
customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
full_name   TEXT NOT NULL

Integrity constraint

An integrity constraint is a rule the database enforces automatically on every insert and update, rejecting any write that would violate it. PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK and UNIQUE are the five constraint types covered in this subject.

Example: an insert attempting to leave full_name blank, where it is declared NOT NULL, is rejected before it is stored, with no application code needing to check it first.