I need to remove a unique constraints that I don&#

问题:

I maintain a product that is installed at multiple locations which as been haphazardly upgraded. Unique constraints were added to a number of tables, but I have no idea what the names are at any particular instance. What I do know is the table/columnname pair that has the unique constraints and I would like to write a script to delete any unique constraint on these column/table combinations.

This is SQL Server 2000 and later. Something that works on 2000/2005/2008 would be best!

回答1:

This script would generate a list of ALTER TABLE..... DROP CONSTRAINT.... commands, which you can then copy+paste and execute (or tweak before executing as needed) to drop all unique constraints / unique indices:

SELECT 
    'ALTER TABLE ' + OBJECT_NAME(so.parent_obj) + ' DROP CONSTRAINT ' + so.name
FROM sysobjects so
WHERE so.xtype = 'UQ'

I hope it should work on all SQL Server versions from 2000 to 2008 R2.

回答2:

This is way trickier than it seems like it should be, I found a way that works for me - I believe it will only work on SQL Server 2005 or above. Here's the full scenario:

Table has been created with a unique constraint on a column, ex:

CREATE TABLE table_name (
    id bigint identity not null,
    column_name varchar(255) not null,
    primary key(id),
    unique (column_name)
);

Sometime later, it is discovered that this unique constraint is not desired.

INSERT INTO table_name(column_name) VALUES('col1');

results in: Violation of UNIQUE KEY constraint 'UQ__table_na__9FA0BA59160F4887'. Cannot insert duplicate key in object 'dbo.table_name'.

If you have manual control of this db and can running SQL directly on it is possible, just do:

ALTER TABLE table_name DROP CONSTRAINT UQ__table_na__9FA0BA59160F4887;

In my case, these scripts will have been run on different environments and the keys won't have identical names, so in order to remove the constraint I need SQL which takes the table and column name as input and figures out the rest.

DECLARE @table_name nvarchar(256)
DECLARE @col_name nvarchar(256)
DECLARE @Command  nvarchar(1000)

-- set your table and column name here:
SET @table_name = N'table_name'
SET @col_name = N'column_name'

SELECT @Command = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + d.name
    FROM sys.tables t
    JOIN sys.indexes d ON d.object_id = t.object_id  AND d.type=2 and d.is_unique=1
    JOIN sys.index_columns ic on d.index_id=ic.index_id and ic.object_id=t.object_id
    JOIN sys.columns c on ic.column_id = c.column_id  and c.object_id=t.object_id
    WHERE t.name = @table_name and c.name=@col_name

--if you want to preview the generated command before running
SELECT @Command

EXEC sp_executesql @Command;

That removes the unique constraint on the column and allows the insert to proceed.

回答3:

This page has a quick and dirty way to pull out all the CONSTRAINTs in the database, and from there you could build up dynamic SQL to drop them:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT';