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

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

Variables in T-SQL

Variables can be used to store the data in temporally based on data type. Variable name begin with @ symbol. There are two types of variables in T-SQL. 1.   Local Variables (It must begin @ symbol). 2.   Global Variables (it must begin @@ symbol) this variables also known as system variables. Syntax Declare a Variable DECLARE   @Variable-Name   DATATYPE EXAMPLE: DECLARE   @Name   VARCHAR ( 100 ) In before 2008 versions of SQL SERVER we declare a variable and assign a value in 2 lines.                 DECLARE   @Name   VARCHAR ( 100 ) SET   @Name = 'LeadFirstSQL' In 2008 and Later versions of SQL SERVER we declare a variable and assign a value in single line. DECLARE   @Name   VARCHAR ( 100 )   = 'LeadFirstSQL' Variables must be declare and used with the same batch. DECLARE   @Name   VARCHAR ( 100 ) ...
Post a Comment
Read more

WAITFOR

WAITFOR: WAITFOR used to pauses the execution of query from certain time. There are two type's of Wait for. 1.WAITFOR DELAY 2.WAITFOR TIME WAITFOR DELAY : WAITFOR DELAY Cause the execution to delay from specified duration. For example it pause's the execution for 5 second's Example WAITFOR DELAY '00:00:05' SELECT * FROM EMP WAITFOR TIME : WAITFOR TIME is pause's the execution wait for specified time. For example '13:15:00' whenever time reached the query will be executed. WAITFOR TIME '13:15:00' SELECT * FROM EMP SELECT GETDATE ()
2 comments
Read more