unable to edit Index name?

Microsoft SQL Server

I am trying to edit the name of an Index being used to assign a Unique
Constraint on a SQL Server 2000 database.

When I try to rename this index, I get this message:
'VDivision' table
- Unable to create index 'IX_Description'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'IX_Description' in the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.

There is an index called IX_Description in a different table in this
same database but Books OnLine and the various knowledge bases and
books I can find all seem to indicate that an Index name must be
unique within a table... not within a database.

Is an Index name supposed to be unique within a database or do I have
something assigned that I can't find that is "occupying" the name I
want to use.

Thanks for any help you can offer.

Tim
What is the code you are using to perform the rename?


Ugh, where are you doing this from?
I am trying to use the MS SQL Enterprise Manager's GUI.

I think this path will show you where I am trying to do this from
1) go to the database on a server and open the TABLES node
2) right-mouse on the table I want to change and select DESIGN TABLE
3) right-mouse in the list of fields and select INDEXES/KEYS

you are now on a window labeled PROPERTIES with the INDEXES/KEYS tab
foremost. select the Index you want to rename and then type the new
name in the Index Name field and press CLOSE. X off the DESIGN TABLE
window and tell the change comfirmation window YES.

The error I've listed will then appear.

Tim
Use sp_rename, in Query Analyzer. Here is a repro that should get you
started:



CREATE TABLE dbo.foo
(
id INT
)
GO

CREATE INDEX blob ON foo(id)
GO

sp_helpindex foo
GO

EXEC sp_rename 'foo.blob', 'blat', 'index'
GO

sp_helpindex foo
GO

DROP TABLE dbo.foo
GO
So the Enterprise Manager can't even handle renaming an index?
I'm impressed.

Your code worked with a slight tweek:

EXEC sp_rename 'VDivision.IX_VDivision', 'VDivision.IX_Description',
'index'

worked but
EXEC sp_rename 'VDivision.IX_VDivision', 'Description', 'index'

blew apart with a message about creating a duplicate index....

So, now I've got an index called VDivision.IX_VDivision......
Hardly what I want

I think I'm missing something really fundamental here.
I've removed the index in question entirely from the table.
Now, when I try to add a new index using the GUI, I get this message

'VDivision' table
- Unable to create index 'IX_Description'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'IX_Description' in the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.

Am I missing something fundamental in that an Index name has to be
unique across the entire database and not the table.. or is the GUI
interface simply not reliable... or do I have a bug somewhere deep and
dark....?

Tim
I've never been impressed with Enterprise Manager. There are many reasons
it has been completely replaced in the next version. For more of my
complaints, and which functions I *do* use it for, see
http://www.aspfaq.com/2455


I didn't have to give the table name as part of the index name. Sounds like
you might actually have an existing index on description? Is it possible
you have two tables called VDivision, one owned by dbo, and one owned by
another owner (or one without an owner, which can happen in obscure
scenarios)? Here is another repro which should prove to you that you
certainly can have multiple indexes with the same name, as long as they are
on different tables, within the same database:

CREATE TABLE dbo.foo
(
id INT
)
GO

CREATE TABLE dbo.blat
(
id INT
)
GO


CREATE INDEX blob ON foo(id)
GO
CREATE INDEX blat ON blat(id)
GO

EXEC sp_helpindex foo
EXEC sp_helpindex blat
GO

EXEC sp_rename 'foo.blob', 'blat', 'index'
GO

EXEC sp_helpindex foo
EXEC sp_helpindex blat
GO

DROP TABLE dbo.foo, dbo.blat
GO

And here is a repro which shows how you can get this error if you have two
tables and, if you don't specify the owner name, you might be generating the
index on the wrong table:

CREATE TABLE guest.blat
(
id INT
)
CREATE TABLE dbo.blat
(
id INT
)

CREATE INDEX bob ON guest.blat(id)
GO

CREATE INDEX bob ON dbo.blat(id)
GO

CREATE INDEX bob ON blat(id)
GO
Thank you.
That is good practical stuff to look at. A number of your
comments mirror my own (with the Enterprise Manager being awkward and
harder to work with than is tolerable).

I can't find another index on Description... but I also think I
may have tangled you up in my bad terminology.
I am in fact not just generating an index, but am generating a
Unique Constraint using the Index area.
I can create an index with this name at will, but when I try to
add the unique constraint option... it goes down on me.
So... my problem is really one of generating a Unique Constraint.
Sorry for the confusion, but I'm not sure I've really gained
anything. It looks to me like a Constraint name also just needs to be
unique within a table... not the database (based upon what Books
Online and other sources say)... but that is not the way the system is
behaving.
I can
1) delete the existing Unique Constraint Index I have on VDepartment
2) ceate the constraint Index on VDivision with the name I want
3) try to recreate the Unique Constraint Index I had on VDepartment
and run into basically the same error message I saw before....
So, I can reverse the tables from what I had and the problem
duplicates itself.

The owner for the two tables that seem to be "clashing" is dbo.

I seem to have a fundamental comprehension problem
somewhere...damned if I can see it.

Tim