SQL Constraints Explained : Types, Syntax & Real-World Use Cases
In SQL, constraints are rules applied to table column(s) or entire tables to enforce data integrity and reliability. Constraints are essential for defining how data should be stored and retrieved. Constraints help prevent invalid data entry and maintain the consistency and accuracy of the data.
In this article, we will learn about constraints , it's type , use cases and so on. By the end, you will have a clear understanding about constraints , it's use and importance , syntax and many more things in depth
Syntax
1. Adding Constraints at the time of table creationCREATE TABLE TableName( column1 datatype constraint, column2 datatype constraint, .... columnN datatype constraint );
ALTER TABLE TableName ADD CONSTRAINT ConstraintName ConstraintDefinition
We will use this table to demonstrate and explain the concepts within this article
CREATE TABLE Employees (
Id INT,
Name VARCHAR(100),
Mobile VARCHAR(15),
Address varchar(300),
Salary DECIMAL(10, 2)
);Types of Constraints in SQL
1. NOT NULL Constraint
It is helpful to ensure that a column cannot have a NULL value.
CREATE TABLE TableName(
.................................
ColumnX datatype NOT NULL,
................................
);
OR
ALTER TABLE TableName
MODIFY ColumnName datatype NOT NULL;
Let's consider a scenario, where we need to add not null constraints on column Name in the Employees table.
ALTER TABLE Employees MODIFY Name varchar(150) NOT NULL;
Verification
To verify is constraints added in column or not, run desc query
desc Employees;
Field Type Null Key Default Extra Id int YES Name varchar(150) NO Mobile varchar(15) YES Address varchar(300) YES Salary decimal(10,2) YES
you can see null column against Name is "No" means null is not allowed in Name.
When you try to insert null value in Name column. you will get an error
INSERT INTO Employees VALUES (1, null,7654345654,"P.K.C. Nagar", 89000.0);
11:43:30 INSERT INTO Employees VALUES (1, null,7654345654,"P.K.C. Nagar", 89000.0) Error Code: 1048. Column 'Name' cannot be null 0.000 sec
2. UNIQUE Constraint
It is used to ensure all values in a column are unique, preventing duplicate entries.
CREATE TABLE TableName(
.................................
ColumnX datatype UNIQUE [KEY],
................................
);
OR
ALTER TABLE TableName
ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (column(s));
Let's consider a scenario, where we need to add unique constraints on column Mobile number in Employees table.
ALTER TABLE Employees ADD CONSTRAINT mobile_unique UNIQUE (Mobile);
Verification : To verify is unique constraints added or not, run desc query on table
desc Employees;
Field Type Null Key Default Extra Id int YES Name varchar(150) NO Mobile varchar(15) YES UNI Address varchar(300) YES Salary decimal(10,2) YES
You can see on Mobile column Unique constraints is added. Learn Unique Constraints in depth
3. PRIMARY KEY Constraint
Primary Key is used to uniquely identifies each record in a table. Generally Primary Key is the combination of NOT NULL and UNIQUE constraints.
CREATE TABLE TableName(
column1 datatype,
column2 datatype,
column3 datatype,
.....................
PRIMARY KEY(column(s))
);
OR
ALTER TABLE TableName
ADD CONSTRAINT PRIMARY KEY (column(s));If you would like to dive deeper into the concept of primary key, their syntax, use cases and practical examples, check out our detailed guide here Understanding Primary Key in SQL.
4. FOREIGN KEY Constraint
Foreign Key helps maintain data consistency between related tables by enforcing referential integrity.
CREATE TABLE TableName (
Column1 DataType,
Column2 DataType,
CONSTRAINT FK_Name FOREIGN KEY (Column1 ) REFERENCES ParentTableName(ParentColumnName)
);Adding Foreign Key constraints on an existing column syntax
ALTER TABLE TableName ADD CONSTRAINT ConstraintName FOREIGN KEY (ColumnName) REFERENCES ParentTableName(ParentColumnName);
Confused about how foreign keys works? Don’t worry! Our in-depth guide covers everything you need to clarify your doubts Read About Foreign Keys
5. CHECK Constraint
Check Constraints ensures that only data meeting certain conditions can be entered into the database.
Column-Level CHECK Constraint
Constraint is directly applied to a single column within the CREATE TABLE statement.
CREATE TABLE TableName(
column1 datatype CHECK (condition),
column2 datatype,
...
);CREATE TABLE Employees (
Id INT PRIMARY KEY,
Name VARCHAR(100),
Mobile VARCHAR(15),
Address varchar(300),
Salary DECIMAL(10, 2) CHECK (SALARY > 0)
);Table-Level CHECK Constraint
Constraint is defined as part of the table definition and can apply to multiple columns.
CREATE TABLE TableName (
column1 datatype,
column2 datatype,
...
CHECK (condition(s))
);CREATE TABLE Employees (
Id INT PRIMARY KEY,
Name VARCHAR(100),
Mobile VARCHAR(15),
Address varchar(300),
Salary DECIMAL(10, 2),
CHECK (SALARY > 0)
);Adding a CHECK Constraint to an Existing Table
If you need to add CHECK constraints on an existing table. you can do this with the help of ALTER TABLE statement.
ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK (condition);
ALTER TABLE Employees ADD CONSTRAINT ck_salary CHECK (Salary > 0);
If you would like to dive deeper into the ALTER TABLE statement, their syntax, use cases and practical examples, check out our detailed article Understanding ALTER TABLE in SQL
6. DEFAULT Constraint
DEFAULT Constraints is useful to sets a default value for a column if no value is provided.
Adding DEFAULT Constraints at the time of table creation
CREATE TABLE TableName(
....................................
columnX datatype DEFAULT default_value,
...................................
);Adding DEFAULT Constraints on an existing column
ALTER TABLE TableName ALTER COLUMN ColumnName SET DEFAULT default_value;
Adding DEFAULT Constraints on Name column. if name is not provided set default name as "UnKnown"
CREATE TABLE Employees (
Id INT,
Name VARCHAR(100) default "UnKnown",
Mobile VARCHAR(15),
Address varchar(300),
Salary DECIMAL(10, 2)
);Verification : To verify what default constraints run describe query on table and check default column against with column name.
Desc Employees;
Field Type Null Key Default Extra Id int YES Name varchar(100) YES UnKnown Mobile varchar(15) YES Address varchar(300) YES Salary decimal(10,2) YES
Removing DEFAULT Constraint
If you need to remove the DEFAULT constraint, use the ALTER TABLE statement.
ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT;
7. INDEX Constraint
INDEX constraints is used to improve performance. it Improves the speed of data retrieval.
CREATE INDEX idx_name ON TableName (column(s));
Dropping or Deleting a Constraint
Dropping a constraints syntax varies across different RDBMS, below is the syntax of some popular RDBMS syntax
1. In MySQL
In MySQL, you need to first identify the name of the constraint (e.g., primary key, foreign key, unique, etc.) after that based on constraint you can follow below syntax to delete constraints
ALTER TABLE TableName DROP PRIMARY KEY;
ALTER TABLE TableName DROP FOREIGN KEY ConstraintName;
ALTER TABLE TableName DROP INDEX ConstraintName;
2. In PostgreSQL
In PostgreSQL, you can drop constraints by referencing the constraint name, below is the query syntax
ALTER TABLE TableName DROP CONSTRAINT ConstraintName;
Same query syntax will be followed for all type of constraints like Primary Key , Foreign Key , Unique Key etc
3. In SQL Server
In SQL Server, the syntax is similar to that of PostgreSQL
ALTER TABLE TableName DROP CONSTRAINT ConstraintName;
Same query syntax will be followed for all type of constraints like Primary Key , Foreign Key , Unique Key etc
4. In Oracle
In Oracle, the syntax for dropping constraints is also similar to SQL Server and PostgreSQL.
ALTER TABLE TableName DROP CONSTRAINT ConstraintName;
Same query syntax will be followed for all type of constraints deletion like Primary Key , Foreign Key , Unique Key etc
Key Points
- Constraints are validated when data is inserted or updated to ensure that it meets the required conditions.
- Constraints apply at the column or table level.
- Constraints ensure the accuracy and reliability of data.
- Constraints are enforced by the database engine, reducing the need for manual validation.
- Test all constraints in a development environment before deploying to production.
- Constraints like FOREIGN KEY or CHECK may slightly impact performance during data insertion or updates due to validation.
- Constraints like UNIQUE and PRIMARY KEY automatically create indexes, which improve query performance but may slow down insert operations.
Frequently Asked Questions (FAQs)
1. What are constraints in SQL and why are they important?
Constraints in SQL are rules applied to table columns or entire tables to control what kind of data can be stored. They prevent invalid, duplicate or inconsistent data from entering the database. By enforcing constraints at the database level, you ensure data accuracy, reliability and long-term consistency without relying solely on application-level checks.
2. Are SQL constraints enforced automatically by the database?
Yes. Constraints are enforced automatically by the database engine whenever data is inserted, updated or modified. If a constraint rule is violated, the database rejects the operation. This automatic enforcement ensures that data integrity is maintained consistently, even if data is modified from different applications or users.
3. What is the difference between column-level and table level constraints?
Column-level constraints apply to a single column and are defined directly with that column. Table-level constraints are defined separately and can involve multiple columns. For example, a CHECK constraint comparing two columns must be defined at the table level. Both types help enforce data rules but serve different use cases.
4. Why is the NOT NULL constraint commonly used?
The NOT NULL constraint ensures that a column must always contain a value. It is commonly used for mandatory fields like names, identifiers or dates. By enforcing NOT NULL at the database level, you prevent incomplete records and reduce the risk of unexpected NULL related errors in queries and applications.
5. How does the UNIQUE constraint help maintain data quality?
The UNIQUE constraint ensures that all values in a column (or set of columns) are different. This is essential for fields like email addresses or mobile numbers where duplicates are not allowed. UNIQUE constraints prevent data duplication and automatically create indexes to support fast lookups.
6. What makes a PRIMARY KEY different from a UNIQUE constraint?
A PRIMARY KEY uniquely identifies each record in a table and does not allow NULL values. While UNIQUE constraints also enforce uniqueness, they allow NULLs (depending on the database). Every table should have a primary key to ensure reliable row identification and efficient indexing.
7. Why are FOREIGN KEY constraints critical in relational databases?
Foreign keys enforce relationships between tables by ensuring that referenced data exists in the parent table. They prevent orphan records and maintain referential integrity. Without foreign keys, databases can easily end up with inconsistent or invalid relationships between related tables.
8. Can FOREIGN KEY constraints impact performance?
Yes, slightly. Foreign key constraints add validation checks during INSERT, UPDATE and DELETE operations. While this adds some overhead, the performance cost is usually small compared to the benefits of maintaining consistent and reliable data across related tables.
9. What is the role of the CHECK constraint?
CHECK constraints ensure that column values satisfy specific conditions, such as salary being greater than zero or age being within a valid range. They help enforce business rules directly in the database, reducing reliance on application logic and preventing invalid data from being stored.
10. How does the DEFAULT constraint simplify data insertion?
DEFAULT constraints automatically assign a predefined value to a column when no value is provided. This is useful for fields like status, timestamps or placeholder values. DEFAULT constraints help reduce errors, simplify insert queries and ensure consistent data values.
11. Are INDEXes considered constraints in SQL?
Indexes are often discussed alongside constraints, but they serve a different purpose. Indexes improve query performance by speeding up data retrieval. However, UNIQUE and PRIMARY KEY constraints automatically create indexes behind the scenes, combining data integrity with performance benefits.
12. Can constraints be added after a table is created?
Yes. SQL allows you to add constraints later using the ALTER TABLE statement. This is common when improving schema design or adding validation rules to existing tables. However, existing data must satisfy the new constraint or the operation will fail.
13. What happens when existing data violates a new constraint?
If existing data violates a constraint you are trying to add, the database will reject the operation. You must clean or update the data first. This protects the database from entering an inconsistent state and forces data issues to be resolved properly.
14. Is it safe to drop constraints in production databases?
Dropping constraints can be risky because it removes data validation rules. In production environments, constraints should only be dropped after careful analysis, testing and confirmation that application logic can handle validation safely. Backups are strongly recommended before making such changes.
15. Are SQL constraints commonly asked in interviews?
Yes. Constraints are a core SQL topic and frequently asked in interviews. Interviewers test understanding of data integrity, relationships and real-world schema design. Questions often focus on differences between PRIMARY KEY and UNIQUE, FOREIGN KEY behavior and performance implications.