Introduction

A relational database should do more than store values. It should reject data that violates the domain rules represented by its schema.

Keys identify rows and connect related tables. Constraints define rules that every valid database state must satisfy.

Keys and constraints help ensure that:

• Every important row can be identified
• Duplicate business identifiers are rejected
• Required values cannot be omitted
• Relationships point to valid parent records
• Numeric and date values remain within valid ranges
• Invalid lifecycle states cannot be stored
• One-to-one and many-to-many relationships remain valid
• Application defects do not silently corrupt stored data

In your System Design curriculum, Keys and Constraints is Topic 5.3 under Relational Data Modeling & SQL. It follows normalization and denormalization and precedes ACID, transactions, isolation, B-tree and composite indexes, query plans, and connection pools.

Prerequisites

What Is a Key?

A key is an attribute or set of attributes used to identify rows, enforce uniqueness, or establish relationships between tables.

Common key categories include:

• Super key
• Candidate key
• Primary key
• Alternate key
• Composite key
• Natural key
• Surrogate key
• Foreign key
• Partial key

Key Terminology

Super Keys

A super key is any set of attributes that uniquely identifies a row. It can contain attributes that are unnecessary for uniqueness.

CUSTOMER attributes:

customer_id
customer_number
email_address
display_name


Possible super keys:

{customer_id}

{customer_number}

{email_address}

{customer_id, display_name}

{customer_number, email_address}

If customer_id uniquely identifies a customer, adding display_name does not improve identification. The resulting attribute set remains a super key but is not minimal.

Candidate Keys

A candidate key is a minimal super key. Removing any attribute from a candidate key causes it to lose its uniqueness property.

Candidate keys for CUSTOMER:

customer_id
customer_number
email_address


Each candidate key:

- Uniquely identifies a row
- Contains no unnecessary attribute

Candidate keys come from actual business rules. An email address is a candidate key only when the system guarantees that every relevant customer has one unique, stable email under the defined scope.

Primary Keys

A primary key is the candidate key selected as the table's principal row identifier.

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    customer_number VARCHAR(30) NOT NULL,
    display_name VARCHAR(200) NOT NULL,
    email_address VARCHAR(254) NOT NULL
);

A suitable primary key should normally be:

• Unique
• Non-null
• Stable
• Minimal
• Available for every row
• Appropriate for references

Alternate Keys

Candidate keys that are not selected as the primary key are alternate keys. They should normally be protected through UNIQUE and NOT NULL constraints when the business rules require a value for every row.

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    customer_number VARCHAR(30) NOT NULL,
    email_address VARCHAR(254) NOT NULL,
    display_name VARCHAR(200) NOT NULL,

    CONSTRAINT uq_customers_number
        UNIQUE (customer_number),

    CONSTRAINT uq_customers_email
        UNIQUE (email_address)
);

The unique constraints preserve the candidate-key rules even though customer_id is the selected primary key.

Composite Keys

A composite key contains more than one column. The combination must be unique even when individual components repeat.

Order-line Example

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(18, 2) NOT NULL,

    CONSTRAINT pk_order_lines
        PRIMARY KEY
        (
            order_id,
            line_number
        )
);

Composite keys are common in weak entities, junction tables, historical tables, and relationships identified by participating entity keys.

Natural Keys

A natural key is based on meaningful domain information.

Examples can include:

• An ISO country code
• A formally assigned product code
• A tenant-scoped employee number
• A course code within an institution

CREATE TABLE countries
(
    country_code CHAR(2) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,

    CONSTRAINT uq_countries_name
        UNIQUE (country_name)
);

Natural-key Benefits

• Can be meaningful to users and integrations
• Can prevent duplicate domain records naturally
• Can reduce the need for a second business identifier

Natural-key Risks

• The business value can change
• The value can be long
• Uniqueness can be scoped rather than global
• The value can expose sensitive information
• Business rules can change after deployment

Surrogate Keys

A surrogate key is generated by the system and has no inherent domain meaning.

CREATE TABLE products
(
    product_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,

    product_code VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,

    CONSTRAINT uq_products_code
        UNIQUE (product_code)
);

Database-specific identity syntax varies. Other implementations can use sequences, UUIDs, or application-generated identifiers.

Natural vs Surrogate Keys

Foreign Keys

A foreign key is a column or set of columns that references a candidate key in another table or the same table.

Customer and Order Relationship

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    display_name VARCHAR(200) NOT NULL
);

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_status VARCHAR(30) NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

The foreign key prevents an order from referring to a customer that does not exist.

Valid:

Customer 42 exists.
Order 9001 references Customer 42.


