• 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
Sheriff
Posts: 36017
427
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
Sheriff
Posts: 36017
427
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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!