Types of Constraint in SQL: A Complete Guide for Beginners to Advanced Users

SQL constraints are rules applied to table columns that control what data can be stored in a database. They help maintain data accuracy, consistency, and integrity by preventing invalid, duplicate, or incomplete information from being entered.

Common constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT each serve a specific purpose in enforcing data quality. Together, they ensure that database records remain reliable, relationships between tables stay valid, and business rules are consistently followed.

In this blog, you will learn every type of constraint in SQL, understand what each one does, see real examples, and know exactly when and how to use them. 

Build in-demand data science skills with upGrad’s Data Science Course. Learn data analysis, machine learning, and AI through hands-on projects and real-world applications.

Types of Constraints in SQL: A Full Breakdown

Here is a quick reference table before we go into each one in detail.

Constraint	Purpose	Allows NULL
NOT NULL	Column must always have a value	No
UNIQUE	All values in a column must be different	Yes (one NULL)
PRIMARY KEY	Uniquely identifies each row	No
FOREIGN KEY	Links data between two tables	Yes
CHECK	Values must meet a condition	Yes
DEFAULT	Assigns a value when none is provided	N/A

Now let us go through each type of constraint in SQL one by one.

1. NOT NULL Constraint

The NOT NULL constraint is the most basic one. It tells the database that a column cannot be left empty. Every row must have a value for that column.

CREATE TABLE employees (
   emp_id INT NOT NULL,
   emp_name VARCHAR(100) NOT NULL,
   department VARCHAR(50)
);

In this example, emp_id and emp_name must always have a value. But department can be left blank.

When to use it: Any column that is essential for the record to make sense. A customer record without a name or an order without an amount is meaningless data.

2. UNIQUE Constraint

The UNIQUE constraint ensures that no two rows have the same value in a specific column. It prevents duplicate entries.

CREATE TABLE users (
   user_id INT PRIMARY KEY,
   email VARCHAR(255) UNIQUE,
   phone VARCHAR(15) UNIQUE
);

Both email and phone must be unique across all rows. Two users cannot share the same email address.

Important distinction: UNIQUE allows NULL values. In most databases, multiple NULL values are permitted in a UNIQUE column because NULL means "unknown," not a specific value.

When to use it: Usernames, email addresses, national ID numbers, product codes, any value that should never repeat.

Also Read: SQL DISTINCT: A Comprehensive Guide

3. PRIMARY KEY Constraint

The PRIMARY KEY constraint is a combination of NOT NULL and UNIQUE. It uniquely identifies every row in a table. No two rows can have the same primary key value, and it can never be NULL.

CREATE TABLE products (
   product_id INT PRIMARY KEY,
   product_name VARCHAR(200) NOT NULL,
   price DECIMAL(10, 2)
);

Each table can have only one primary key. But that key can consist of multiple columns — this is called a composite primary key.

CREATE TABLE order_items (
   order_id INT,
   item_id INT,
   quantity INT,
   PRIMARY KEY (order_id, item_id)
);

Here, the combination of order_id and item_id uniquely identifies each row.

When to use it: Every table should have a primary key. It is the standard way to identify and reference individual records.

4. FOREIGN KEY Constraint

The FOREIGN KEY is one of the most important types of integrity constraints in SQL. It creates a relationship between two tables. A foreign key in one table points to the primary key in another table.

CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   customer_id INT,
   order_date DATE,
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example, every customer_id in the orders table must exist in the customers table. You cannot add an order for a customer who does not exist.

Referential actions

You can define what happens when a referenced row is deleted or updated.

Action	What happens
CASCADE	Automatically deletes or updates related rows
SET NULL	Sets the foreign key to NULL
RESTRICT	Prevents deletion if related rows exist
NO ACTION	Similar to RESTRICT but checked after the statement

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE

When to use it: Any time two tables share data. Orders and customers, students and courses, employees and departments.

Also Read: What Are The Types of Keys in DBMS? Examples, Usage, and Benefits

5. CHECK Constraint

The CHECK constraint lets you define a custom condition that every value in a column must satisfy.

CREATE TABLE employees (
   emp_id INT PRIMARY KEY,
   emp_name VARCHAR(100) NOT NULL,
   age INT CHECK (age >= 18 AND age <= 65),
   salary DECIMAL(10,2) CHECK (salary > 0)
);

Here, the age must be between 18 and 65. The salary must always be a positive number. If you try to insert a row with age = 15, the database will reject it.