Invalid:

Customer 99 does not exist.
Order 9002 references Customer 99.

Result:

Database rejects the invalid relationship.

Referential Integrity

Referential integrity ensures that a foreign-key value references a valid parent key or is null when the relationship is optional.

It helps prevent:

• Orders without valid customers
• Order lines without valid orders
• Payments without valid invoices or orders
• Enrollments without valid learners or courses
• Memberships without valid users or projects
• Orphaned dependent records

Self-referencing Foreign Keys

A foreign key can reference another row in the same table.

CREATE TABLE employees
(
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    manager_employee_id BIGINT NULL,

    CONSTRAINT fk_employees_manager
        FOREIGN KEY (manager_employee_id)
        REFERENCES employees (employee_id),

    CONSTRAINT ck_employee_not_own_manager
        CHECK
        (
            manager_employee_id IS NULL
            OR manager_employee_id <> employee_id
        )
);

The check prevents direct self-management. Preventing longer management cycles can require transaction-level application logic or database-specific recursive validation.

Composite Foreign Keys

A composite foreign key references a composite parent key. Column order and compatible data types must align with the referenced key.

CREATE TABLE tenant_products
(
    tenant_id BIGINT NOT NULL,
    product_code VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,

    PRIMARY KEY
    (
        tenant_id,
        product_code
    )
);

CREATE TABLE tenant_order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    tenant_id BIGINT NOT NULL,
    product_code VARCHAR(50) NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    ),

    CONSTRAINT fk_order_line_product
        FOREIGN KEY
        (
            tenant_id,
            product_code
        )
        REFERENCES tenant_products
        (
            tenant_id,
            product_code
        )
);

Including the tenant in the key can also help enforce that a child relationship remains within the intended tenant boundary.

NOT NULL Constraint

NOT NULL requires a column to contain a value for every row.

CREATE TABLE courses
(
    course_id BIGINT PRIMARY KEY,
    course_title VARCHAR(200) NOT NULL,
    course_status VARCHAR(30) NOT NULL,
    published_at TIMESTAMP NULL
);

The title and status are mandatory. Publication time is optional because an unpublished course can legitimately have no publication timestamp.

UNIQUE Constraint

A UNIQUE constraint prevents duplicate values or duplicate column combinations according to the database platform's null semantics.

Single-column Uniqueness

CREATE TABLE users
(
    user_id BIGINT PRIMARY KEY,
    email_address VARCHAR(254) NOT NULL,

    CONSTRAINT uq_users_email
        UNIQUE (email_address)
);

Composite Uniqueness

CREATE TABLE employees
(
    employee_id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    employee_number VARCHAR(30) NOT NULL,
    employee_name VARCHAR(200) NOT NULL,

    CONSTRAINT uq_employee_number_per_tenant
        UNIQUE
        (
            tenant_id,
            employee_number
        )
);

The same employee number can exist in different tenants, but it cannot occur twice within one tenant.

PRIMARY KEY vs UNIQUE

CHECK Constraint

A CHECK constraint requires an inserted or updated row to satisfy a Boolean condition.

Positive Quantity

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    ),

    CONSTRAINT ck_order_lines_quantity
        CHECK (quantity > 0),

    CONSTRAINT ck_order_lines_price
        CHECK (unit_price >= 0)
);

Date Range

CREATE TABLE promotions
(
    promotion_id BIGINT PRIMARY KEY,
    starts_at TIMESTAMP NOT NULL,
    ends_at TIMESTAMP NOT NULL,

    CONSTRAINT ck_promotion_dates
        CHECK (ends_at > starts_at)
);

Allowed Values

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    order_status VARCHAR(30) NOT NULL,

    CONSTRAINT ck_orders_status
        CHECK
        (
            order_status IN
            (
                'pending',
                'confirmed',
                'processing',
                'completed',
                'cancelled'
            )
        )
);

When allowed values have attributes, relationships, translations, or independent lifecycles, a lookup table can be more suitable than a long CHECK list.

DEFAULT Values

A DEFAULT clause supplies a value when an INSERT does not provide one.

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    display_name VARCHAR(200) NOT NULL,

    customer_status VARCHAR(30)
        NOT NULL
        DEFAULT 'active',

    created_at TIMESTAMP
        NOT NULL
        DEFAULT CURRENT_TIMESTAMP
);

A default is not a substitute for validation. The generated value must still satisfy the column's data type and constraints.

Foreign-key Delete Actions

A foreign key can define what happens when a referenced parent row is deleted.

Restricted Deletion

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    ON DELETE RESTRICT

Cascading Deletion

CONSTRAINT fk_order_lines_order
    FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
    ON DELETE CASCADE

Optional Relationship

CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_employee_id)
    REFERENCES employees (employee_id)
    ON DELETE SET NULL

Foreign-key Update Actions

A foreign key can also define what happens when a referenced key value changes.

CONSTRAINT fk_order_lines_order
    FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
    ON UPDATE CASCADE

Stable surrogate primary keys normally do not need business-driven updates. Mutable natural keys can create broader update propagation.

Junction-table Constraints

A many-to-many relationship should prevent duplicate associations and invalid participants.

CREATE TABLE course_instructors
(
    course_id BIGINT NOT NULL,
    instructor_id BIGINT NOT NULL,
    assigned_at TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_course_instructors
        PRIMARY KEY
        (
            course_id,
            instructor_id
        ),

    CONSTRAINT fk_course_instructors_course
        FOREIGN KEY (course_id)
        REFERENCES courses (course_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_course_instructors_instructor
        FOREIGN KEY (instructor_id)
        REFERENCES instructors (instructor_id)
        ON DELETE RESTRICT
);

The composite primary key prevents the same instructor from being assigned to the same course twice.

One-to-one Constraints

A unique foreign key can enforce a one-to-one relationship.

CREATE TABLE user_profiles
(
    profile_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    display_name VARCHAR(200) NOT NULL,

    CONSTRAINT uq_user_profiles_user
        UNIQUE (user_id),

    CONSTRAINT fk_user_profiles_user
        FOREIGN KEY (user_id)
        REFERENCES users (user_id)
        ON DELETE CASCADE
);

The foreign key ensures that the user exists. The unique constraint ensures that each user can have at most one profile.

Tenant-aware Constraints

Multi-tenant schemas can include the tenant key in unique and foreign-key constraints to enforce tenant boundaries structurally.

CREATE TABLE tenant_customers
(
    tenant_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    customer_number VARCHAR(30) NOT NULL,

    PRIMARY KEY
    (
        tenant_id,
        customer_id
    ),

    CONSTRAINT uq_customer_number_per_tenant
        UNIQUE
        (
            tenant_id,
            customer_number
        )
);

CREATE TABLE tenant_orders
(
    tenant_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,

    PRIMARY KEY
    (
        tenant_id,
        order_id
    ),

    CONSTRAINT fk_tenant_orders_customer
        FOREIGN KEY
        (
            tenant_id,
            customer_id
        )
        REFERENCES tenant_customers
        (
            tenant_id,
            customer_id
        )
);

This prevents an order in one tenant from referencing a customer belonging to another tenant through the constrained relationship.

Case-insensitive Business Uniqueness

Text uniqueness depends on collation, case rules, whitespace, Unicode normalization, and business requirements.

Potentially equivalent email inputs:

Customer@Example.com
customer@example.com
customer@example.com followed by whitespace

If the business treats these as equivalent, normalize the value according to an approved rule and enforce uniqueness on the normalized representation.

CREATE TABLE users
(
    user_id BIGINT PRIMARY KEY,
    email_address VARCHAR(254) NOT NULL,
    normalized_email VARCHAR(254) NOT NULL,

    CONSTRAINT uq_users_normalized_email
        UNIQUE (normalized_email)
);

Email handling requirements can be domain-specific. Do not invent normalization rules without confirming the identity and communication contract.

NULL and UNIQUE Semantics

Database systems can differ in how UNIQUE constraints treat null values. Verify behaviour for the selected platform.

Business requirement:

A username is optional.

When present:
It must be unique.


Design questions:

- Can several rows contain NULL?
- Is an empty string allowed?
- Is matching case-sensitive?
- Should whitespace be normalized?
- Can a filtered or partial unique index be used?

Constraints must implement the actual business rule, not rely on an unverified assumption about database-specific null behaviour.

Cross-column Constraints

CHECK constraints can compare values within the same row.

CREATE TABLE subscriptions
(
    subscription_id BIGINT PRIMARY KEY,
    starts_on DATE NOT NULL,
    ends_on DATE NULL,
    cancellation_date DATE NULL,

    CONSTRAINT ck_subscription_period
        CHECK
        (
            ends_on IS NULL
            OR ends_on >= starts_on
        ),

    CONSTRAINT ck_cancellation_date
        CHECK
        (
            cancellation_date IS NULL
            OR cancellation_date >= starts_on
        )
);

Cross-row and cross-table rules usually require other mechanisms because a simple row CHECK normally evaluates only the row being changed.

What Constraints Cannot Express Easily

Examples of complex rules include:

• An order must always have at least one line after completion
• A manager hierarchy must contain no indirect cycles
• Reservation date ranges must not overlap
• Total payment must not exceed the order balance
• Exactly one address must be primary for each customer
• One active assignment can exist for a time range
• A shipment can be created only for an eligible order state

Depending on the database and consistency requirements, these can require:

• Transactions
• Additional unique or exclusion constraints
• Deferred constraints
• Triggers
• Stored procedures
• Application services
• Serialized operations

Application Validation vs Database Constraints

1. Application checks whether email exists.
2. Email is absent.
3. Another request performs the same check.
4. Both requests insert the email.
5. Duplicate users are created.

1. Application validates the request.
2. Database UNIQUE constraint arbitrates concurrent inserts.
3. One insert succeeds.
4. The duplicate insert is rejected.
5. Application maps the violation to a safe conflict response.

Constraints and Concurrency

A check-then-insert sequence does not safely enforce uniqueness under concurrency.

SELECT
    user_id
FROM users
WHERE
    normalized_email = :email;

Two transactions can both see no row.

Both then attempt to insert.

The UNIQUE constraint must provide
the final atomic uniqueness decision.

Adding Constraints to Existing Tables

Before adding a new constraint, inspect and correct existing violations.

Detect Duplicates

SELECT
    normalized_email,
    COUNT(*) AS duplicate_count
FROM users
GROUP BY
    normalized_email
HAVING
    COUNT(*) > 1;

Detect Orphaned Foreign Keys

SELECT
    o.order_id,
    o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
    ON c.customer_id = o.customer_id
WHERE
    c.customer_id IS NULL;

Detect Invalid Values

SELECT
    order_id,
    order_status
FROM orders
WHERE
    order_status NOT IN
    (
        'pending',
        'confirmed',
        'processing',
        'completed',
        'cancelled'
    );

Add a Constraint

ALTER TABLE users
ADD CONSTRAINT uq_users_normalized_email
UNIQUE (normalized_email);

ALTER TABLE syntax, validation timing, and online-schema-change capabilities differ between database platforms.

Naming Constraints

Meaningful constraint names improve migrations, diagnostics, monitoring, and error handling.

Follow the naming convention established for the database project rather than introducing a different convention table by table.

Complete Constrained Order Schema

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    customer_number VARCHAR(30) NOT NULL,
    display_name VARCHAR(200) NOT NULL,
    email_address VARCHAR(254) NOT NULL,
    customer_status VARCHAR(30)
        NOT NULL
        DEFAULT 'active',
    created_at TIMESTAMP
        NOT NULL
        DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_customer_number_per_tenant
        UNIQUE
        (
            tenant_id,
            customer_number
        ),

    CONSTRAINT uq_customer_email_per_tenant
        UNIQUE
        (
            tenant_id,
            email_address
        ),

    CONSTRAINT ck_customer_status
        CHECK
        (
            customer_status IN
            (
                'active',
                'inactive'
            )
        )
);

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_code VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    current_price DECIMAL(18, 2) NOT NULL,
    product_status VARCHAR(30)
        NOT NULL
        DEFAULT 'active',

    CONSTRAINT uq_products_code
        UNIQUE (product_code),

    CONSTRAINT ck_products_price
        CHECK (current_price >= 0),

    CONSTRAINT ck_products_status
        CHECK
        (
            product_status IN
            (
                'active',
                'inactive',
                'discontinued'
            )
        )
);

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    order_number VARCHAR(30) NOT NULL,
    customer_id BIGINT NOT NULL,
    order_status VARCHAR(30)
        NOT NULL
        DEFAULT 'pending',
    ordered_at TIMESTAMP
        NOT NULL
        DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,

    CONSTRAINT uq_order_number_per_tenant
        UNIQUE
        (
            tenant_id,
            order_number
        ),

    CONSTRAINT fk_orders_customer
        FOREIGN KEY
        (
            tenant_id,
            customer_id
        )
        REFERENCES customers
        (
            tenant_id,
            customer_id
        ),

    CONSTRAINT ck_orders_status
        CHECK
        (
            order_status IN
            (
                'pending',
                'confirmed',
                'processing',
                'completed',
                'cancelled'
            )
        ),

    CONSTRAINT ck_orders_total
        CHECK (total_amount >= 0)
);

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    product_description_snapshot VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(18, 2) NOT NULL,

    CONSTRAINT pk_order_lines
        PRIMARY KEY
        (
            order_id,
            line_number
        ),

    CONSTRAINT fk_order_lines_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_order_lines_product
        FOREIGN KEY (product_id)
        REFERENCES products (product_id)
        ON DELETE RESTRICT,

    CONSTRAINT ck_order_lines_number
        CHECK (line_number > 0),

    CONSTRAINT ck_order_lines_quantity
        CHECK (quantity > 0),

    CONSTRAINT ck_order_lines_price
        CHECK (unit_price >= 0)
);

