For an instructor lead, in-depth look at learning SQL click below.
In SQL, constraints are rules that are used to limit the type of data that can be stored in a specific table. Constraints are essential because they ensure the accuracy and reliability of the data in the table. SQL offers several types of constraints to help you regulate and validate the data you’re inserting into your tables.
What are Constraints?
Constraints are used to specify the limit on the data type of the table. They help to ensure that the integrity and the accuracy of the data in the table are maintained. The constraints in SQL include: NOT NULL, UNIQUE, CHECK, DEFAULT, and FOREIGN KEY.
NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Employees ( ID int NOT NULL, Name varchar(255) NOT NULL, Birthday date NOT NULL, City varchar(255) ); |
UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column and helps identify each record uniquely.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Employees ( ID int NOT NULL UNIQUE, Name varchar(255) NOT NULL, Birthday date NOT NULL, City varchar(255) ); |
CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Employees ( ID int NOT NULL, Age int CHECK (Age>=18), Name varchar(255) NOT NULL, Birthday date NOT NULL, City varchar(255) ); |
DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records, if no other value is specified.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Employees ( ID int NOT NULL, Name varchar(255) NOT NULL, Birthday date NOT NULL, City varchar(255) DEFAULT 'London' ); |
FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. It provides a link between two tables.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, EmployeeID int, FOREIGN KEY (EmployeeID) REFERENCES Employees(ID) ); |
SQL constraints are used to specify rules for the data in a table. We have discussed each of these constraints with examples. Remember, constraints are part of the database schema definition and are used to enforce business rules and to prevent the entry of invalid data into the tables.