Electric Harbour

Articles Danish Alt Codes Finnish Alt Codes French Alt Codes Norwegian Alt Codes Spanish Alt Codes Scandanavian Alt Codes Swedish Alt Codes Bulk Identity Insert SQL Server Constraints Visual Studio Shortcuts LINQ Cast Operator LINQ Group By Example LINQ Not In Example LINQ Order By Example Tools CSV String Generator About Curriculum Vitæ Contact

Dropping SQL Server Constraints When the Constraint Name is Unknown

Introduction:

Several times I've encountered databases in SQL Server 2000 where multiple databases with the same structure have constraints with different names. This article will describe how the problem occurs and how to drop the constraints without knowing beforehand what the constraint is named.

The Problem:

This situation occurs when a table column is created without explicitly naming the constraint. Consider the following SQL:

CREATE TABLE table1
(
     field1 INT NOT NULL DEFAULT 0
)

If you run this script on two different databases SQL Server may create two different names, the first may be named DF__table1__field1__7E6XX123, the second may be named DF__table1__field1__7E6CC920. Although this may not be a problem in the near term, later you may need to drop this column. Before you can drop a column you must drop any constraints on the column, but in order to drop the constraint you must know the name of the constraint. This will be an issue if you have to apply changes on many databases.

Before I move onto the soltuion it may be useful to note how to create a table where the default constraint name is defined. Consider the following SQL:

CREATE TABLE table1
(
     field1 INT NOT NULL CONSTRAINT DF_TABLE1_FIELD1 DEFAULT (0)
)

This will create a default constraint on field1 named DF_TABLE1_FIELD1. This follows the naming convention DF_[table name]_[column name], where DF is an abbreviation for default, but of course you should always try to match the naming conventions that are used in the database you are working on.

The Solution:

The solution I use is to write a simple script that will be ran on each database. First a query is performed to get the constraint name, then a dynamic query string is built and executed. Below is a script that performs these tasks. To use it just change the @TableName and @ColumnName variables to match the database the script will be performed on.

DECLARE @TableName AS NVARCHAR(255),
     @ColumnName AS NVARCHAR(255),
     @ConstraintName AS NVARCHAR(255),
     @DropConstraintSQL AS NVARCHAR(255)

SET @TableName = 'Table1'
SET @ColumnName = 'Field1'

--Get the name of the constraint that will be dropped
SET @ConstraintName =
     (SELECT TOP 1 o.name FROM sysobjects o
     JOIN syscolumns c
     ON o.id = c.cdefault
     JOIN sysobjects t
     ON c.id = t.id
     WHERE o.xtype = 'd'
     AND c.name = @ColumnName
     AND t.name = @TableName)

--Build a query string that will drop the constraint
SET @DropConstraintSQL = 'ALTER TABLE ' + @TableName + ' DROP ' + @ConstraintName

--Execute the sql to drop the constraint
EXEC (@DropConstraintSQL)

So this is a simple solution for an unecessary but common problem. The best solution is to just name the constraints in the first place, so remember SQL administrators it's good practice to always name your constraints.