This schema is illustrative. Data types, identity generation, tenant-key strategy, decimal precision, default syntax, and constraint capabilities should be adapted to the selected database platform.

Keys, Constraints, and Indexes

Constraints and indexes have related but different purposes.

A database can create an index automatically for primary or unique constraints. Foreign-key indexing behaviour varies. Index foreign-key columns when the query and modification workload justifies it, and verify the resulting execution plans.

Constraints and Transactions

Constraints are evaluated as data changes. Exact timing can be immediate or deferred depending on the database and constraint type.

Begin transaction

1. Insert parent row.
2. Insert related child rows.
3. Update aggregate state.
4. Validate applicable constraints.

Commit transaction

Multi-row business invariants should be designed together with transaction boundaries and isolation behaviour.

Handling Constraint Violations

Database errors should be translated into safe application responses rather than exposing raw SQL, table names, or internal stack traces.

{
  "type": "resource-conflict",
  "title": "The customer number already exists",
  "status": 409,
  "errors": [
    {
      "field": "customerNumber",
      "code": "duplicate"
    }
  ]
}

Constraint Observability

Useful database-integrity metrics include:

• Primary-key violation count
• Unique-constraint violation count
• Foreign-key violation count
• Check-constraint violation count
• NOT NULL violation count
• Restricted-deletion failure count
• Constraint name and affected operation
• Schema-migration validation duration
• Orphan-reconciliation result count

