Friday, September 11, 2009

How To Drop An Unnamed Constraint on MS SQL Server

This document is copied from http://www.nonhostile.com/howto-drop-unnamed-constraint-in-sql-server.asp

In SQL Server, Constraints are rules that can be added to the columns of a table.
There are several types of constraint:

Primary Key: 
Identifies the table's primary key column(s)

Unique: 
Makes sure that values are not duplicated in the column

Foreign Key: 
Used to enforce referential integrity

Check: 
Places additional logic checks on a column, for example, cannot be zero

Default: 
Provides a default value used when a value is not provided for the column in an INSERT statement

In SQL Server, when you create a constraint you can specify a name. If you do not specify a name, SQL Server will allocate its own random name. This is very nice, but because the names are random, they vary from one database to another. This causes problems for maintenance script writers who may need to drop the constraint to make a change to the underlying column.
This T-SQL fragment creates an unnamed DEFAULT constraint on the MyColumn column of the MyTable table.

ALTER TABLE [MyTable]
ADD CONSTRAINT DEFAULT (0) FOR [MyColumn]

It should be considered as good practice to always specify constraints with a name, like below.

ALTER TABLE [MyTable]
ADD CONSTRAINT [CONSTRAINT_NAME] DEFAULT (0) FOR [MyColumn]

If you do not specify a name, SQL will generate a random name built from a prefix which denotes the type of constraint, and partial table and column names followed by a random alphanumeric string. It would normally look something like this:

    DF__MyTable__MyColu__3F3159AB

If you are unfortunate enough to have to deal with a database that contains unnamed constraints and you need to drop them then you'll need to find the name of the constraint that you want to drop.


The first section of the script below uses SQL's own system tables to interrogate the database schema. It finds the constraint using the table name and a wildcard 'like' search using the piece of the constraint name that SQL Server will always generate. You will need to modify this part of the query with your table name and partial constraint name. The next step is to builds a dynamic SQL statement to drop the constraint using its actual name. The statement is then executed using the system stored procedure: sp_executesql

declare @name nvarchar(32), 
@sql nvarchar(1000)

-- find constraint name
select @name = O.name
from sysobjects AS O
left join sysobjects AS T
on O.parent_obj = T.id
where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0
and O.name not like '%dtproper%'
and O.name not like 'dt[_]%'
and T.name = 'MyTable'
and O.name like 'DF__MyTable__MyColu%'

-- delete if found
if not @name is null
begin
select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'
execute sp_executesql @sql
end

-- do your ALTER TABLE here

-- replace the constraint
select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'
execute sp_executesql @sql


Hope this helps :-)



 



The code above does not test successfully. The code below works for similar changes.



DECLARE @name nvarchar(255), @SQL VARCHAR(4000)
SET @SQL = 'ALTER TABLE TIMPORTMAPPINGS DROP CONSTRAINT |ConstraintName| '

SELECT @name = name FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = OBJECT_ID('TIMPORTMAPPINGS')

IF not @name IS NULL
BEGIN
SET @SQL = REPLACE(@SQL, '|ConstraintName|', @name)
EXEC (@SQL)
END

ALTER TABLE TIMPORTMAPPINGS ADD PRIMARY KEY (UIDPK);

No comments:

Post a Comment