SQL constraints are used to specify rules for the data in a table. If
there is any violation between the constraint and the data action, the action
is aborted by the constraint.
Some time we
need to delete the records from the child table we are getting error that
Msg
547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Dept_ID". The conflict occurred in database "Emp", table "Dept", column 'DeptID'.
The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "FK_Dept_ID". The conflict occurred in database "Emp", table "Dept", column 'DeptID'.
The statement has been terminated.
In this we
have to delete the records in parent record. It will be bit difficult do this. For
the best way to disable the constraints and delete the records from the child
table and after that we need to enable the constraint.
Disable Constraints
Disable the all constraints from a table
-- Disable all table constraints
ALTER TABLE FactInternetSales NOCHECK CONSTRAINT ALL
|
Suppose if we want to disable the all constraint of the all
table use below script
declare @temp table
( ID int IDENTITY(1,1),
script varchar(500)
)
insert into @temp
SELECT 'ALTER TABLE [' + S.name + '].[' + T.name + '] NOCHECK CONSTRAINT ALL'
AS
[SQL to run:]
FROM sys.tables T
JOIN sys.schemas S
ON T.schema_id = S.schema_id
ORDER BY T.name
select * from @temp
|
It will return the script to disable the all constraint.
Run the below script to disable the all constraint
declare @temp table
(
ID int
IDENTITY(1,1),
script varchar(500)
)
insert into @temp
SELECT 'ALTER TABLE ['
+ S.name + '].[' + T.name +
'] NOCHECK
CONSTRAINT ALL'
AS
[SQL Script]
FROM sys.tables T
JOIN sys.schemas S
ON T.schema_id = S.schema_id
ORDER BY T.name
Declare @MinID int=1,
@MaxID int= (select Count(*) from @temp),@script varchar(500)
while (@MinID<=@MaxID)
BEGIN
select @script=script
from @temp where
ID=@MinID
exec (@script)
SET @MinID=@MinID+1
END
|
Enable Constraint
If we want to enable the constraint for a table use below
script
-- Enable all table constraints
ALTER TABLE FactInternetSales CHECK CONSTRAINT ALL
|
Enable the constraint for all tables in a database
declare @temp table
(
ID int
IDENTITY(1,1),
script varchar(500)
)
insert into @temp
SELECT 'ALTER TABLE ['
+ S.name + '].[' + T.name +
'] WITH CHECK CHECK CONSTRAINT ALL'
AS
[Sql script]
FROM sys.tables T
JOIN sys.schemas S
ON T.schema_id = S.schema_id
ORDER BY T.name
Declare @MinID int=1,
@MaxID int= (select Count(*) from @temp),@script varchar(500)
while (@MinID<=@MaxID)
BEGIN
select @script=script
from @temp where
ID=@MinID
exec (@script)
SET @MinID=@MinID+1
END
|
No comments:
Post a Comment
If you have any doubt, please let me know.