• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

index already exists

 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic