Constraints are rules applied to table columns to restrict the data that can be stored. Their purpose is to ensure data correctness, validity, and integrity within the database.

Categories of Constraints

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Guarantees that all values in a column are different.
• PRIMARY KEY: Uniquely identifies each row in a table (combination of NOT NULL and UNIQUE).
• FOREIGN KEY: Links data between two tables, maintaining referential integrity.
• CHECK: Validates that values satisfy a specific condition.
• DEFAULT: Assigns a default value to a column when no value is provided.

Constraints are defined at the column level during table creation or alteration.

Example Table with Constraints

CREATE TABLE tb_user (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique ID',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT 'Name',
    age INT CHECK (age > 0 && age <= 120) COMMENT 'Age',
    status CHAR(1) DEFAULT '1' COMMENT 'Status',
    gender CHAR(1) COMMENT 'Gender'
);

Foreign Key Constraints

A foreign key creates a connection between two tables, ensuring referential consistency. For instance, consider an emp table storing employee details including a dept_id column that references the id primary key of a dept table. Although a logical relationship exists, without an explicit foreign key constraint, the database does not enforce consistency.

Adding a Foreign Key

-- During table creation
CREATE TABLE child_table (
    column_name data_type,
    ...
    [CONSTRAINT fk_name] FOREIGN KEY (fk_column) REFERENCES parent_table(pk_column)
);

-- After table creation
ALTER TABLE child_table
ADD CONSTRAINT fk_name FOREIGN KEY (fk_column) REFERENCES parent_table(pk_column);

Example: Link emp.dept_id to dept.id.

ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

Removing a Foreign Key

ALTER TABLE child_table DROP FOREIGN KEY fk_name;

Example: Drop the fk_emp_dept_id constraint.

ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;

Deletion/Update Behavior

When a foreign key is present, actions on the parent table trigger constraints on child records. The behavior during DELETE and UPDATE operations can be defined using ON DELETE and ON UPDATE clauses.

Syntax

ALTER TABLE child_table
ADD CONSTRAINT fk_name FOREIGN KEY (fk_column)
REFERENCES parent_table(pk_column)
ON UPDATE CASCADE
ON DELETE CASCADE;

Common behaviors:

• CASCADE: Propagates the deletion/update to related rows.
• SET NULL: Sets foreign key columns to NULL in child rows.
• RESTRICT / NO ACTION: Prevents deletion or update if related rows exist.