SQL Constraints: Complete Guide with Examples for Beginners

What are SQL Constraints?

SQL Constraints are rules applied to database tables and columns that control what data can be inserted, updated, or deleted. These rules help maintain data integrity, improve consistency, and prevent invalid data from being stored.

Whether you’re using MySQL, SQL Server, PostgreSQL, or Oracle, constraints play a vital role in designing reliable databases. They ensure every record follows predefined rules, making applications more secure and easier to maintain.

Types of Constraints in SQL

NOT NULL Constraint

It ensures that a column cannot have any NULL values. It forces the user to always provide a value for this column.

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

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

Example: The id and name columns in the employees table must have values.

UNIQUE Constraint

Ensures that all the values in a column are unique, meaning no duplicate values are allowed.

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

CREATE TABLE employees (
id INT UNIQUE,
email VARCHAR(100) UNIQUE
);

Example: The id and email columns must have unique values for every employee in the employees table.

PRIMARY KEY Constraint

A combination of NOT NULL and UNIQUE. It uniquely identifies each record in the table and ensures that no NULL or duplicate values exist in the column.

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);

Example: The id column in the employees table is a PRIMARY KEY, ensuring each employee has a unique ID.

FOREIGN KEY Constraint

Enforces a link between two tables. The foreign key in one table references the primary key in another table, ensuring data consistency across related tables.

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
    );

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
    );

Example: The department_id column in the employees table is a FOREIGN KEY explicitly referencing the department_id in the departments table.

CHECK Constraint

The CHECK constraint ensures that all values in a column consistently satisfy a specific condition, enforcing domain integrity in the database.

Syntax (Different in MySQL and PostgreSQL):

MySQL:

CREATE TABLE employees (
id INT,
age INT CHECK (age >= 18)
);

PostgreSQL and MSSQL:

CREATE TABLE employees (
id INT,
age INT CONSTRAINT check_age CHECK (age >= 18) 
);

Example: The age column in the employees table must contain values of 18 or older.

Note: In MySQL, the CHECK constraint is recognized but not enforced in versions prior to 8.0.

DEFAULT Constraint

Provides a default value for a column when no value is supplied during the insertion of data.

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

CREATE TABLE employees (
id INT,
name VARCHAR(100),
salary DECIMAL(10, 2) DEFAULT 30000.00
);

Example: The salary column in the employees table will automatically default to 30000.00 if no salary is provided during insertion.

AUTO_INCREMENT (MySQL) / IDENTITY (MSSQL) / SERIAL (PostgreSQL)

Automatically generates a unique sequential number for a column, often used for primary keys.

Syntax:

MySQL:

CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);

MSSQL:

CREATE TABLE employees (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100)
);

PostgreSQL:

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

Example: In each case, the id column automatically increments with every new row added to the employees table.

Conclusion

SQL Constraints are fundamental to building secure, reliable, and well-structured databases. By using constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT, you can maintain data integrity and enforce business rules with minimal effort. Mastering SQL Constraints will not only improve your database design skills but also prepare you for real-world development projects and SQL interviews.

Frequently Asked Questions

What are SQL Constraints?

SQL Constraints are rules that enforce valid, accurate, and consistent data in database tables.

Why are SQL Constraints important?

They maintain data integrity, prevent invalid records, and enforce business rules.

What is the difference between PRIMARY KEY and UNIQUE?

A PRIMARY KEY uniquely identifies each row and cannot contain NULL values, while a UNIQUE constraint also prevents duplicates but typically allows a NULL value depending on the database system.

Can we add constraints after creating a table?

Yes. SQL allows constraints to be added later using the ALTER TABLE statement.