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

If you know the name of your constraint then you can directly use the command like

alter table users drop constraint constraint_name;

If you don't know the constraint name, you can get the constraint by using this command

select constraint_name,constraint_type from user_constraints where table_name = 'YOUR TABLE NAME';

Answer 2

I had the same problem. I'm using DB2. What I have done is a bit not too professional solution, but it works in every DBMS:

1. Add a column with the same definition without the unique contraint.
2. Copy the values from the original column to the new
3. Drop the original column (so DBMS will remove the constraint as well no matter what its name was)
4. And finally rename the new one to the original
5. And a reorg at the end (only in DB2)

ALTER TABLE USERS ADD COLUMN LOGIN_OLD VARCHAR(50) NOT NULL DEFAULT '';
UPDATE USERS SET LOGIN_OLD=LOGIN;
ALTER TABLE USERS DROP COLUMN LOGIN;
ALTER TABLE USERS RENAME COLUMN LOGIN_OLD TO LOGIN;

CALL SYSPROC.ADMIN_CMD('REORG TABLE USERS');

The syntax of the ALTER commands may be different in other DBMS

Answer 3

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)

Answer 4

ALTER TABLE users
DROP CONSTRAINT 'constraints_name'

Answer 5

Use this SQL command to drop a unique constraint:

ALTER TABLE tbl_name
DROP INDEX column_name

Answer 6

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, . . . )