Database Keys in Design: Types, Constraints, and Worked Examples

In relational database design, “keys” are the mechanisms that enforce uniqueness and relationships between entities. At a conceptual level, key families are defined using the ideas of superkeys, candidate keys, and primary keys (the ones you choose as the main identifier). At an implementation level, SQL provides constraints such as PRIMARY KEY, FOREIGN KEY, and UNIQUE that enforce the intended semantics.

A typical design workflow is: (1) model entities and relationships, (2) derive candidate keys from business rules and functional dependencies, (3) pick a primary key, (4) define foreign keys to model relationships, and (5) add supporting constraints like uniqueness and nullability.

Key taxonomy (conceptual → logical → physical)

keyword
keyword
keyword
keyword
keyword

Important: Conceptual keys (super/candidate/primary) are about logical identification. SQL constraints are about enforcement (and they map to key ideas but don’t always map 1:1).

1) Conceptual keys: superkeys, candidate keys, primary keys

Formal intuition

• A superkey is any set of attributes that can uniquely identify a row.
• A candidate key is a minimal superkey (remove any attribute and you lose uniqueness).
• A primary key is one candidate key chosen to serve as the official identifier.

Worked example: Student enrollment database

Assume we have a relation:

STUDENT(StudentID, SSN, Email, Name)

Business rules:

• Each StudentID is unique.
• Each SSN is unique.
• Emails may change over time, but assume for now each current Email is unique.

Then candidate keys are likely:

• {StudentID}
• {SSN}
• {Email} (if the rule “Email is unique” is enforced)

Corresponding superkeys include any superset of a candidate key, for example:

• {StudentID, Name}
• {SSN, Email}

A primary key might be chosen as {StudentID} because it’s stable and non-null, even if SSN also works.

Key selection rationale

Choosing the primary key typically considers:

• stability over time (don’t change keys due to real-world changes),
• minimal size and simplicity (short, numeric IDs often preferred),
• nullability (primary keys are not null),
• privacy/legal constraints (SSN may be sensitive).

2) Implementing keys in SQL: PRIMARY KEY and UNIQUE

PRIMARY KEY

A PRIMARY KEY constraint enforces:

• uniqueness of the key columns,
• not-null behavior (in standard SQL, primary key columns are not allowed to be NULL).

Example: DEPARTMENT(DeptID, DeptName)
Design decision: DeptID is the identifier.

1CREATE TABLE Department (
2  DeptID   INT PRIMARY KEY,
3  DeptName VARCHAR(100) NOT NULL
4);

UNIQUE constraint (alternate/candidate keys)

A UNIQUE constraint enforces that values are distinct, but:

• unlike PRIMARY KEY, UNIQUE columns can often be NULL (DB-dependent; standard behavior is usually “NULLs are not equal” so multiple NULLs may be allowed).

Use UNIQUE when you want to enforce an alternate candidate key.

Example: STUDENT(StudentID, Email, SSN, Name)
Primary key is StudentID; Email and SSN are alternate keys.

1CREATE TABLE Student (
2  StudentID INT PRIMARY KEY,
3  Email     VARCHAR(255) UNIQUE,
4  SSN       CHAR(11) UNIQUE,
5  Name      VARCHAR(200) NOT NULL
6);

3) Foreign keys: modeling relationships and referential integrity

A FOREIGN KEY links a column (or set of columns) in one table to a key in another table. This enforces referential integrity: values in the foreign key must match existing referenced rows (unless NULL is allowed).

One-to-many relationship example: Department → Students

• One Department has many Students.
• Each Student belongs to exactly one Department (for this example).

STUDENT( StudentID, Name, DeptID )
DeptID is a foreign key referencing DEPARTMENT(DeptID).

1CREATE TABLE Department (
2  DeptID INT PRIMARY KEY,
3  DeptName VARCHAR(100) NOT NULL
4);
5
6CREATE TABLE Student (
7  StudentID INT PRIMARY KEY,
8  Name       VARCHAR(200) NOT NULL,
9  DeptID     INT NOT NULL,
10  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
11);

Many-to-many example: Students ↔ Courses

Use a junction table.

• STUDENT(StudentID, ...)
• COURSE(CourseID, ...)
• ENROLLMENT(StudentID, CourseID, EnrollDate)

Typically:

• Primary key in ENROLLMENT is the combination {StudentID, CourseID} (composite key),
• Each component is a foreign key referencing its parent.

1CREATE TABLE Enrollment (
2  StudentID INT NOT NULL,
3  CourseID  INT NOT NULL,
4  EnrollDate DATE NOT NULL,
5  PRIMARY KEY (StudentID, CourseID),
6  FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
7  FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)
8);

4) Composite keys and surrogate vs natural keys

Composite primary keys

Sometimes a single attribute doesn’t uniquely identify a row, but a combination does.

• Example: ENROLLMENT uses (StudentID, CourseID).

Composite keys are useful for associative entities where the identity is naturally a pair (or tuple).

Surrogate keys vs natural keys

• Surrogate key: artificial identifier (e.g., auto-increment ID).
• Natural key: real-world attribute(s) with business meaning (e.g., SSN, email).

Example contrast

• Natural: Student(SSN) as primary key
• Surrogate: Student(StudentID) as primary key; SSN becomes UNIQUE

Trade-offs:

• Surrogate keys are often stable and rarely change.
• Natural keys can be meaningful but may change and can be sensitive.

5) Key constraints summary (what each one enforces)