Adding and removing constraints from an existing SQL Server database table is performed using the ALTER TABLE statement. The article Using Microsoft SQL Server Constraints provides more information on constraints and some good examples and Books Online has specific details, if needed.
The following statement is an example of adding a primary key constraint to the Account table:
ALTER TABLE [Account] ADD CONSTRAINT [PK_Account] PRIMARY KEY( AccountId )
A common issue that I’ve run into is not adding/removing constraints, but rather needing to disable constraints when deleting data or importing data from related tables, including the primary keys. Disabling constraints is a two-step process because the constraints must also be re-enabled. For example, the following statement disables the ‘FK_Account’ Foreign Key constraint in the Account table. The keyword “NOCHECK” disables the constraint and “CHECK” enables it.
ALTER TABLE [Account] NOCHECK CONSTRAINT [FK_Account]
Issuing a statement or two isn’t bad, but does require that one knows the name of the constraint to disable. What if you had to deal with five, ten, fifteen or more tables? I needed an automated way of identifying the constraints and creating the ALTER statements based on the constraint type.
Using the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view I was able to automate the process of creating and executing the statements using a variation of the script below. This script will list ALL the Foreign Key constraints and the corresponding table for all tables in the current database. The script can be filtered as needed, but for my purposes, all tables were needed.
SELECT constraint_name, table_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_type = 'FOREIGN KEY' ORDER BY table_name
The above script was converted to the stored procedure below to disable/enable all the constraints of a specific type for all tables.
CREATE PROCEDURE [dbo].[ToggleConstraints]
(
@type VARCHAR(128) = 'FOREIGN KEY'
,@enable BIT = 1
)
AS
SET NOCOUNT ON
DECLARE @__CONSTRAINTS TABLE(Id INT IDENTITY(1,1), ConstraintName VARCHAR(128), TableName VARCHAR(128))
DECLARE @rows INT
,@index INT
,@sql NVARCHAR(2000)
,@tableName VARCHAR(128)
,@constraintName VARCHAR(128)
-- Retrieve the constraints using the INFORMATION SCHEMA View and place into
-- a table variable based on the specified type. Primary Key and Unique Key
-- constraints are excluded by default to prevent duplicate data from being
-- inserted, but can also be included by changing the constraint_type.
INSERT INTO @__CONSTRAINTS(ConstraintName, TableName)
SELECT constraint_name, table_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = @type
ORDER BY table_name
-- Set defaults
SELECT @rows = @@ROWCOUNT, @index = 1
-- Generate and execute the sql script
WHILE( @index <= @rows )
BEGIN
SELECT @tableName = TableName, @constraintName = ConstraintName
FROM @__CONSTRAINTS
WHERE Id = @index
-- Toggle the constraint based on the @enable parameter
SET @sql = 'ALTER TABLE [' + @tableName + '] ' + CASE WHEN @enable = 0 THEN 'NOCHECK' ELSE 'CHECK' END + ' CONSTRAINT [' + @constraintName + ']'
PRINT @sql
-- EXEC sp_executesql @sql
SET @index = @index + 1
END
SET NOCOUNT OFF
Simply uncomment the EXEC line if you want to actually run the generated statements.
