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'
SET @ConstraintName =
(SELECT
TOP 1 o.name FROM o
JOIN c
ON o.id = c.cdefault
JOIN t
ON c.id = t.id
WHERE o.xtype = 'd'
AND c.name = @ColumnName
AND t.name = @TableName)
SET @DropConstraintSQL = 'ALTER
TABLE ' + @TableName + ' DROP ' + @ConstraintName
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.