index already exists

[Dave Brown]

Hi All,

I'm not sure whether i'm running into a problem with hsqldb or more simply my understanding of SQL indexes is to pot.

I've got a number of table in a hsqldb, i.e DIVES, LOCATIONS

My Locations table as a column called Location_ID which is its primary key. The DIVES table also has a column called Location_ID, so I know at which location the Dives was at.

I want to create an index of the Location_ID in the dives table but when I do a "CREATE INDEX Location_ID on DIVES(Location_ID)"

I get back an "index already exists" error, it definately is NOT an index in the DIVES table. This is confusing me completely. I'm using DBVIsualizer as a GUI to maintain my HSQLDB tables.

Can anyone shed any light on why I'm getting this message back ?

Thanks

[Jeanne Boyarsky]

Dave,
You can only create one index with the same name. Presumably LOCATION_ID is the name of the index on the LOCATIONS table. Try:
"CREATE INDEX Location_ID_FK on DIVES(Location_ID)"

[Dave Brown]

Ahh ok, Thanks.

Is this just hsqldb behaviour or general SQL ?

I thought the indexes belong to each table, therefore you could use the same name across a whole database.

[Paul Sturrock]

Is this just hsqldb behaviour or general SQL?

General SQL I think. Database objects are usually named within the context of the schema they exist in.

[Jeanne Boyarsky]

Paul is correct that it is not specific to hsqldb. It's not general SQL either though as SQL is a language. It would be more accurate to say that it applies to most if not all database implementations.