SQL Constraints are rules applied to columns in a table to ensure the accuracy, reliability, and integrity of the data. They enforce certain rules on the data entering a table, such as ensuring that a column cannot have NULL values or duplicate entries. SQL provides several types of constraints that help manage how data is inserted, updated, or deleted.

Types of Constraints in SQL
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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
Constraints in SQL ensure data integrity and maintain the reliability of the database. They prevent invalid or inconsistent data from being inserted, ensuring that every entry adheres to predefined rules. Each constraint type whether it’s NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, or auto-incrementing fields serves a unique purpose in database management. Understanding how and when to use these constraints is essential for database administrators and developers
Knowledge Check
Check out our Trending Courses Demo Playlist
Data Analytics with Power Bi and Fabric |
Could Data Engineer |
Data Analytics With Power Bi Fabic |
AWS Data Engineering with Snowflake |
Azure Data Engineering |
Azure & Fabric for Power bi |
Full Stack Power Bi |
Kick Start Your Career With Our Data Job
Social Media channels
► KSR Datavizon Website :- https://www.datavizon.com
► KSR Datavizon LinkedIn :- https://www.linkedin.com/company/datavizon/
► KSR Datavizon You tube :- https://www.youtube.com/c/
Most Commented