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

User · Answer

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

User · Answer

ALTER TABLE users DROP CONSTRAINT  constraints name

User · Answer

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

User · Answer

To find all system generated unique constraint names and other information related to it on any database    You may use below query and enhance it as per your need   SELECT   FROM INFORMATION SCHEMA CONSTRAINT COLUMN USAGE WHERE CONSTRAINT NAME LIKE   UQ     Final query to drop all unique constraint through database  You may add where clause to restrict it to one table       DECLARE  uqQuery NVARCHAR MAX      SET  uqQuery   SUBSTRING   SELECT         ALTER TABLE      Table Schema       Table Name              DROP CONSTRAINT    CONSTRAINT NAME          FROM INFORMATION SCHEMA CONSTRAINT COLUMN USAGE WHERE CONSTRAINT NAME LIKE   UQ       FOR XML PATH       2   2000000      SELECT  uqQuery

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use this SQL command to drop a unique constraint   ALTER TABLE tbl name DROP INDEX column name

User · Answer

This statement works for me    ALTER TABLE table name DROP UNIQUE  column name

User · Answer

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

User · Answer

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    Add a column with the same definition without the unique contraint  Copy the values from the original column to the new Drop the original column  so DBMS will remove the constraint as well no matter what its name was  And finally rename the new one to the original 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

User · Answer

ALTER TABLE dbo table DROP CONSTRAINT uq testConstrain  constraint name uq testConstrain can be found under database- gt table- gt keys folder

User · Answer

This works mostly   drop index IX dbo YourTableName  YourColumnName on dbo YourTableName GO

User · Answer

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

User · Answer

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

[sql] How to drop a unique constraint from table column?

Examples related to sql

Examples related to database

Examples related to sql-server-2005

Examples related to unique-constraint

Examples related to non-clustered-index