Skip to main content

Enable/ Disable Constraints

The best way to insert the valid data by using constraints. This also known as data enforcing data or data integrity. We already know the constraints in sql server.diffrent types of constraints in sql server. They are.

  1. Primary key constraint.
  2. Default key Constraint.
  3. Check Constraint.
  4. Foreign key Constraint
  5. Unique key Constraint
Example:

create table Student
(

ID int primary key,
Name varchar(20) unique ,
Marks int not null check (marks>500),
SchoolName varchar(20) default 'HydPublic'
)

Senario-1:

insert into Student(ID,Name,Marks) values(1,'rakesh',550)

If we check the folliowing data from Student table

select * from Student






Senario-2:

insert into Student(ID,Name,Marks) values(2,'nagaraju',350)

If we are trying to insert second record with marks 350 values we ever run above insert query it gives error because.


The INSERT statement conflicted with the CHECK constraint "CK__Student__Marks__29572725". The conflict occurred in database "Demo", table "dbo.Student", column 'Marks'.

How to insert 350 marks into marks column ?

Ans:

We need to disable the Check Constraint on table.

How can we do this stuff?

Ans:

By using alter statement.

Synatx:

Alter table ‘tablename’ nocheck constraint ‘Check_Constraintname’

Exmaple:

alter table Student nocheck constraint CK__Student__Marks__29572725

insert into Student(ID,Name,Marks) values(2,'nagaraju',350)

Try to insert the above record . we we run above insert query the record get’s inserted successfully.because of check constraint is diabled on this time.
Check the data


select * from Student

How to enable check constraint on table ?

alter table Student check constraint CK__Student__Marks__29572725

try to insert the the invalid data on Marks column and see the result.

Senario-3:

create table SchoolName
(
ID int identity(1,1) primary key,
Name varchar(20) unique
)

Drop table Student

create table Student
(

ID int primary key,
Name varchar(20) unique ,
Marks int not null check (marks>500),
SchoolName int foreign key references SchoolName(ID)
)

I have created the 2 tables SchoolName table is Just like Master table . it is maintaining tte All school Name Information on this table. Student table is maintaining the Schools information. In student table SchoolName columns is references to the ID column in SchoolName table.
Insert Data to SchoolName table

insert into SchoolName
select 'HydPublic'
union all
select 'BabyMoon'

Select ScoolName table


select * from SchoolName






insert into Student(ID,Name,Marks,SchoolName) values(1,'rakesh',550,1)

Run above query.record get inserted successfully.

insert into Student(ID,Name,Marks,SchoolName) values(2,'phani',550,3)

Run above query it throws an error.beacause of SchoolName value 3 deos not exists in ID column of SchoolName table.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Student__SchoolN__4222D4EF". The conflict occurred in database "Demo", table "dbo.SchoolName", column 'ID'.

insert into Student(ID,Name,Marks,SchoolName) values(2,'phani',550,3)

How Run above Insert Query Successfully ?
Ans:
To disable the FOREIGN KEY Constraint from Student table.

How can we do this stuff?
Ans:
By using alter statement.

Syntax:

Alter table ‘tablename’ nocheck constraint ‘FOREIGN KEY CONSTRAINT NAME’

Example:
alter table Student nocheck constraint 
FK__Student__SchoolN__4222D4EF

Try insert record.
insert into Student(ID,Name,Marks,SchoolName) values(2,'phani',550,3)

Here record get insereted successfully .

How to enable FOREIGN KEY Constraint?

alter table Student check constraint FK__Student__SchoolN__4222D4EF

After enable try insert below inavlid record ans check result.

insert into Student(ID,Name,Marks,SchoolName) values(3,'Dan',550,3)

Is it possible disable and enable the all constraint from a table ?
Ans:
Yes.

Example:

Disable:
alter table Student nocheck constraint all
Enable :
alter table Student check constraint all

Limitations:
  • Enabing , disabling constraints works only check constraint and foreign key constraint.
  • Enabing , disabling constraints does not works default,primary, unique constraint.

Labels:

Comments

Post a Comment

Popular posts from this blog

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...
7 comments
Read more

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee (   ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,   NAME VARCHAR ( 20 ),   SALARY INT ) INSERT INTO Employee   ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...
1 comment
Read more

Simple Trick Using ISNULL

Simple Trick Using ISNULL: Step-1: Create Sample Student Table. CREATE TABLE Student ( Id INT IDENTITY ( 1 , 1 ) PRIMARY KEY , StudentName VARCHAR ( 100 ) NOT NULL, LivingLocation VARCHAR ( 100 ) NULL ) Step-2 :Insert Some Sample Data INSERT INTO Student SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' Step-3 : Select Data from Student Table SELECT * FROM Student If we observe above data of student table we have student id 4 record LivingLocation is NULL. Step -4 :Select Data from Student Table With Where Clause. SELECT * FROM Student WHERE LivingLocation = 'Hyderabad' In the above query we are included the where clause w...
3 comments
Read more