How to drop a unique constraint from table column?
Solution 1
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)
Solution 2
ALTER TABLE users
DROP CONSTRAINT 'constraints_name'
if earlier constraints_name is not provided, it will have some default constraint_name, in pgAdmin 4 (pSql), try violating the constraint and you can see the constraint_name being violated in the error received, most probably same must be the case with other platforms or there are some articles available over web where constraint_name is extracted from certain tables where they are stored, not sure about this though. P.S : Can take reference from comments also
Solution 3
This works mostly.
drop index IX_dbo_YourTableName__YourColumnName on dbo.YourTableName
GO
Solution 4
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.
The syntax would be:
ALTER TABLE table_name DROP UNIQUE (column1, column2, . . . )
Solution 5
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
SKINDER
Updated on July 19, 2022Comments
-
SKINDER almost 2 years
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_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...
Thanks.