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.

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.