2 Keys and Relationships

Identify rows with keys

A database key provides a reliable way to identify rows. A candidate key is a minimal set of columns that uniquely identifies every row. A table can have more than one candidate key: for example, both an employee ID and a work email could qualify if each is guaranteed to be unique. Minimality matters: if a column can be removed and the remaining columns still uniquely identify each row, the original set was not minimal.

The database designer chooses one candidate key as the primary key, the table’s main identifier. It must be unique and non-null. A table can have only one primary key, but that key may use more than one column. Such a key is called a composite key. Other candidate keys can be enforced with UNIQUE constraints.

For example, employee_id might be selected as the primary key while work_email is kept unique as an alternative identifier.

Connect tables with foreign keys

Tables connect when a column in one table refers to a key in another. A foreign key is a column, or set of columns, whose values refer to a primary key or other unique key in the referenced table. This enforces referential integrity: every non-null foreign-key value must match an existing referenced value. The referencing and referenced columns must correspond in number and have compatible data types.

In this example, each order must refer to an existing customer. NOT NULL makes the relationship required for every order:

A foreign key may instead be nullable when the relationship is optional. Use NOT NULL when every row must be linked to a referenced row.

Represent table relationships

The arrangement of foreign keys expresses the relationship between tables.

• In a one-to-many relationship, one customer can have many orders, while each order points to one customer. The orders.customer_id foreign key represents this pattern.

• In a one-to-one relationship, each row is associated with at most one row in the other table. Making the foreign key UNIQUE prevents multiple rows from referring to the same parent.

• In a many-to-many relationship, rows in either table can be related to many rows in the other. This is usually represented with a junction table containing foreign keys to both tables.

For example, enrollments connect students and courses. A composite primary key prevents the same student-course pair from being entered more than once:

Each enrollment row connects one student to one course, while a student can enroll in multiple courses and a course can include multiple students.

Enforce rules with constraints

A constraint is a rule the database checks when data is inserted or changed. Constraints make key and data requirements enforceable rather than relying only on application code.

• PRIMARY KEY uniquely identifies each row and disallows null key values.

• FOREIGN KEY requires values to match a referenced key, subject to the foreign key’s nullability rules.

• UNIQUE prevents duplicate values in a column or combination of columns.

• NOT NULL requires a value to be present.

• CHECK requires a row to satisfy a condition, such as CHECK (quantity > 0).

When a referenced row is updated or deleted, actions such as CASCADE, SET NULL, or RESTRICT control what happens to dependent rows. Choose an action to fit the meaning of the data: for example, cascading deletion may be unsuitable if orders must be retained for records. Exact behavior and syntax can vary between database systems.

Takeaway: Choose a minimal candidate key, designate one as the primary key, and use foreign keys and appropriate constraints to express relationships and protect data consistency.