Constraints

Constraints are rules applied to columns in a table to maintain data integrity and accuracy. By using constraints, we can ensure that only valid data enters the database.

NOT NULL

The NOT NULL constraint ensures that a column cannot contain a NULL value. Every row must have a value for this column.

CREATE TABLE student (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

In the table above, the id and name columns are required, while email may be empty (NULL).

UNIQUE

The UNIQUE constraint ensures that all values in a column must be distinct from one another. No two rows may have the same value in a column with this constraint.

CREATE TABLE student (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    student_id VARCHAR(20) UNIQUE
);

If we try to insert two students with the same email, the database will return an error.

PRIMARY KEY

PRIMARY KEY is a combination of NOT NULL and UNIQUE. Each table may have only one primary key, which serves as the unique identifier for each row.

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

A primary key can consist of a single column or multiple columns (composite primary key):

-- Composite Primary Key
CREATE TABLE grades_student (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)
);

FOREIGN KEY

FOREIGN KEY creates a relationship between two tables. A foreign key column in one table references the primary key in another table.

CREATE TABLE grades (
    id INT PRIMARY KEY,
    student_id INT,
    score INT,
    FOREIGN KEY (student_id) REFERENCES student(id)
);

A foreign key ensures that the student_id value in the grades table must exist in the student table. If we try to insert a student_id that does not exist, the database will reject it.

CHECK

The CHECK constraint ensures that values in a column satisfy a specific condition.

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

With CHECK constraints, we can ensure that the price is not negative and the stock is not less than zero.

DEFAULT

The DEFAULT constraint sets a default value that will be used if no value is supplied when inserting data.

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

When we insert data without specifying a value for status, it will automatically be filled with 'Active'. Likewise, created_at will automatically contain the time at which the data is created.

AUTO_INCREMENT

AUTO_INCREMENT automatically assigns a unique value to each new row added. Very useful for ID columns.

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);

With AUTO_INCREMENT, we do not need to specify the id value manually when inserting data. The database will automatically provide the next value (1, 2, 3, and so on).

Combining Constraints

Here is an example of a table that uses various combinations of constraints:

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    amount INT NOT NULL CHECK (amount > 0),
    total_price DECIMAL(12,2) NOT NULL,
    transaction_date DATE DEFAULT (CURRENT_DATE),
    notes TEXT,
    FOREIGN KEY (product_id) REFERENCES product(id)
);

The transactions table above uses AUTO_INCREMENT, PRIMARY KEY, NOT NULL, CHECK, DEFAULT, and FOREIGN KEY in a single table definition.

Dropping Constraints

To remove a constraint from an existing column, we can use ALTER TABLE:

-- Drop the CHECK constraint on the price column
ALTER TABLE product DROP CONSTRAINT price_check;

-- Add a constraint to an existing column
ALTER TABLE product ADD CONSTRAINT chk_price CHECK (price > 0);

Constraint names can be specified explicitly or will be created automatically by MySQL.

Summary

Constraints play a crucial role in maintaining data quality and integrity in a database. By using the right combination of constraints, we can prevent invalid data from entering the system. In the next chapter, we will learn how to insert data into tables using the INSERT Query.