You can also apply CHECK at the table level when the condition involves multiple columns:

CREATE TABLE events (
   event_id INT PRIMARY KEY,
   start_date DATE,
   end_date DATE,
   CHECK (end_date >= start_date)
);

This ensures the event end date is never earlier than the start date.

When to use it: Whenever column values must fall within a specific range or pattern. Age, salary, status codes, date ranges.

6. DEFAULT Constraint

The DEFAULT constraint sets an automatic value for a column when no value is provided during insertion.

CREATE TABLE registrations (
   reg_id INT PRIMARY KEY,
   student_name VARCHAR(100) NOT NULL,
   status VARCHAR(20) DEFAULT 'active',
   registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

If you insert a row without specifying status, it will automatically be set to 'active'. If you do not provide registered_at, it gets the current date and time.

DEFAULT is not a data validation constraint like the others. It is more of a convenience feature. But it does help keep data complete and consistent.

When to use it: Status fields, timestamps, boolean flags, any column with a predictable default value.

Also Read: SQL for Data Science: Functions, Queries, and Best Practices

Types of Integrity Constraints in SQL and How They Work Together

The different types of integrity constraints in SQL are designed to work as a system, not in isolation. Here is how they connect:

1. Entity integrity is maintained by the PRIMARY KEY. Every row is uniquely identifiable.
2. Referential integrity is maintained by the FOREIGN KEY. Relationships between tables stay valid.
3. Domain integrity is maintained by NOT NULL, UNIQUE, CHECK, and DEFAULT. Individual column values follow the defined rules.

When all three are in place, your database behaves predictably. Queries return accurate results. Application bugs are easier to trace. Data migrations are safer.

Adding and dropping constraints on existing tables

You do not always define constraints at table creation. You can add them later using ALTER TABLE.

-- Add a CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);

-- Add a FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Drop a constraint
ALTER TABLE employees
DROP CONSTRAINT chk_age;

This is useful when you are modifying an existing schema or working with tables that were created without constraints.

Also Read: Detailed SQL Syllabus Structure for Data Science Certification

Common Mistakes to Avoid with SQL Constraints

Even experienced developers run into issues with constraints. Here are the ones that come up most often.

Also Read: SQL For Data Science: Why Or How To Master Sql For Data Science

Practical Example: Building a Table Structure with All Constraint Types

Here is a real-world example that uses all the different types of constraints in SQL together.

CREATE TABLE students (
   student_id    INT          PRIMARY KEY,
   full_name     VARCHAR(150) NOT NULL,
   email         VARCHAR(255) NOT NULL UNIQUE,
   age           INT          CHECK (age >= 16 AND age <= 60),
   course_id     INT,
   enrolled_on   DATE         DEFAULT CURRENT_DATE,
   status        VARCHAR(20)  DEFAULT 'active',
   FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE SET NULL
);

Let us walk through what each constraint does here:

• student_id is the PRIMARY KEY. Every student has a unique ID that cannot be NULL.
• full_name uses NOT NULL. A student record without a name makes no sense.
• email uses NOT NULL and UNIQUE together. Every student must have an email, and no two students can share one.
• age uses CHECK. Only students between 16 and 60 can be enrolled.
• course_id is a FOREIGN KEY. It links to the courses table. If a course is deleted, this field becomes NULL rather than causing an error.
• enrolled_on uses DEFAULT. If no date is provided, today's date is used automatically.
• status uses DEFAULT. New students are active by default.

This one table demonstrates how the different types of constraints in SQL protect data quality from multiple angles at once.

Also Read: Career in Data Science: Jobs, Salary, and Skills Required

Conclusion

SQL constraints are not just database rules. They are the foundation of reliable, trustworthy data. When you understand every type of constraint in SQL and apply them correctly, your database becomes far more robust, and your application code becomes cleaner because you are not writing validation logic that the database should be handling.

To summarize what you have learned:

• NOT NULL ensures a column always has a value.
• UNIQUE prevents duplicate values.
• PRIMARY KEY uniquely identifies each row.
• FOREIGN KEY enforces relationships between tables.
• CHECK validates that values meet a condition.
• DEFAULT fills in values automatically when none are provided.

The different types of constraints in SQL work best together. Use them as a system. Define them from the start of your schema design. 

Want personalized guidance in Data Science and upskilling? Speak with an expert for a free 1:1 counselling session today.