Can not drop UNIQUE index from table

Question

When I run this query

ALTER TABLE "dbo"."ROOM" DROP INDEX "UNIQUE";

I got this message:

Error 1018: Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

The name of the unique index is, justly, UNIQUE. I think that is the problem, and it's an autogenerated name (for the SQL Server client that was used to create this index). This is the create table sentence:

CREATE TABLE "ROOM" (
    "ID" BIGINT NOT NULL DEFAULT NULL,
    //the rest of the columns...
    "ROOM" VARCHAR(100),
    UNIQUE INDEX "UNIQUE" ("ROOM")
)
;

Any idea how can I remove this index? I know I can drop the table, and create it again, I want to avoid that.

Debbee

"The name of the unique index is, justly, UNIQUE.". That is an awful name for the index. I would use idx_room_room, or idxu_room_room for the index name. – Weinhardt

Why do you have the column ID defined as NOT NULL with a default of NULL???? That makes absolutely no sense at all. This thing screams of being in serious need of a total redesign. – Appositive

"and it's an autogenerated name": I am willing to bet just about anything that SQL Server did not auto-generate the name "Unique" for your index. – Zabaglione

Absolutely right. But that create table maybe is being autogenerated by the sql server client that I'm using.. – Debbee

Answer 1

You need to use this statement:

DROP INDEX [UNIQUE] ON dbo.Room 

You need to drop the index, and also define which table it's been created on ... and since its name is a reserved SQL keyword, you need to put that into square brackets ([UNIQUE]).

See the official MSDN documentation on DROP INDEX for more details

Update: if this statement doesn't work, then that index isn't called UNIQUE after all.

Check what indexes are defined on the Room table with this statement:

SELECT * 
FROM sys.indexes 
WHERE object_id=OBJECT_ID('dbo.Room')

and look at the Name column - then use the appropriate, actual index name to drop that index.

Update #2: OK so you really have an unique constraint, which is enforced by that unique index. So in order to get rid of that, first you need to find out what that constraint is called, and which table it's on:

SELECT 
    name, 
    TableName = OBJECT_NAME(parent_object_id)
FROM sys.key_constraints
WHERE type = 'UQ'

Once you have these two pieces of information, you can now drop that constraint:

ALTER TABLE (TableName)
DROP CONSTRAINT (ConstraintName)

and then your unique index will be gone as well.

Woodwork

Then check what those indexes on Room are really called with this command: SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('dbo.Room') - I doubt that the index name really is UNIQUE .... use the appropriate, actual index name .... – Woodwork

Saying "Didn't worked" is a lot less helpful than saying what error message or other unexpected result you got. – Zabaglione

The same error Tab Alleman. I tried to bring up the indexes names. And the names are not very descriptive. I'm not sure which one should I delete. I can see there is a column named "is_unique_constraint", and only one is set to true. Maybe that one is the one I'm looking for? – Debbee

I tried to remove that one which shows "is_unique_constraint" = true, and I can't. The sql server doesn't allow me to do that, because it says that index enforces the unique key constraint. I removed another one, but when I try to insert a duplicate entry in the table, the unique constraint pops up again. – Debbee

Well, if your unique index enforces an unique constraint, you would have to find out what that constraint is (its name), and then drop that constraint (not the index). – Woodwork

@Perimosh: updated my response to include steps how to find out what unique constraints you have, and how to drop that (which will also drop the unique index, in the background) – Woodwork

You rock man! So many thanks! Worked. A little correction: SELECT name, TableName = OBJECT_NAME(parent_object_id) FROM sys.key_constraints WHERE type = 'UQ' and parent_object_id = OBJECT_ID('dbo.name_of_table') – Debbee

Answer 2

The first step, Get indexes

select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type],
    case when c.[type] = 'PK' then 'Primary key'
        when c.[type] = 'UQ' then 'Unique constraint'
        when i.[type] = 1 then 'Unique clustered index'
        when i.type = 2 then 'Unique index'
        end as constraint_type, 
    c.[name] as constraint_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    i.[name] as index_name,
    case when i.[type] = 1 then 'Clustered index'
        when i.type = 2 then 'Index'
        end as index_type
from sys.objects t
    left outer join sys.indexes i
        on t.object_id = i.object_id
    left outer join sys.key_constraints c
        on i.object_id = c.parent_object_id 
        and i.index_id = c.unique_index_id
   cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        and ic.index_id = i.index_id
                            order by col.column_id
                            for xml path ('') ) D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1 and t.[name]='table name'
order by schema_name(t.schema_id) + '.' + t.[name]

The second step, drop indexes

DROP INDEX [INDEXES NAME] ON dbo.[TABLE NAME] 

Chingchinghai