● What Are SQL Constraints?
SQL constraints are rules applied to columns or tables in a relational database to limit the type of data that
can be inserted, updated, or deleted. These 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 row must include a value for that column.
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);
Explanation: In the above example, both the ID and NAME columns are defined with the NOT NULL constraint,
meaning every student must have an ID and NAME value.
2. UNIQUE Constraint:
The UNIQUE constraint ensures that all values in a column are distinct across all rows in a table. Unlike
the PRIMARY KEY, which requires uniqueness and does not allow NULLs, the UNIQUE constraint allows NULL
values but still enforces uniqueness for non-NULL entries.
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
Explanation: Here, the ID column must have unique values, ensuring that no two students can share the
same ID. We can have more than one UNIQUE constraint in a table.
3. PRIMARY KEY Constraint :
A PRIMARY KEY constraint is a 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
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);
Explanation: In this case, the ID column is set as the primary key, ensuring that each student’s ID is unique
and cannot be NULL.
4. FOREIGN KEY Constraint
A FOREIGN KEY constraint links a column in one table to the primary key in another table. This relationship
helps maintain referential integrity by ensuring that the value in the foreign key column matches a valid
record in the referenced table.
Orders Table:
O_ID ORDER_NO C_ID
1 2253 3
2 3325 3
3 4521 2
4 8532 1
Customers Table:
C_ID NAME ADDRESS
1 RAMESH DELHI
2 SURESH NOIDA
3 DHARMESH GURGAON
As we can see clearly that the field C_ID in Orders table is the primary key in Customers table, i.e. it uniquely
identifies each row in the Customers table. Therefore, it is a Foreign Key in Orders table.
Example:
CREATE TABLE Orders
(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
)
Explanation: In this example, the C_ID column in the Orders table is a foreign key that references
the C_ID column in the Customers table. This ensures that only valid customer IDs can be inserted into
the Orders table.
How to Specify Constraints in SQL
Constraints can be specified during the table creation process using the CREATE TABLE statement.
Additionally, constraints can be modified or added to existing tables using the ALTER TABLE statement.
Syntax for Creating Constraints:
CREATE TABLE table_name
(
column1 data_type [constraint_name],
column2 data_type [constraint_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 unique_student_id UNIQUE (ID);
Key constraints in a Database Management System (DBMS) are rules that ensure the uniqueness and
integrity of data within a table. These constraints are crucial for identifying rows and establishing
relationships between tables. Common types of key constraints include primary key, foreign key, and
unique constraints.
Types of Key Constraints:
● Primary Key: A column or combination of columns that uniquely identifies each row in a table. Primary keys must
contain unique values and cannot be NULL.
● Foreign Key: A column in one table that refers to the primary key of another table, establishing a relationship
between the two tables. Foreign keys help maintain referential integrity by ensuring that relationships between tables
are valid.
● Unique Constraint: Ensures that all values in a column are different, preventing duplicate entries. While similar to
a primary key, a table can have multiple unique constraints, whereas it can only have one primary key.
Other Related Concepts:
● Candidate Key: Any set of columns that can uniquely identify a row in a table. A primary key is chosen from
among the candidate keys.
● Composite Key: A primary key that consists of multiple columns.
● Super Key: A set of attributes that can uniquely identify a tuple in a relation.
● Alternate Key: Any candidate key that is not chosen as the primary key.
SQL Primary Key: The SQL Primary Key is a column (or combination of columns) that uniquely identifies each record in a
database table. The Primary Key also speeds up data access and is used to establish a relationship between tables.
Points to Remember
● Here are some key points of the PRIMARY KEY −
● It contains only a unique value.
● It can not be null.
● One table can have only one Primary Key.
CREATE TABLE statement:
Syntax:
Following is the syntax to define a column of a table as a primary key
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(column_name)
);
Example:
CREATE TABLE CUSTOMERS (
ID number(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
AGE number(3) NOT NULL,
ADDRESS varchar2(25),
SALARY number(18, 2),
PRIMARY KEY (ID)
);
Verification:
INSERT INTO CUSTOMERS VALUES (3, 'Kaushik', 23, 'Kota', 2000.00);
INSERT INTO CUSTOMERS VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO CUSTOMERS VALUES (NULL, 'Komal', 22, 'Hyderabad', 4500.00);
Creating Primary Key on an Existing Column:
We can also add the PRIMARY KEY constraint on an existing column of a table using the ALTER TABLE statement.
Syntax:
Following is the syntax to create a primary constraint on existing columns of a table :
ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name);
like,
ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY(NAME);
Dropping an SQL Primary Key:
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY;
like,
ALTER TABLE CUSTOMERS DROP PRIMARY KEY;
Verification:
INSERT INTO CUSTOMERS VALUES
(3, 'virat', 25, 'Mumbai', 6500.00 ),
(3, 'Hardik', 27, 'Bhopal', 8500.00 ),
(3, 'kohli', 22, 'Hyderabad', 4500.00 ),
(3, 'rohit’, 24, 'Indore', 10000.00 );
UNIQUE Constraint : The UNIQUE Constraint prevents two records from having identical values in a column. In the
CUSTOMERS table, for example, you might want to prevent two or more people from having an identical age.
Example:
For example, the following SQL query creates a new table called CUSTOMERS and adds five columns. Here, the AGE
column is set to UNIQUE, so that you cannot have two records with the same age.
CREATE TABLE CUSTOMERS(
ID number(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
AGE number(3) NOT NULL UNIQUE,
ADDRESS CHAR (25) ,
SALARY number(18, 2),
PRIMARY KEY (ID)
);
If the CUSTOMERS table has already been created, then to add a UNIQUE constraint to the AGE column. You would write
a statement like the query that is given in the code block below.
ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE;
You can also use the following syntax, which supports naming the constraint in multiple columns as well.
ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);
DROP a UNIQUE Constraint:
ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;
If you are using MySQL, then you can use the following syntax −
ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;
