SQL Table Constraints In Database

In this post, we will discuss how to work with SQL constraints and explain the concepts with an example in simple way. I hope this is very useful for beginners to help them understand the basic concept.

Introduction

In this post, we will discuss how to work with SQL constraints and explain the concepts with an example in a simple way. I hope this is very useful for beginners as it helps them understand the basic concept.

Prerequisites

Before we start with what an SQL constraint is, let us confirm that we are already aware of creating a simple table with some basic Data Definition Language (DDL) query create.

What are the constraints?

SQL constraints are used to specify the rules for the data in a table. Constraints can be specified at the time of creating the table.

We have the following constraints in SQL.

• DEFAULT - To specify the default value for a column
• NOT NULL – To restrict that a column cannot store NULL value
• PRIMARY KEY – The combination of a NOT NULL and UNIQUE.  Restricts that a column or combination of two or more columns have a unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY – To restrict the referential integrity of the data in one table to match values in another table
• UNIQUE – To restricts that each row for a column must have a unique value
• CHECK – To restrict that the value in a column meets a specific condition

DEFAULT

If you want to specify the default value for a column, then you use DEFAULT constraint. If we are not explicitly specifying any value, then the default value will be added.

Example

Create a table Passport with Default constraints.

Create table Passport
(
   Id int primary key identity,
   PassportId nvarchar(50) NOT NULL,
   FirstName nvarchar(50) NOT NULL,
   LastName nvarchar(50) NOT NULL,
   Gender nvarchar(10) DEFAULT 'Male',
   Address nvarchar(50) NOT NULL,
   City nvarchar(50) NOT NULL,
   State nvarchar(50) NOT NULL,
   Country nvarchar(50) NOT NULL
)

Output

NOT NULL

The "Not Null" constraints enforce that the column cannot store NULL values. Whatever the column or field is, it must have some value.

Example

Create a table Passport with NOT NULL constraints.

Create table Passport
(
   Id int primary key identity,
   PassportId nvarchar(50) NOT NULL,
   FirstName nvarchar(50) NOT NULL,
   LastName nvarchar(50) NOT NULL,
   Address nvarchar(50) NOT NULL,
   City nvarchar(50) NOT NULL,
   State nvarchar(50) NOT NULL,
   Country nvarchar(50) NOT NULL
)

Output

 

Note
I tried to insert the NULL value on Not Null constraint LastName column but insert failed as expected.

Example

INSERT INTO Passport VALUES ('INDABC123','J',NULL,'L16 3JT','Latur', 'Maha', 'IND')

Output

PRIMARY KEY

This is a combination of NOT NULL and UNIQUE. PRIMARY KEY restricts that a column or combination of two or more columns has a unique identity which helps to find a particular record in a table more quickly.

Each table can have only one primary key. A primary key must contain a unique value. However, a column which is used to identify each row in the table uniquely and key column cannot contain NULL values.

Example

We are on Id column creating a primary key.

Create table Passport
(
   Id int primary key identity,
   PassportId nvarchar(50) NOT NULL,
   FirstName nvarchar(50) NOT NULL,
   LastName nvarchar(50) NOT NULL,
   Address nvarchar(50) NOT NULL,
   City nvarchar(50) NOT NULL,
   State nvarchar(50) NOT NULL,
   Country nvarchar(50) NOT NULL
)

Output

 

Note
We can not create more than one primary key in a single table.

Output

FOREIGN KEY

The Foreign key must be a primary key in another table to ensure the referential integrity of the data in one table to match values in another table.

Note
We can create more than one foreign key in a single table. 

Example

As we know, we have a passport table with primary key and now, we are creating an Appointments table for foreign key referential integrity.

Create table Appointments
(
   AppointmentId int primary key identity,
   PassportId int foreign key references Passport(ID),
   AppointmentFirst datetime,
   AppointmentSecond datetime,
   AppointmentThired datetime
)

Output

UNIQUE

The unique key constraints uniquely identify each record in a database table. Whenever we specify primary key, it automatically has unique constraints defined on it.

Note
Only one primary key can be there per table but we can have many unique constraints per table.

Example

Create table Passport
(
   Id int Not Null Unique identity,
   PassportId nvarchar(50) NOT NULL,
   FirstName nvarchar(50) NOT NULL,
   LastName nvarchar(50) NOT NULL,
   Address nvarchar(50) NOT NULL,
   City nvarchar(50) NOT NULL,
   State nvarchar(50) NOT NULL,
   Country nvarchar(50) NOT NULL)

Step 1 We are going to insert one record.

 

Note
We are going to insert the second record with the same id but insert failed expected.

Output

CHECK

If you want to limit or restrict that the value in a column meets a specific condition then we use check constraints.

Example

I tried to insert record of one employee who has salary less than 5000 constraint LastName column but insert failed as expected.

create table Employee
(
   EmployeeId int primary key,
   Name nvarchar(50),
   Age int,
   salary money
   CHECK(salary >=5000 and salary <=100000)
)

Insert into Employee values(1,'AAA',55,500)

Output