MS SQL Training (Constraints) Part-2

Written by creation2018, September 2, 2018


SQL Server contain 6 type of constraint
1. Not Null
2. Primary Key
3. Unique
4. Default
5. Check
6. Foreign Key

NOT NULL : Using Not Null Constraint we can make a column who doesnot accept null value mean we have to provide some value into that column.

create table Customers(
CustomerID int not null,
Name varchar(100),
Email varchar(100) not null,
Mobile varchar(20) not null,
City varchar(100),
Payment int
);

Creation InfoTech SQL Training

Primary Key : Using Primary Key Constraint we can make a column of a table who accept always different value means primary key column doesnot allow duplicate values.
Primary key column also doesnot allow null value into that column.
We can make only one column of a table as primary key.

create table Customers(
CustomerID int primary key,
Name varchar(100),
Email varchar(100) not null,
Mobile varchar(20) not null,
City varchar(100),
Payment int
);

Creation InfoTech SQL Training

Unique : Using Unique Constraint we can make a column of a table who accept always different value means Unique Key column doesnot allow duplicate values.
Unique key column can allow null value into only one row of a column.
We can make multiple column of a table as unique.

create table Customers(
CustomerID int primary key,
Name varchar(100),
Email varchar(100) not null unique,
Mobile varchar(20) unique,
City varchar(100),
Payment int
);

Default : Using Default constraint we provide a default value to a column and that will work if donot assign any value into that column mean in place of null automatically default value insert.

create table Customers(
CustomerID int primary key,
Name varchar(100),
Email varchar(100) not null unique,
Mobile varchar(20) unique,
City varchar(100),
Payment int default 0
);

Check : Using check constraint we can check a condition into a column value.

create table Customers(
CustomerID int primary key,
Name varchar(100),
Email varchar(100) not null unique,
Mobile varchar(20) unique,
City varchar(100),
Payment int check (payment>100 and payment<5000)
);

Foreign Key :

create table Products(
ProductId int Primary Key,
Name varchar(100),
Price int
);

create table Customers(
CustomerId int Primary key,
Name varchar(100),
Email varchar(100),
Mobile varchar(20),
City varchar(20)
);

create table Orders(
OrderId int Primary Key,
Customer_Id int Foreign Key references Customers(CustomerId),
Product_Id int Foreign Key references Products(ProductId),
OrderDate date,
Quantity int
);

insert into Customers values(1,’Aditya’,’[email protected]’,’9876453789′,’Pune’);
insert into Customers values(2,’Sumit’,’[email protected]’,’8676453778′,’Mumbai’);

insert into Products values(101,’MI SmartTv’,12000);
insert into Products values(201,’Iphone8′,25000);
insert into Products values(301,’Macbook Pro’,50000);
insert into Products values(401,’Tshirt’,5000);
insert into Products values(501,’Titan Watch’,8000);

select * from Customers;
select * from Products;

insert into Orders values(120123,2,301,’03 Sep 2018′,2);
select * from Orders;