SQL constraints are rules that a database uses to control the values stored in a table. They are defined when a table is created or modified and help the database reject data that does not satisfy an important rule.
For example, a student table may require every student to have a unique student ID, while the student's name may be mandatory. An employee table may require the salary to be greater than zero and the department ID to refer to an existing department. These rules can be enforced directly by using SQL constraints.
Constraints are important because data validation should not depend only on the application that sends the data. When the rule is defined in the database, the same rule can be enforced even when data is inserted or updated through a different application, script, or database client.
A constraint is a restriction placed on a column or a group of columns in a database table. When an INSERT or UPDATE operation violates the defined rule, the database can reject the operation.
For example, consider the following table definition:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE
);
Here, three different rules are being enforced. StudentID must uniquely identify a row, StudentName must contain a value, and duplicate values in Email are not permitted according to the database's UNIQUE constraint rules.
A database often stores information that is used by many parts of an application. If important rules are not enforced at the database level, inconsistent or invalid records can gradually enter the system.
For example, suppose an application stores the department ID of an employee. Without a foreign key, an employee record could contain a department ID that does not exist in the department table. A foreign key can prevent that type of invalid relationship.
The main purposes of constraints are:
The commonly used SQL constraints covered in this tutorial are:
| Constraint | What it does | Typical use |
|---|---|---|
| NOT NULL | Requires a value to be supplied for a column | Student name, employee name |
| UNIQUE | Prevents duplicate values according to the database's uniqueness rules | Email, username, registration number |
| PRIMARY KEY | Uniquely identifies each row | Student ID, Employee ID |
| FOREIGN KEY | Maintains a valid relationship between tables | Employee department ID |
| CHECK | Requires a value to satisfy a condition | Age, quantity, marks |
| DEFAULT | Provides a value when one is not supplied | Status, creation state |
The NOT NULL constraint requires a column to contain a value. It is useful when a field is essential for understanding or processing a record.
ColumnName DataType NOT NULL
CREATE TABLE Student (
StudentID INT,
StudentName VARCHAR(100) NOT NULL
);
In this table, StudentName cannot be omitted by an INSERT operation. The database will reject an attempt to store a row without a value for this column.
INSERT INTO Student (StudentID, StudentName) VALUES (101, 'Rahul');
The above statement supplies a value for StudentName and therefore satisfies the NOT NULL rule.
NOT NULL is particularly useful for fields such as names, registration numbers, dates, or other information that the application considers mandatory.
The UNIQUE constraint is used when values in a column should not be duplicated. A common example is an email address or username.
ColumnName DataType UNIQUE
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Email VARCHAR(150) UNIQUE
);
If one student already has the email address student@example.com, another row cannot normally use the same value in the Email column.
INSERT INTO Student (StudentID, StudentName, Email) VALUES (101, 'Rahul', 'student@example.com');
A later INSERT using the same email value can fail because it violates the UNIQUE constraint.
The exact treatment of NULL values in a UNIQUE column can differ between database systems, so developers should check the documentation of the particular DBMS being used.
A primary key is used to identify each row in a table. A primary key must uniquely identify records and cannot contain NULL values.
ColumnName DataType PRIMARY KEY
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL
);
Here, StudentID identifies each student. Two rows cannot have the same StudentID, and StudentID cannot be NULL.
Suppose a college has two students with the same name. Searching by StudentName alone may not uniquely identify a person. A StudentID provides a stable identifier that can distinguish one student record from another.
A primary key can also consist of more than one column. This is called a composite primary key. It is useful when the combination of two or more values uniquely identifies a row.
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID)
);
In this example, the combination of StudentID and CourseID identifies an enrollment record.
A foreign key connects a column in one table with a key in another table. It is commonly used to maintain relationships between related records.
Consider a database containing departments and employees. Each employee may belong to a department.
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100) NOT NULL,
DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);
DepartmentID in Employee is a foreign key that refers to DepartmentID in the Department table.
This relationship helps prevent an employee from referencing a department that does not exist, subject to the database system's foreign key rules.
INSERT INTO Department (DepartmentID, DepartmentName) VALUES (10, 'Computer Science'); INSERT INTO Employee (EmployeeID, EmployeeName, DepartmentID) VALUES (501, 'Amit', 10);
The Employee record refers to the department whose DepartmentID is 10.
When a referenced parent record is updated or deleted, the database can be configured to control what happens to related child records. Depending on the DBMS and the table definition, options can include actions such as CASCADE, SET NULL, or RESTRICT/NO ACTION.
These options should be selected carefully because deleting or changing a parent record can affect many related records.
The CHECK constraint requires a value to satisfy a specified condition. It is useful when the database should reject values outside an acceptable range or set of rules.
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Age INT CHECK (Age >= 18)
);
The condition requires Age to be at least 18.
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Quantity INT CHECK (Quantity >= 0)
);
This rule prevents the database from accepting a negative quantity when the DBMS supports and enforces the CHECK constraint as defined.
CHECK constraints are useful for simple validation rules involving values such as age, quantity, marks, status codes, or numeric ranges.
The DEFAULT constraint provides a value when an INSERT statement does not supply a value for that column.
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100) NOT NULL,
Status VARCHAR(20) DEFAULT 'Active'
);
If Status is omitted during insertion, the database can use the default value Active.
INSERT INTO Employee (EmployeeID, EmployeeName) VALUES (501, 'Amit');
Because Status was not supplied, the DEFAULT rule provides the value defined for that column.
DEFAULT values are useful for fields that commonly begin with the same value, such as a record status or a creation-related value.
A practical table normally uses several constraints together. Each constraint solves a different data-quality problem.
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE,
Age INT CHECK (Age >= 18),
Course VARCHAR(50) DEFAULT 'B.Tech'
);
In this example:
Using several constraints together is often more useful than relying on a single rule because different columns may have different requirements.
Constraints can be written directly beside a column definition or separately at the table level. The table-level approach is particularly useful for constraints involving multiple columns.
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL
);
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID)
);
The second example defines a composite primary key involving two columns. Writing the constraint at the table level makes the relationship between the columns clear.
| Feature | PRIMARY KEY | UNIQUE |
|---|---|---|
| Main purpose | Uniquely identifies each row | Prevents duplicate values according to DBMS rules |
| NULL | Not allowed | NULL handling depends on the database system |
| Number in a table | One primary key constraint | Multiple UNIQUE constraints can be defined |
| Composite definition | Can contain multiple columns | Can also be defined across multiple columns |
| Feature | PRIMARY KEY | FOREIGN KEY |
|---|---|---|
| Purpose | Identifies rows in its own table | References a key in another table |
| Duplicate values | Not allowed | May be allowed |
| NULL | Not allowed | May be allowed depending on column definition |
| Relationship | Provides an identity for a table's rows | Helps establish relationships between tables |
Constraints are most useful when they represent real rules of the data. Problems can occur when constraints are added without understanding the data model.
Constraints can be given explicit names. Naming constraints can make database maintenance and error messages easier to understand, especially in larger projects.
CREATE TABLE Student (
StudentID INT,
StudentName VARCHAR(100) NOT NULL,
CONSTRAINT pk_student
PRIMARY KEY (StudentID)
);
Here, pk_student is the name assigned to the primary key constraint.
| Constraint | Key Idea |
|---|---|
| NOT NULL | A value is required. |
| UNIQUE | Duplicate values are restricted. |
| PRIMARY KEY | Uniquely identifies each row. |
| FOREIGN KEY | Maintains a relationship with another table. |
| CHECK | Requires a value to satisfy a condition. |
| DEFAULT | Provides a value when one is not supplied. |
SQL constraints enforce rules on data stored in tables. They help the database prevent invalid, duplicate, incomplete, or incorrectly related data.
Yes. A table can have multiple UNIQUE constraints when different columns or combinations of columns need uniqueness.
A table has only one primary key constraint, but that primary key can contain multiple columns and is then called a composite primary key.
A primary key identifies rows in its own table. A foreign key stores values that reference a key in another table and helps maintain the relationship between the two tables.
Defining important rules in the database provides a central layer of data validation. This is useful when the same database is accessed by multiple applications or users.
SQL constraints are an important part of database design because they allow a database to enforce rules about the data it stores. Instead of depending entirely on application code, developers can define important requirements directly in the table structure.
NOT NULL is used for required values, UNIQUE helps control duplicate values, PRIMARY KEY identifies rows, FOREIGN KEY maintains relationships, CHECK validates conditions, and DEFAULT supplies a value when one is not provided.
A well-designed database does not use constraints simply to make a table more complex. Each constraint should represent a meaningful rule of the data model. Understanding this principle helps developers create databases that are easier to maintain and less likely to contain inconsistent information.