- Disable/Enable All Table Constraints
- E.g. if you have an user table and an UserLog table, UserLog have a foreign key which reference the UserID in the user table.
- Now you can disable the foreign key Constraint on the UserLog table and insert a userid which does not exist in the user table into the UserLog table. After that you can enable the constraint.
CREATE PROCEDURE [dbo].[procDisableEnableAllTableConstraints]
@TblName VARCHAR(128),
@IsCheck BIT = 1
AS
DECLARE @SQLState VARCHAR(500)
IF @IsCheck = 0
BEGIN
SET @SQLState = 'ALTER TABLE [' + @TblName + '] NOCHECK CONSTRAINT ALL'
END
ELSE
BEGIN
SET @SQLState = 'ALTER TABLE [' + @TblName + '] CHECK CONSTRAINT ALL'
END
EXEC (@SQLState)
Public Shared Sub DisableEnableAllTableConstraints(ByVal tableName As String, ByVal isCheck As Boolean)
Dim dbCon As New SqlConnection(clsConfig.BaseConnectionString)
dbCon.Open()
Dim cmd As New SqlCommand("[procDisableEnableAllTableConstraints]", dbCon)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@TblName", tableName)
cmd.Parameters.AddWithValue("@IsCheck", IIf(isCheck, 1, 0))
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
Throw (ex)
Finally
dbCon.Close()
End Try
End Sub
Reference:
Stored Procedure to Disable/Reenable All Constraints on a Given Table
