MySQL - Constraints

MySQL − Modern Data Integrity with Constraints

Understanding Constraints

In database design, constraints are rules enforced on data columns in a table. Their primary purpose is to ensure the accuracy, reliability, and integrity of the data. By restricting the type of data that can be inserted, updated, or deleted, constraints prevent invalid data from being stored in your database.

Constraints are defined when a table is created with CREATE TABLE or can be added later using the ALTER TABLE statement. Explicitly naming your constraints is a best practice for easier management.

Column-level vs. Table-level Constraints

• Column-level constraints: Applied to a single column. They are defined as part of the column definition.
• Table-level constraints: Applied to one or more columns in a table. They are defined separately after all column definitions and are necessary for multi-column constraints (e.g., a composite primary key).

Commonly used constraints and attributes in MySQL include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and AUTO_INCREMENT.

General Syntax for Creating a Table with Constraints

CREATE TABLE table_name (
    column_1_name data_type [column_constraint],
    column_2_name data_type [column_constraint],
    ...
    [CONSTRAINT constraint_name] table_constraint (column_or_columns)
);

The NOT NULL Constraint

By default, a column can hold NULL values. The NOT NULL constraint ensures that a column cannot have a NULL value. This is crucial for fields that must always contain data, such as a user’s email or a product ID.

Example

In this example, we create a Products table where product_id and product_name must not be null.

CREATE TABLE Products (
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    description TEXT
);

Attempting to insert a record with a NULL value for product_name will fail.

-- This will succeed
INSERT INTO Products(product_id, product_name, price) VALUES(1, 'Wireless Mouse', 29.99);

-- This will fail
INSERT INTO Products(product_id, product_name, price) VALUES(2, NULL, 129.99);

MySQL will return an error, protecting data integrity.

ERROR 1048 (23000): Column 'product_name' cannot be null

The UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a set of columns) are different from one another. This is ideal for fields like email addresses or usernames that must be unique but are not the primary key. A table can have multiple UNIQUE constraints.

Example

Here, we ensure that every user has a unique email.

CREATE TABLE Users (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    -- Naming the constraint is a good practice
    CONSTRAINT uq_email UNIQUE (email)
);

Let’s attempt to insert two users with the same email address.

-- This will succeed
INSERT INTO Users(user_id, username, email) VALUES(101, 'john_doe', 'john.doe@example.com');

-- This will fail
INSERT INTO Users(user_id, username, email) VALUES(102, 'johnny_d', 'john.doe@example.com');

The second insert fails because the email is not unique.

ERROR 1062 (23000): Duplicate entry 'john.doe@example.com' for key 'users.uq_email'

The PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. A primary key must contain UNIQUE values and cannot contain NULL values. It’s a combination of NOT NULL and UNIQUE. Each table can have only one primary key, which can consist of single or multiple columns (a composite key).

Example

The user_id column is the primary key for the Users table.

CREATE TABLE Users (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    PRIMARY KEY(user_id)
);

Any attempt to insert a duplicate or NULL user_id will be rejected.

-- This will succeed
INSERT INTO Users(user_id, username, email) VALUES(1, 'jane_doe', 'jane.doe@example.com');

-- This will fail (Duplicate entry)
INSERT INTO Users(user_id, username, email) VALUES(1, 'jane_d', 'jane.d@example.com');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'

-- This will also fail (NULL value)
INSERT INTO Users(user_id, username, email) VALUES(NULL, 'john_smith', 'j.smith@example.com');
-- ERROR 1048 (23000): Column 'user_id' cannot be null

The FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together. It is a field (or collection of fields) in one table, called the child table, that refers to the PRIMARY KEY in another table, the parent table. This establishes referential integrity, preventing actions that would leave orphaned records.

You can also specify actions for ON DELETE and ON UPDATE events, such as CASCADE, SET NULL, or RESTRICT.

Example

First, we create the parent table Customers.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

Next, we create the child table Orders, which links to Customers.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    ON DELETE SET NULL -- If a customer is deleted, set the customer_id in Orders to NULL
    ON UPDATE CASCADE -- If a customer_id is updated, update it in Orders too
);

The CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column, it will allow only certain values for this column. Note: MySQL started enforcing CHECK constraints in version 8.0.16.

Example

This Products table ensures that price is a positive number and stock_quantity is not negative.

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    stock_quantity INT,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

Attempting to insert a product with a price of 0 will fail.

-- This will succeed
INSERT INTO Products VALUES(1, 'Laptop', 999.99, 50);

-- This will fail
INSERT INTO Products VALUES(2, 'Sticker', 0.00, 1000);
-- ERROR 3819 (HY000): Check constraint 'chk_price' is violated.

The DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during an INSERT operation.

Example

In this Orders table, the status column will default to ‘Pending’ if not provided.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending'
);

Insert a new order without specifying the status.

INSERT INTO Orders(order_id, order_date) VALUES(1001, '2023-10-27');

-- Now, let's view the result
SELECT * FROM Orders WHERE order_id = 1001;

The output shows ‘Pending’ was automatically applied.

order_id	order_date	status
1001	2023-10-27	Pending

The AUTO_INCREMENT Attribute

AUTO_INCREMENT is not a constraint but a column attribute that allows a unique number to be generated automatically when a new record is inserted into a table. It’s most often used for the primary key column. By default, the starting value is 1, and it increments by 1 for each new record.

Example

The employee_id will be generated automatically.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

We insert new employees without specifying the employee_id.

INSERT INTO Employees(first_name, last_name) VALUES('Maria', 'Jones');
INSERT INTO Employees(first_name, last_name) VALUES('David', 'Smith');

Let’s see the generated IDs.

SELECT * FROM Employees;

employee_id	first_name	last_name
1	Maria	Jones
2	David	Smith

Managing Indexes (Performance Tuning)

While not a data integrity constraint, INDEX is a crucial part of database schema design. Indexes are used to retrieve data from the database more quickly. PRIMARY KEY and UNIQUE constraints automatically create indexes behind the scenes. You can also create indexes manually on other columns that are frequently used in WHERE clauses to speed up queries.

Example

If you often search for employees by their last name, creating an index on the last_name column is a good idea.

CREATE INDEX idx_lastname ON Employees(last_name);

This index is not visible to users but will be used by the MySQL query optimizer to accelerate lookups based on last_name.