This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes index already exists Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "index already exists " Watch "index already exists " New topic
Author

index already exists

Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
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


Regards, Dave Brown
SCJP 6 - [url]http://www.dbws.net/[/url] - Check out Grails Forum
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30405
    
150

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)"


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
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

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30405
    
150

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: index already exists