如何从表列中删除唯一约束？ [英] How to drop a unique constraint from table column?

问题描述

我有一个'users'与'login'列定义为：

I have a table 'users' with 'login' column defined as:

[login] VARCHAR(50) UNIQUE NOT NULL

现在我想使用SQL脚本删除这个唯一的约束/索引。我在我的本地数据库中找到了其名称 UQ_ users _7D78A4E7 。但是我想它在另一个数据库上有不同的名称。

Now I want to remove this unique constraint/index using SQL script. I found its name UQ_users_7D78A4E7 in my local database but I suppose it has a different name on another database.

删除此唯一约束的最佳方法是什么？或至少任何...

What is the best way to drop this unique constraint? Or at least any...

感谢。

推荐答案

SKINDER ，你的代码不使用列名。正确的脚本是：

SKINDER, your code does not use column name. Correct script is:

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

set @table_name = N'users'
set @col_name = N'login'

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

print @Command

--execute (@Command)

这篇关于如何从表列中删除唯一约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！