Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 79 ยท The Relational Model

Keys and Integrity Constraints

Understand superkeys, candidate, primary, alternate and foreign keys, entity and referential integrity, domain constraints and ON DELETE actions with live SQL.

A relation is a set of tuples, and a set has no duplicates. But how does the database know two tuples are "the same"? And how does it keep a tuple in one relation pointing to a tuple in another that really exists? The answer to both is keys and the integrity constraints built on them. Keys identify tuples; constraints are the rules the database refuses to let you break.

This lesson gives the theory names and definitions that exam questions use: superkey, candidate key, primary key, alternate key and foreign key; the two classic rules, entity integrity and referential integrity; and domain constraints. It then shows each rule being enforced (and violated) live. For the SQL syntax of declaring keys, see Primary, Candidate, Composite and Foreign Keys and Foreign Keys; this lesson is about what the ideas mean.

The family of keys

Superkey Any set of attributes that identifies a tuple.
Candidate key A minimal superkey.
Primary key The candidate key we choose.
Alternate key A candidate key we did not choose.
1 / 2

Superkeys, candidate keys and primary keys

A superkey is a set of attributes whose values are different for every tuple in any valid instance. Take Student(student_id, email, name, birth_date). If ids are unique, then is a superkey. So is {student_id, name}, since adding attributes to a superkey still gives a superkey. Even the full set of all attributes is a superkey (it works because a relation has no duplicate tuples).

A candidate key is a minimal superkey: remove any attribute and it stops identifying. is a candidate key. {student_id, name} is not, because name can be removed. If emails are also unique, is a second candidate key.

The primary key is the one candidate key the designer picks as the main identifier. The other candidate keys are alternate keys (secondary keys), and in SQL you declare them with UNIQUE. A key can have several attributes: in Enrollment(student_id, course_id, term) the only candidate key might be the combination of all three, a composite key.

An important point: keys are statements about the meaning of the data, not about what happens to be unique today. Three students that currently have different names do not make name a key. A key must hold for every possible valid instance, which you decide from the business rules.

You can still use data to refute a key. If a column set has duplicates, it cannot be a superkey:

SQL Testing candidate keys against data

Press Run (or Ctrl + Enter) to see the result here.

student_id and email pass (4 rows, 4 distinct values), while name and name + birth_date fail because two students are both "Ana, 2005-03-14". A passing test is only evidence; a failing test is proof that the columns are not a key.

Entity integrity

The entity integrity rule says that no attribute of a primary key may be NULL. A tuple stands for a real-world thing, and a key is how we refer to it. A tuple with a missing key cannot be referred to at all, so it is not a valid fact.

In standard SQL a PRIMARY KEY implies NOT NULL. SQLite is the odd one out: for historical reasons it allows NULL in a non-integer PRIMARY KEY column unless you write NOT NULL yourself. (An INTEGER PRIMARY KEY is special and never null.) Always write the NOT NULL explicitly in SQLite tables and it behaves like the theory.

Candidate keys that are alternate keys (UNIQUE) may allow NULLs in SQL, because NULL is never equal to another NULL. That is a subtle point covered in Nulls and Three-Valued Logic.

Domain constraints

A domain constraint limits the values an attribute can take: its data type, a CHECK condition, a NOT NULL, or a DEFAULT. CHECK (price >= 0) and CHECK (status IN ('open', 'closed')) are domain constraints. They are the simplest integrity rules because they look at one value in one row.

Here entity integrity and domain constraints are at work. The script succeeds until the last statement, which is rejected on purpose:

SQL Entity integrity and domain constraints in action

Press Run (or Ctrl + Enter) to see the result here.

The first two rows are accepted; the third is rejected with CHECK constraint failed. The error message tells you which rule was broken, which is exactly the benefit of putting rules in the database.

Foreign keys and referential integrity

A foreign key is an attribute (or set) in one relation, the referencing or child relation, whose values must match a candidate key (usually the primary key) of another relation, the referenced or parent relation. The referential integrity rule states: every foreign key value must either be NULL or equal the key of an existing tuple in the parent.

flowchart LR
    C["course<br/>PK: course_id"] -->|"referenced by"| E["enrollment<br/>FK: course_id"]
    S["student<br/>PK: student_id"] -->|"referenced by"| E2["enrollment<br/>FK: student_id"]

Child rows must point at existing parent rows; the arrow shows who is referenced.

Referential integrity can be broken in four ways, and a DBMS must decide what to do in each:

Operation Problem Typical choices
Insert child Parent does not exist Reject
Update child key New value has no parent Reject
Delete parent Children are left pointing nowhere Reject, cascade, set NULL, set default
Update parent key Children point at the old value Reject, cascade, set NULL

Referential actions

The last two rows are controlled by an action you attach to the foreign key:

  • NO ACTION or RESTRICT: refuse the change while children exist (the default).
  • CASCADE: apply the change to the children too (delete the children, or update their keys).
  • SET NULL: set the child's foreign key to NULL. The column must allow it.
  • SET DEFAULT: set it to the column's default value.

Use CASCADE for rows the parent truly owns (an order's lines) and refusal for records that must survive on their own (invoices). SQLite enforces foreign keys only when you run PRAGMA foreign_keys = ON, which you must do on every new connection. Without it the constraints are stored and ignored.

SQL Referential actions: CASCADE, SET NULL and RESTRICT

Press Run (or Ctrl + Enter) to see the result here.

After deleting the Falcons, Rae and Sol remain with team_id NULL, and only the Otters' report survives (reports_left is 1). The final delete fails with FOREIGN KEY constraint failed, because the sponsor deal blocks it.

Other kinds of constraints

Real schemas combine several kinds of rules.

  • Key constraints: PRIMARY KEY and UNIQUE.
  • Not-null constraints: a value is required.
  • Check constraints: any condition on the values of a single row, even across columns (CHECK (end_date >= start_date)).
  • General (semantic) constraints: rules across rows or tables, such as "a bed holds one patient at a time". These need triggers or other mechanisms; see Enforcing Integrity.

Declaring constraints in the schema is almost always better than checking in application code. They are checked for every client, every script and every manual fix, they cannot be forgotten, and the optimizer can sometimes even use them.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In Student(id, email, name), both id and email are unique. Which statement is correct?

    A candidate key is a minimal superkey. {id, name} is a superkey but not minimal. Both {id} and {email} are minimal, so they are candidate keys, and the one not chosen as primary is alternate.
  2. 2. Which integrity rule says a primary key value can never be NULL?

    Entity integrity requires every tuple to be identifiable, so no part of its primary key may be NULL.
  3. 3. A foreign key orders.customer_id has ON DELETE CASCADE. What happens when a customer row is deleted?

    CASCADE propagates the delete to every child row that references the deleted parent.
  4. 4. Which values may a nullable foreign key column hold?

    Referential integrity requires a non-NULL foreign key value to match a parent key. NULL means "no reference" and is allowed when the column is nullable.
  5. 5. In SQLite, foreign key constraints are declared but rows with missing parents are still accepted. What is the likely reason?

    SQLite enforces foreign keys only when the connection has run PRAGMA foreign_keys = ON.

Summary

  • A superkey identifies tuples; a candidate key is a minimal superkey; the primary key is the chosen candidate key and the rest are alternate keys.
  • Keys express business meaning and must hold for every valid instance; data can only refute a key, never prove one.
  • Entity integrity: primary key values are never NULL (in SQLite, write NOT NULL explicitly).
  • Referential integrity: a foreign key is NULL or matches an existing parent key; actions are RESTRICT, CASCADE, SET NULL and SET DEFAULT.
  • Domain constraints (type, CHECK, NOT NULL, DEFAULT) restrict the values of one attribute.
  • SQLite needs PRAGMA foreign_keys = ON on every connection.

Next: Nulls and Three-Valued Logic, where the special value NULL changes how comparisons work.