Understanding SQL constraints is crucial for database developers and administrators who want to build robust, error-free databases. SQL Constraints are statements used at the column or table level to define rules for inserting data into a column or table. Constraints are restrictions on data types that ensure the data’s reliability, consistency, and accuracy. Learn more from this blog, and explore some practical examples of using various SQL constraints effectively in your database.
Watch this Video on MS SQL Tutorial for Beginners
What are the Constraints in SQL?
Constraints in SQL are rules you can set to control the data inserted or updated in a table. These specific rules ensure that the data in your tables is accurate and consistent, preventing errors and inconsistencies. Constraints set to enforce a range of regulations, from providing each record with a unique identifier to certain columns with a specific data type.
How to Implement Constraints in SQL?
In SQL, you can create constraints using the CREATE TABLE command when creating a new table or using the ALTER TABLE command to modify an existing table. Constraints are rules that define data integrity and enforce specific conditions on the columns of a table.
To learn how to create SQL constraints using the CREATE TABLE command, you can use the following syntax:
SQL:
CREATE TABLE table_name (
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
...
);
In this syntax:
‘table_name’ is the name of the table you want to create.
‘column_name’ is the name of the column you want to create.
‘data_type’ is the data type of the column.
‘size’ specifies the maximum size or length of the column.
‘constraint_name’ is the name you want to give to the constraint.
Alternatively, if you want to create constraints using the ALTER TABLE command, use the ADD CONSTRAINT clause.
Here’s the syntax:
SQL:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
column_name data_type(size);
In this syntax:
‘table_name’ is the name of the table to which you want to add the constraint.
‘constraint_name’ is the name you want to give to the constraint.
‘column_name’ is the column's name to which the constraint applies.
‘data_type’ represents the data type of the column.‘size’ specifies the maximum size or length of the column.
Types of Constraints in SQL
There are different types of SQL constraints to aim for the accuracy and consistency of data in a table. Here are some common types of constraints, where we will clarify your understanding of SQL constraint syntax and examples:
NOT NULL Constraint in SQL
This constraint ensures that a column in a table must have a value and cannot be left blank or empty. It is useful for columns that must always contain data, such as a customer’s name or an order number.
- NOT NULL Constraints on CREATE TABLE
Below is an example of a SQL query to demonstrate the NOT NULL constraint on column definitions in the CREATE TABLE statement. The query also includes an INSERT statement to insert values into the table:
Example
CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
JobPosition varchar(255) NOT NULL
);
INSERT INTO Mytable (UserID, FirstName, LastName, JobPosition)
VALUES (1, 'John', 'Doe', 'Manager');
Executing this SQL query will create the “Mytable” table with the specified columns, and insert one row of values into the table. The table will have the following structure and data:
Table “Mytable”:
+-------------+---------------+-------------+----------------+
| UserID | FirstName | LastName |JobPosition |
+-------------+---------------+-------------+----------------+
| 1 | John | Doe | Manager |
+----------+----------------+----------------+---------------+
In this example, the NOT NULL constraint is applied to all columns (UserID, FirstName, LastName, and JobPosition). This ensures that these columns must have non-null values when inserting data into the table.
Get 100% Hike!
Master Most in Demand Skills Now!
- NOT NULL Constraints on ALTER TABLE
Below is an example of how you can apply the NOT NULL constraint using ALTER TABLE on the existing “Mytable” table:
Example
ALTER TABLE Mytable
ALTER COLUMN UserID int NOT NULL,
ALTER COLUMN FirstName varchar(255) NOT NULL,
ALTER COLUMN LastName varchar(255) NOT NULL,
ALTER COLUMN JobPosition varchar(255) NOT NULL;
By executing the above ALTER TABLE statement, the NOT NULL constraint will be added to each of the columns (UserID, FirstName, LastName, and JobPosition) in the “Mytable” table. This means that these columns will now require non-null values when inserting or updating data in the table.
CHECK Constraint in SQL
This constraint ensures that the data in a column meets a specific condition or set of conditions.
- CHECK Constraints on CREATE TABLE
Below is an example of a SQL query to demonstrate the CHECK constraint on column definitions in the CREATE TABLE statement. The query also includes an INSERT statement to insert values into the table:
Example
CREATE TABLE Employees (
EmployeeID int PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
Age int,
Salary decimal(10,2),
CONSTRAINT age_check CHECK (Age >= 18),
CONSTRAINT salary_check CHECK (Salary > 0)
);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Salary)
VALUES (1, 'John', 'Doe', 25, 50000.00),
(2, 'Jane', 'Smith', 32, 65000.00),
(3, 'Michael', 'Johnson', 17, 0.00);
Executing this SQL query will create the “Employees” table with the specified columns, including CHECK constraints on the “Age” and “Salary” columns. It will then insert three rows of values into the table. The table will have the following structure and data:
Table “Employees”:
+-------------+-----------+----------+-----+--------------+
| EmployeeID | FirstName | LastName | Age | Salary |
+-------------+-----------+----------+-----+--------------+
| 1 | John | Doe | 25 | 50000.00 |
| 2 | Jane | Smith | 32 | 65000.00 |
| 3 | Michael | Johnson | 17 | 0.00 |
+-------------+-----------+----------+-----+--------------+
This example applies the CHECK constraints to the “Age” and “Salary” columns. The “age_check” constraint ensures that the “Age” value must be greater than or equal to 18, and the “salary_check” constraint ensures that the “Salary” value must be greater than 0. These constraints enforce data integrity and prevent invalid values from being inserted into the table.
- CHECK Constraints on ALTER TABLE
To apply the CHECK constraint using ALTER TABLE on the existing “Employees” table, you can use the following ALTER TABLE statement:
Example
ALTER TABLE Employees
ADD CONSTRAINT age_check CHECK (Age >= 18),
ADD CONSTRAINT salary_check CHECK (Salary > 0);
By executing the above ALTER TABLE statement, the CHECK constraints will be added to the “Age” and “Salary” columns in the “Employees” table. Any new rows inserted or updated in the table must satisfy the specified conditions in the CHECK constraints.
- CHECK Constraints on DROP TABLE
To remove the CHECK constraints from the “Employees” table, you would need to use the ALTER TABLE statement with the DROP CONSTRAINT clause. Here’s an example:
Example:
ALTER TABLE Employees
DROP CONSTRAINT age_check,
DROP CONSTRAINT salary_check;
Executing the above ALTER TABLE statement will remove the CHECK constraints named “age_check” and “salary_check” from the “Employees” table, allowing unrestricted values in the “Age” and “Salary” columns.
UNIQUE Constraint in SQL
A unique constraint ensures that the data in a column is unique across all records in the table.
- UNIQUE Constraints on CREATE TABLE
Below is an example of a SQL query to demonstrate the UNIQUE constraint on column definitions in the CREATE TABLE statement. The query also includes an INSERT statement to insert values into the table:
Example
CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
CONSTRAINT unique_user_id UNIQUE (UserID)
);
INSERT INTO Mytable (UserID, FirstName, LastName, JobPosition)
VALUES (1, 'John,' 'Doe,' 'Manager'),
(2, 'Jane,' 'Smith,' 'Engineer'),
(3, 'Michael,' 'Johnson,' 'Analyst');
Executing this SQL query will create the “Mytable” table with the specified columns, including a UNIQUE constraint on the “UserID” column. It will then insert three rows of values into the table. The table will have the following structure and data:
CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition)
);
Table “Mytable”:
+--------+-----------+----------+-------------+
| UserID | FirstName | LastName | JobPosition |
+--------+-----------+----------+-------------+
| 1 | John | Doe | Manager |
| 2 | Jane | Smith | Engineer |
| 3 | Michael | Johnson | Analyst |
+--------+-----------+----------+-------------+
In this example, the UNIQUE constraint is applied to the “UserID” column, ensuring that each value in the column must be unique within the table. Attempting to insert or update a row with a duplicate “UserID” value will result in an error.
- UNIQUE Constraints on ALTER TABLE
Below is an example of how you can apply the UNIQUE constraint using ALTER TABLE on the existing “Mytable” table:
Example
ALTER TABLE Mytable
ADD CONSTRAINT unique_user_id UNIQUE (UserID);
By executing the above ALTER TABLE statement, the UNIQUE constraint will be added to the “UserID” column in the “Mytable” table. This means that the “UserID” values must be unique within the table. If any attempt is made to insert or update a row with a duplicate “UserID” value, an error will be thrown.
- UNIQUE Constraints on DROP TABLE
If you want to remove the UNIQUE constraint from the “Mytable” table, you would need to use the ALTER TABLE statement with the DROP CONSTRAINT clause. Here’s an example:
ALTER TABLE Mytable
DROP CONSTRAINT unique_user_id;
Executing the above ALTER TABLE statement will remove the UNIQUE constraint named “unique_user_id” from the “Mytable” table, allowing for duplicate values in the “UserID” column.
PRIMARY KEY Constraint in SQL
This type of SQL constraint is developed to aim that each record in the table has a very unique identifier. It is typically applied to a column that contains a unique value for each record, such as an ID number or a name.
- CREATE PRIMARY KEY ON A TABLE
Below is an example of a SQL query to demonstrate the PRIMARY KEY in SQL constraint on column definitions in the CREATE TABLE statement. The query also includes an INSERT statement to insert values into the table:
Example
Customer Table
CREATE TABLE Mytable (
UserID int PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255)
);
INSERT INTO Mytable (UserID, FirstName, LastName, JobPosition)
VALUES (1, 'John,' 'Doe,' 'Manager'),
(2, 'Jane,' 'Smith,' 'Engineer'),
(3, 'Michael,' 'Johnson,' 'Analyst');
Executing this SQL query will create the “Mytable” table with the specified columns, including a PRIMARY KEY constraint on the “UserID” column. It will then insert three rows of values into the table.
- PRIMARY KEY ON ALTER TABLE
Below is an example of how you can apply the PRIMARY KEY constraint using ALTER TABLE on the existing “Mytable” table:
Example
ALTER TABLE Mytable
ADD PRIMARY KEY (UserID);
By executing the above ALTER TABLE statement, the PRIMARY KEY constraint will be added to the “UserID” column in the “Mytable” table. This means that the “UserID” values must be unique and not null within the table. If any attempt is made to insert or update a row with duplicate or null “UserID” values, an error will be thrown.
- PRIMARY KEY ON DROP TABLE
If you want to remove the PRIMARY KEY constraint from the “Mytable” table, you would need to use the ALTER TABLE statement with the DROP CONSTRAINT clause. Here’s an example:
Example
ALTER TABLE Mytable
DROP CONSTRAINT constraint_name;
Executing the above SQL ALTER TABLE statement will remove the PRIMARY KEY constraint from the “Mytable” table, allowing for duplicate and null values in the “UserID” column.
DEFAULT Constraint in SQL
A default constraint is used to specify a default value for a column in a table. The default value will be used instead if no value is selected for the column when a new row is inserted.
- DEFAULT Constraints on CREATE TABLE
Below is an example of a SQL query to demonstrate the DEFAULT constraint on column definitions in the CREATE TABLE statement. The query also includes an INSERT statement to insert values into the table:
Example
CREATE TABLE Students (
StudentID int PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
Age int DEFAULT 18,
Gender varchar(10) DEFAULT 'Unknown'
);
INSERT INTO Students (StudentID, FirstName, LastName)
VALUES (1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Michael', 'Johnson');
Executing this SQL query will create the “Students” table with the specified columns, including DEFAULT constraints on the “Age” and “Gender” columns. It will then insert three rows of values into the table. The table will have the following structure and data:
Table “Students”:
+-----------+-----------+--------------+-----+-----------+
| StudentID | FirstName | LastName | Age | Gender |
+-----------+-----------+--------------+-----+-----------+
| 1 | John | Doe | 18 | Unknown |
| 2 | Jane | Smith | 18 | Unknown |
| 3 | Michael | Johnson | 18 | Unknown |
+-----------+-----------+--------------+-----+-----------+
This example applies the DEFAULT constraints to the “Age” and “Gender” columns. The “Age” column is set to have a default value of 18, and the “Gender” column is set to have a default value of ‘Unknown.’ These DEFAULT constraints ensure that the default values will be used instead if a value is not provided for these columns during the INSERT operation.
- DEFAULT Constraints On ALTER TABLE
To apply the DEFAULT constraint using ALTER TABLE on the existing “Students” table, you can use the following ALTER TABLE statement:
Example
ALTER TABLE Students
ALTER COLUMN Age SET DEFAULT 18,
ALTER COLUMN Gender SET DEFAULT 'Unknown';
By executing the above ALTER TABLE statement, the DEFAULT constraints will be added to the “Age” and “Gender” columns in the “Students” table. Any new rows inserted into the table without providing explicit values for these columns will use the specified default values.
- DEFAULT Constraints On DROP TABLE
To remove the DEFAULT constraints from the columns in the “Students” table, you need to use the ALTER TABLE statement with the ALTER COLUMN clause to modify the column definitions. Here’s an example:
ALTER TABLE Students
ALTER COLUMN Age DROP DEFAULT,
ALTER COLUMN Gender DROP DEFAULT;
Executing the above ALTER TABLE statement will remove the DEFAULT constraints from the “Age” and “Gender” columns in the “Students” table.
FOREIGN KEY Constraint in SQL
A Foreign Key constraint establishes a relationship between two tables, typically by linking a column in one table to a primary key in another table. It ensures that the data in the linked columns is consistent and accurate across both tables.
- FOREIGN KEY ON CREATE TABLE
Here’s an example of a SQL query to demonstrate the FOREIGN KEY constraint on column definitions in the CREATE TABLE statement. The query also includes an INSERT statement to insert values into the table:
Example
Table Users:
CREATE TABLE Users (
UserID int PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL
);
Table Orders:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
UserID int,
OrderDate date,
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
INSERT INTO Users (UserID, FirstName, LastName)
VALUES (1, 'John', 'Doe'),
(2, 'Jane', 'Smith');
INSERT INTO Orders (OrderID, UserID, OrderDate)
VALUES (1, 1, '2022-01-01'),
(2, 2, '2022-02-01');
Executing this SQL query will create two tables, “Users” and “Orders”. The “Users” table has UserID, FirstName, and LastName columns, while the “Orders” table has OrderID, UserID, and OrderDate columns. The FOREIGN KEY constraint is applied to the “UserID” column in the “Orders” table, referencing the “UserID” column in the “Users” table.
Here is a representation of whathow the ‘Users’ and ‘Orders’ tables will look like:
Table “Users”:
+---------+------------+-------------+
| UserID | FirstName | LastName |
+---------+------------+-------------+
| 1 | John | Doe |
| 2 | Jane | Smith |
+---------+------------+-------------+
Table “Orders”:
+---------+----------------+--------------+
| OrderID | UserID | OrderDate |
+---------+----------------+--------------+
| 1 | 1 | 2022-01-01 |
| 2 | 2 | 2022-02-01 |
+---------+----------------+--------------+
In this example, the FOREIGN KEY constraint establishes a relationship between the “Orders” table and the “Users” table based on the “UserID” column. It ensures referential integrity by only allowing values in the “Orders” table’s “UserID” column that exist in the “Users” table’s “UserID” column.
- FOREIGN KEY ON ALTER TABLE
To apply the FOREIGN KEY constraint using ALTER TABLE on the existing tables, you’ll need to perform the following steps:
First, create the “Users” table without any foreign key constraints:
CREATE TABLE Users (
UserID int PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL
);
Then, create the "Orders" table without the FOREIGN KEY constraint:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
UserID int,
OrderDate date
);
Finally, alter the “Orders” table to add the FOREIGN KEY constraint:
ALTER TABLE Orders
ADD CONSTRAINT fk_user_id FOREIGN KEY (UserID) REFERENCES Users(UserID);
Executing these SQL statements will create the “Users” and “Orders” tables and then add the FOREIGN KEY constraint to the “UserID” column in the “Orders” table, referencing the “UserID” column in the “Users” table.
- FOREIGN KEY ON DROP TABLE
To remove the FOREIGN KEY constraint from the “Orders” table, you would need to use the ALTER TABLE statement with the DROP CONSTRAINT clause. Here’s an example:
ALTER TABLE Orders
DROP CONSTRAINT constraint_name;
In the example above, replace “constraint_name” with the actual name of the foreign key constraint. The exact name of the foreign key constraint depends on the database system you are using.
The above ALTER TABLE statement will remove the FOREIGN KEY constraint from the “Orders” table, allowing unrestricted values in the “UserID” column.
AUTO_INCREMENT Constraint in SQL
An auto-increment constraint automatically generates a unique value for a column each time a new row is inserted into a table. It is specifically used for primary key columns so that each new row is automatically assigned a unique identifier.
Below is an example of a SQL query to demonstrate the AUTO_INCREMENT attribute for generating auto-incremented values in a column:
CREATE TABLE Employees (
EmployeeID int AUTO_INCREMENT PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL
);
INSERT INTO Employees (FirstName, LastName)
VALUES ('John,' 'Doe'),
('Jane,' 'Smith');
This SQL query will create the “Employees” table with an “EmployeeID” column defined with the AUTO_INCREMENT attribute. This attribute allows the “EmployeeID” column to generate unique values sequentially for each inserted row automatically. The table will have the following structure and data:
Table “Employees”:
+------------+------------+-----------+
| EmployeeID | FirstName | LastName |
+------------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
+------------+------------+-----------+
In this example, the “EmployeeID” column is defined with AUTO_INCREMENT, and as new rows are inserted into the table, the “EmployeeID” values will automatically increment starting from 1. It provides a convenient way to generate unique identifiers for each employee in the table.
Conclusion
SQL constraints are essential to database design and management. They ensure the integrity and accuracy of data by enforcing rules that restrict specific actions in the database. These little rules and regulations may seem insignificant, but they play a crucial role in ensuring the accuracy and reliability of your data.
Understanding how to use constraints effectively when designing and managing a database is essential. With careful planning and implementation, constraints can help ensure the accuracy and reliability of data in any database system.