Constraints

Constraints are rules applied to table columns to enforce data integrity and consistency. They ensure that the data stored in a table adheres to certain rules.

Primary Key

A Primary Key is a column (or set of columns) that uniquely identifies each row in a table.

• It must be unique (no duplicate values).
• It cannot contain NULL values.
• A table can have only one primary key, but it can consist of multiple columns (called a composite primary key).

Why use it?

• Ensures that every record is uniquely identifiable.
• Helps MySQL internally organize data efficiently.

By default, a primary key in MySQL is treated as both UNIQUE and NOT NULL.

Colmn Level vs Table Level

Syntax	Level	Supports Composite Key?	Notes
column_name DATA_TYPE PRIMARY KEY	Column-level	No	Simple and convenient for single-column keys
PRIMARY KEY (column_name[, ...])	Table-level	Yes	Required for multi-column keys; more flexible

Example:

CREATE TABLE students (
    student_id INT NOT NULL,
    name VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT pk_student_id PRIMARY KEY (student_id)
);

• id is the primary key.
• Each id must be unique and cannot be null.
• Constraint name pk_student_id allows easy reference if we need to modify or drop it later.

Foreign Key

A Foreign Key is a column (or combination of columns) that establishes a relationship between two tables.

• References the primary key (or unique key) of another table.
• Ensures referential integrity — you cannot insert a value in the child table if it doesn’t exist in the parent table.
• You can define what happens to child rows when the parent row is updated or deleted using:
  • CASCADE: Automatically updates/deletes child rows when the parent changes.
  • SET NULL: Sets the foreign key in child rows to NULL when the parent is deleted/updated.
  • RESTRICT: Prevents deletion or update of the parent row if child rows exist.

Why use a Foreign Key?

• Maintains consistency between related tables
• Prevents orphan records

Single-Column Example

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id CHAR(10),
    course_name VARCHAR(100) NOT NULL,
    CONSTRAINT fk_student FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

• student_id in enrollments references student_id in students.
• ON DELETE CASCADE ensures that deleting a student also deletes their enrollments.

Composite (Multi-Column) Foreign Key Example

Suppose we have a courses table with course_id and semester as a composite primary key:

CREATE TABLE courses (
    course_id INT,
    semester VARCHAR(10),
    course_name VARCHAR(100),
    CONSTRAINT pk_course PRIMARY KEY (course_id, semester)
);

CREATE TABLE enrollments (
    student_id CHAR(10),
    course_id INT,
    semester VARCHAR(10),
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id, semester),
    CONSTRAINT fk_course FOREIGN KEY (course_id, semester)
        REFERENCES courses(course_id, semester)
        ON DELETE SET NULL
        ON UPDATE RESTRICT
);

• Here, (course_id, semester) is a composite foreign key in enrollments referencing courses.
• pk_course is the primary key constraint on (course_id, semester) in courses.
• pk_enrollment is a composite primary key in enrollments on (student_id, course_id, semester).
• Naming the constraint (CONSTRAINT pk_name) allows easier reference when modifying or dropping the key later.
• ON DELETE SET NULL: If a course is deleted, the corresponding course_id and semester in enrollments are set to NULL.
• ON UPDATE RESTRICT: Prevents updating the course’s course_id or semester if there are related enrollments.

NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value. It enforces that every row in the table must have a value for that column.

Why use it?

• Use Mandatory fields like id, name, email in a table.

General Syntax

CREATE TABLE table_name (
    column_name datatype NOT NULL
);

UNIQUE

The UNIQUE constraint ensures that all values in a column (or a set of columns) are unique. Unlike the primary key, a table can have multiple UNIQUE constraints, and they allow NULLs (depending on DBMS).

Why use it?

• Fields that must not repeat, like email or username.

General Syntax

CREATE TABLE table_name (
    column_name datatype UNIQUE
);

• Multiple UNIQUE constraints can exist in a table.
• Composite Unique: UNIQUE(column_1, column_2)

CHECK

The CHECK constraint ensures that values in a column satisfy a specific condition. This helps enforce business rules at the database level.

Why use it?

• To validate data before it’s inserted or updated.

General Syntax

CREATE TABLE table_name (
    column_name datatype CHECK (condition)
);

Column-Level CHECK

age INT CHECK (age >= 18),

Table-Level CHECK

CONSTRAINT chk_cgpa_age CHECK (cgpa >= 3.5 AND age >= 18)

• CHECK can be column-level (applies to a single column) or table-level (involving multiple columns).
• Helps maintain data integrity by preventing invalid entries.