SQL 如何从表列中删除唯一约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5499574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop a unique constraint from table column?
提问by SKINDER
I have a table 'users' with 'login' column defined as:
我有一个表“用户”，其中“登录”列定义为：
[login] VARCHAR(50) UNIQUE NOT NULL
Now I want to remove this unique constraint/index using SQL script. I found its name UQ_users_7D78A4E7in my local database but I suppose it has a different name on another database.
现在我想使用 SQL 脚本删除这个唯一的约束/索引。我在本地数据库中找到了它的名字UQ_ users_7D78A4E7但我想它在另一个数据库上有一个不同的名字。
What is the best way to drop this unique constraint? Or at least any...
删除此唯一约束的最佳方法是什么？或者至少任何...
Thanks.
谢谢。
回答by Avadhesh
ALTER TABLE users
DROP CONSTRAINT 'constraints_name'
回答by Rail
SKINDER, your code does not use column name. Correct script is:
SKINDER，你的代码没有使用列名。正确的脚本是：
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)
回答by fluidguid
This works mostly.
这主要有效。
drop index IX_dbo_YourTableName__YourColumnName on dbo.YourTableName
GO
回答by juliG
To drop a UNIQUE constraint, you don't need the name of the constraint, just the list of columns that are included in the constraint.
要删除 UNIQUE 约束，您不需要约束的名称，只需要包含在约束中的列的列表。
The syntax would be:
语法是：
ALTER TABLE table_name DROP UNIQUE (column1, column2, . . . )
回答by beginner
Use this SQL command to drop a unique constraint:
使用此 SQL 命令删除唯一约束：
ALTER TABLE tbl_name
DROP INDEX column_name
回答by Vaseph
This statement works for me
这个声明对我有用
ALTER TABLE table_name DROP UNIQUE (column_name);
回答by sikarnarender
You can use following script :
您可以使用以下脚本：
Declare @Cons_Name NVARCHAR(100)
Declare @Str NVARCHAR(500)
SELECT @Cons_Name=name
FROM sys.objects
WHERE type='UQ' AND OBJECT_NAME(parent_object_id) = N'TableName';
---- Delete the unique constraint.
SET @Str='ALTER TABLE TableName DROP CONSTRAINT ' + @Cons_Name;
Exec (@Str)
GO
回答by SKINDER
I have stopped on the script like below (as I have only onenon-clustered unique index in this table):
我已经停止了像下面这样的脚本（因为我在这个表中只有一个非聚集唯一索引）：
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
where t.name = @table_name and d.type=2 and d.is_unique=1
--print @Command
execute (@Command)
Has anyone comments if this solution is acceptable? Any pros and cons?
有没有人评论这个解决方案是否可以接受？有什么优点和缺点吗？
Thanks.
谢谢。
回答by Jason Clark
Expand to database name >> expand to table >> expand to keys >> copy the name of key then execute the below command:
扩展到数据库名称>>扩展到表>>扩展到键>>复制键的名称然后执行以下命令：
ALTER TABLE Test DROP UQ__test__3213E83EB607700F;
Here UQ__test__3213E83EB607700Fis the name of unique key which was created on a particular column on test table.
这里UQ__test__3213E83EB607700F是在测试表的特定列上创建的唯一键的名称。
回答by gdmanandamohon
I would like to refer a previous question, Because I have faced same problem and solved by this solution.
First of all a constraint is always built with a Hashvalue in it's name. So problem is this HASHis varies in different Machine or Database. For example DF__Companies__IsGlo__6AB17FE4here 6AB17FE4is the hash value(8 bit). So I am referring a single script which will be fruitful to all
我想参考以前的问题，因为我遇到了同样的问题并通过此解决方案解决了。首先，约束总是Hash在其名称中包含一个值。所以问题是这HASH在不同的机器或数据库中有所不同。例如DF__Companies__IsGlo__6AB17FE4这里6AB17FE4是哈希值（8 位）。所以我指的是一个对所有人都有益的单一脚本
DECLARE @Command NVARCHAR(MAX)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N'ProcedureAlerts'
set @col_name = N'EmailSent'
select @Command ='Alter Table dbo.ProcedureAlerts Drop Constraint [' + ( select d.name
from
sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where
t.name = @table_name
and c.name = @col_name) + ']'
--print @Command
exec sp_executesql @Command
It will drop your default constraint. However if you want to create it again you can simply try this
它将删除您的默认约束。但是，如果您想再次创建它，您可以简单地尝试这个
ALTER TABLE [dbo].[ProcedureAlerts] ADD DEFAULT((0)) FOR [EmailSent]
Finally, just simply run a DROPcommand to drop the column.
最后，只需运行一个DROP命令来删除列。
