How to drop a unique constraint from table column?

Question

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.

Answer 1

This statement works for me

  ALTER TABLE table_name DROP UNIQUE (column_name);

Answer 2

I have stopped on the script like below (as I have only one non-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.

Answer 3

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 Hash value in it's name. So problem is this HASH is varies in different Machine or Database. For example DF__Companies__IsGlo__6AB17FE4 here 6AB17FE4 is the hash value(8 bit). So I am referring a single script which will be fruitful to all

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 DROP command to drop the column.

Answer 4

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__3213E83EB607700F is the name of unique key which was created on a particular column on test table.

Answer 5

FOR SQL to drop a constraint

ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [unique key created by sql] GO

alternatively: go to the keys -- right click on unique key and click on drop constraint in new sql editor window. The program writes the code for you.

Hope this helps. Avanish.

Answer 6

This works mostly.

drop index IX_dbo_YourTableName__YourColumnName on dbo.YourTableName
GO