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.
This question is related to
sql
database
sql-server-2005
unique-constraint
non-clustered-index
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.
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.
ALTER TABLE dbo.table
DROP CONSTRAINT uq_testConstrain
constraint name uq_testConstrain
can be found under database->table->keys folder
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.
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
Use this SQL command to drop a unique constraint:
ALTER TABLE tbl_name
DROP INDEX column_name
ALTER TABLE users
DROP CONSTRAINT 'constraints_name'
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, . . . )
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';
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:
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
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
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.
This statement works for me
ALTER TABLE table_name DROP UNIQUE (column_name);
This works mostly.
drop index IX_dbo_YourTableName__YourColumnName on dbo.YourTableName
GO
Source: Stackoverflow.com