SQL|Constraints
Last Updated:30 Jan, 2025
database design
that
SQL constraints are essential elements
elements in relational stored in adatabase.
reliability of the data
sure trne integrity, accuracy. and SQL constraints help
maintain
By enforcing specific rules on table columns,
entries and optim1zing quely
ala consistency, preventing invalid data
performance.
We will explain the most common SQL constraints in detall,
s dricie,
providing clear examples and explaining how to implement them effectively.
What Are SQL Constraints? tables in a relational
DL Constraints are rules applied to columns or
database to limit the type of data that can be inserted, updated, or deleted.
nese rules ensure the data is valid, consistent. and adheres to the business
logic or database requirements. Constraints can be enforced during table
Creation or later using the ALTER TABLE statement. They play a vital role in
maintaining the quality and integrity of your database.
Types of SQL Constraints
SQL provides several types of constraints to manage different aspects of data
integrity. These constraints are essential for ensuring that data meets the
requirements of accuracy, consistency, and validity. Let's go through each
of them with detailed explanations and examples.
1. NOT NULL Constraint
The NOT NULL Constraint ensures that a column cannot contain NULL values.
This is_particularly important for columns where a value is essential for
identifying records or performing calculations. If a column is defined as NOT
NULL,every rowW must include a value for that column.
Example:
CREATE TABLE Student
TD int6) NOT NULL
NAME Vanchar(10) NOT NULL
ADDRESS yarchnae(20)
Explanation: In the above example, both the D and AME Columns are defined
with the NOT NULL constraint, meaning aning every student must have
an Tp and NAME value.
2. UNIQUE Constraint
O ID ORDER NO CID
4521
8532 1
Customers Table:
CID NAME ADDRESS
RAMESH DELHI
2 SURESH NOIDA
3 DHARMESH GURGAON
Aswe can see clearly that the field CID in Orders table is the primary key ir
Customers table, i.e. it uniquely identifies each row in the Customers table
Therefore, it is a Foreign Key in Orders table.
Example:
CREATETABLE Orders
OID int NOT NULLS
ORDER NO int NOT NULL
C ID 1nt
PRIMARY KEY (0 TD),
FORETGN KEY (C TD) REFERENGES CUStomers(C ID)
Explanation: In this example, the c zD Column in the zders table is aforeigr
key that references the c ID Column in the cust omers table. This ensures tha
only valid customer iDs can be inserted into the oraers table.
5. CHECK Constraint
The CHECK constraint allows us to specifya condition that data must satisfy
before it is inserted into the table. This can be used to enforce rules, such as
ensuring that a column'svalue meets certain criteria (e.g., age must be greate
than 18)
Example:
CREATE TABLE STUdent
1O int6) NOT MULL
AGE int NOT NULL CHECK (AGE
18)
Explanation: In the above table, the CHECK Constraint ensures that only
Students aged 18 [Link] can be inserted into the
6. DEFAULT Constraint table.
e DEFAULT Constraint providesadefault value for acolumn when no
S Specified during insertion. This ís useful for ensuring that certain value
always have a meaninaful value. even if the user does not columns
Example: provide one
CREATE TABLE Student
ID int(6) NOT NULLy
NAME Varchar(18) NOT
AGE int DEFAULT 18 NULL
Explanation: Here. if no value is provided for AGE during an insert, the
value of 18 will be assigned automatically. default
How to Specify Constraints in SQL
Constraints can be specified during the table creation process Using
the caEe CaETE statement. Additionally. constraints can
be modified or
added to existing tables using the ALTER TAELE
Syntax for Creating Constraints: Statement.
CREATE TABLEtable name
column1 data type (constraint name].
column2 data type fconstraint name],
column3 data type (constraint name),
We can also add or remove constraints after a table is created:
Example to Add a Constraint:
ALTER TABLE Student
ADD CONSTRAINT Uniauestudert idUNOUE (TD)
Conclusion
SQL constraints are essential for maintaining data integrity and ensuring
lconsistency in relatíonal databases. Understanding and implementing these
constraints effectively will help in designing robust, error-free databases. By
leveraging NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.
DEFAULT, and INDEX, you can ensure your database is optimized
Iracv and performance.
The UNIQUE constraint columnare distinct across
ensures that
ensures al values in a
a lable. Unlike the PRIMARY KEY which requires uniqueness and
REo alow NULLS, the UNIOUF Constraint allows NULL values but stll
enforces uniqueness for non-NULL entries.
Example:
CREATE TABLE Studert
D int(6) NOT NULL UNIOUE,
NAME Varchar (1O),
ADDRESS varchar(20)
Explanation: Here, the 1b column must have unique values,ensuring that no
two students can share the same ID. Ne can have more than
one UNIQUE Constraint in a table.
3. PRIMARY KEY Constraint
A PRIMARY KEY constraint combination of the NOT
NULL and UNIQUE Constraints. It uniquely identifies each row in a table. A
table can only have one PRIMARY KEY and it cannot accept NULL values
This is typically used for the column that will serve as the identifier of records.
Example:
CREATE TABLE Student
IO int6) NOT NULL UNIOUES
NAME Varchar10)
ADDRESS varchar(20)
PRIMARY KEY(ID)
Explanation: In this case, the t Column is set as the primary key,
that each student's ID is unique and cannot be NULL. ensuring
4. FOREIGN KEY Constraint
AFOREIGN KEY Constraint links a columnin one table to the
another table. This relationship helps maintain referential primary key i
that the value in the foreign key integrity by ensurin
column matches a valid reCord in the reference
table.
Orders Table:
0 ID ORDER NO C ID
2253
2 3325 3