Logs should include safe constraint identifiers and trace information without exposing sensitive values or raw database details to clients.

Constraint-design Workflow

1. Identify each table's entity or relationship meaning.
2. List all candidate keys.
3. Select a stable primary key.
4. Preserve alternate keys with UNIQUE constraints.
5. Define required and optional columns.
6. Identify parent-child relationships.
7. Add foreign keys for referential integrity.
8. Define delete and update actions explicitly.
9. Add CHECK constraints for row-level domain rules.
10. Add meaningful defaults where omission has a valid interpretation.
11. Review tenant and ownership boundaries.
12. Test constraints under concurrent writes.
13. Map violations to safe application errors.
14. Deploy through repeatable schema migrations.

Common Key and Constraint Mistakes

Recommended Test Cases

Keys and Constraints Best Practices

Practice Exercise

Add keys and constraints to the normalized course-platform model from the previous lesson.

Requirements

1. Create stable primary keys for learners, courses, chapters, lessons, quizzes, and attempts.
2. Make learner email unique within the approved identity scope.
3. Make course code unique.
4. Require every course to reference a valid category.
5. Require every chapter to reference a valid course.
6. Require every lesson to reference a valid chapter.
7. Prevent duplicate learner-course enrollments.
8. Prevent duplicate course-instructor assignments.
9. Require completion percentage to remain from 0 through 100.
10. Require quiz scores to remain within their valid range.
11. Require submission time to be after attempt start time.
12. Define safe parent-deletion behaviour.
13. Use tenant-aware uniqueness when the platform supports several organizations.
14. Test concurrent enrollment attempts.
15. Map database violations to safe API errors.

Suggested Enrollment Table

CREATE TABLE enrollments
(
    enrollment_id BIGINT PRIMARY KEY,
    learner_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrollment_status VARCHAR(30)
        NOT NULL
        DEFAULT 'active',
    completion_percentage DECIMAL(5, 2)
        NOT NULL
        DEFAULT 0,
    enrolled_at TIMESTAMP
        NOT NULL
        DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,

    CONSTRAINT uq_enrollment_learner_course
        UNIQUE
        (
            learner_id,
            course_id
        ),

    CONSTRAINT fk_enrollments_learner
        FOREIGN KEY (learner_id)
        REFERENCES learners (learner_id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_enrollments_course
        FOREIGN KEY (course_id)
        REFERENCES courses (course_id)
        ON DELETE RESTRICT,

    CONSTRAINT ck_enrollment_status
        CHECK
        (
            enrollment_status IN
            (
                'active',
                'completed',
                'cancelled'
            )
        ),

    CONSTRAINT ck_completion_percentage
        CHECK
        (
            completion_percentage
            BETWEEN 0 AND 100
        ),

    CONSTRAINT ck_enrollment_completion
        CHECK
        (
            completed_at IS NULL
            OR completion_percentage = 100
        )
);

Constraint Inventory Template

Frequently Asked Questions