Skip Navigation Links
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.