Constraint In MS SQL Server : 1

According to Microsoft, constraint is nothing but a rule applied on single column or multiple column on MS SQL server table.

 

Below six types of commonly used constraint in MS SQL server. All below constraint can be applied on column level but few from below list can be applied on column and table level both. Will discuss more in detail below. In this article we will discuss on Not Null, Default Check and Unique. Primary and Foreign will discuss on another article.

    1.       Not Null

    2.       Default

    3.       Check

    4.       Unique

    5.       Primary key

    6.       Foreign key

 

Not Null:

                When you create a table, by default table column support null value. If you want to avoid null value in column specify not null constraint for that column. 

Created student table and applied two not null constraint on StudentName and Grade column.

Now try to insert data in Student table, EmailId column skipped in insert statement still data inserted as EmailId column allow null entry.

Let’s try to insert null entry for column where not null constraint applied.

SQL server also allow to apply constraint on existing table. Let’s apply Not Null constraint on EmailId column also.

SQL Server is not allowing to apply Not Null constraint on EmailId column.  Lets check table data to understand why SQL is not allowing.

   

If applying constraint on existing table make sure that column does not contain any null value. To apply Not Null constraint on EmailId column you need to delete null records from that column/table. For now  will delete the record from table using below query.

delete from Student

Now apply Not Null constraint on Student table and it will be applied successfully, as we deleted all records so no null value present in EmailId column.

To drop Not Null constraint from column use below query.

Alter table Student

Alter column EmailId int null

 

Default:

                Default constraint is used to set default value for a column if value not provided for column while inserting record into table.

 

Let’s understand with an example, make sure student table dropped before creating.

drop table student

 

create table student

(

       StudentId int primary key,

       StudentName  varchar (30),

       EmailId varchar(30),

       Grade int default 1,

       City varchar(10) default 'Pune'

)

 

Insert into student (StudentId,StudentName,EmailId) values (1,'Saquib','test@gmail.com')

 

In above insert statement, I didn’t provide any column value for City and Grade still values showing for City and Grade column. This is because default constraint applied on column City and Grade while creating table highlighted on below screen.

We can apply default constraint in existing table.

Alter table Student

add constraint con_EmailId default 'common@gmail.com' for EmailId

Constraint with name con_EmailId which is of type default applied on column EmailId and default value is ‘common@gmail.com’. run above script in sql and check table records.

Noticed I have inserted only two column Student Id and StudentName, remaining all column are with default value if no value provided while inserting record.

 

 

Check:

                Check constraint is like range validation before entering value into column. It checks value while inserting records into table, if the value within range allow to insert records else give an error.

 

Create student table drop if already available in database and run below command.

create table student

(

       StudentId int primary key,

       StudentName varchar(30) not null,

       Grade int check (Grade >0 and Grade <=10)

)

        

 

Drop table student

Create table again with check constraint, apply constraint at table level.

 

create table student

(

       StudentId int primary key,

       StudentName varchar(30) not null,

       Grade int,

       age int,

       constraint chk_table_level check (age > 5 and age <= 15 and grade > 0 and grade <=10)

)

Insert record into table student

Insert into student (StudentId,StudentName, Grade,age) values (1,'Ashish',-1,4)

Observe it won’t allow to insert record if column value not matching condition of check constraint. Also, at right side it show constraint name under constraints folder highlighted.

Drop constraint with the help of below query.

Alter table student

Drop constraint chk_table_level

Now try to insert record and it will insert it means constraint deleted and you can check in SQL server object Explorer, constraint folder is empty highlighted in below screen.

Insert into student (StudentId,StudentName, Grade,age) values (1,'Ashish',-1,4)

 

Unique

As name suggest it restricts duplicate value in column while inserting record.  MS SQL unique column support 1 Null value for second Null Value it considered as duplicate and avoid inset statement.

 

create table student

(

       StudentId int primary key,

       StudentName varchar(30) not null,

       EmailId varchar(30) unique

)

Now insert below two records, first records will inserted and second one give an error.

Insert into student(StudentId,StudentName,EmailId) values (1, 'Sara', 'abc@gmail.com')

Insert into student(StudentId,StudentName,EmailId) values (2, 'Sara', 'abc@gmail.com')

As discussed, observer first insert statement inserted successfully (1 row affected) while second insert statement fail to insert as unique constraint applied on EmailId column and it check value in column before inserting the value is unique or not.

Drop student table using below query.

Drop table student

 

Apply unique constraint on table level.

create table student

(

       StudentId int ,

       StudentName varchar(30) not null,

       EmailId varchar(30)

       constraint unique_Constraint unique (StudentId, EmailId)

)

Second inset statement fails to insert as duplicate record gets restricted by unique constraint.

 

Drop constraint using below query.

Alter table student

Drop constraint unique_Constraint

 

Add constraint in existing table using below SQL command.

-- for multiple columns

Alter table student

Add constraint unique_Constraint unique(Studentid,EmailId)

 

--- for single column

Alter table student

Add unique(EmailId)

Primary Key:

Primary key constraint is important, and it won’t allow duplicate value in column hence it is used to identify table row uniquely. It won’t allow null values. Table can have only one primary kay which uses clustered index by default. Primary key contains one or more columns.

Below table create query contains primary key with one column.

 

create table thlStudent(

       id int,

       firstName varchar(100),

       lastName varchar(100),

       Grade int,

       City varchar(100)

       primary key (id)    

)

 

Insert into thlStudent values (1, 'Atif','Aslam',2,'Pune')

Insert into thlStudent values (2, 'Adish','Aaglawe',3,'Pune')

Insert into thlStudent values (3, 'Samir','Thakur',5,'Pune')

Insert into thlStudent values (4, 'Alex','Carter',4,'Pune')

Insert into thlStudent values (4, 'Alexa','Carter',5,'Pune')

 

Observe it gives error for 5^th record as it contains duplicate id of 4 and id 4 already available in table, which means primary key restrict duplicate entries.

  

Below table create query contains primary key with three columns.

create table thlStudent(

       id int,

       firstName varchar(100),

       lastName varchar(100),

       Grade int,

       City varchar(100)

       Constraint PK_student primary key (id,firstname,lastname))

 

Insert into thlStudent values (1, 'Atif','Aslam',2,'Pune')

Insert into thlStudent values (2, 'Adish','Aaglawe',3,'Pune')

Insert into thlStudent values (3, 'Samir','Thakur',5,'Pune')

Insert into thlStudent values (4, 'Alex','Carter',4,'Pune')

Insert into thlStudent values (4, 'Alexa','Carter',5,'Pune')

Insert into thlStudent values (4, 'Alex','Porter',6,'Pune')

Insert into thlStudent values (4, 'Alex','Carter1',6,'Pune')

 

 

Notice all highlighted columns (id, firstName, lastName) looks like duplicate data, but according to primary key concept it won’t allow duplicate data. Now important point primary key contains multiple column and it is uniquely identify data against three columns (id, firstname, lastname ). All 3 columns (id, firstname, lastname ) highlighted are different if compare all three columns.

 

 

I will cover foreign key in separate article with all foreign key related concept.