This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan 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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 29219
    
135

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: 29219
    
135

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
 
Similar Threads
Can anyone help with query ?
Making schema changes to an Application
Hibernate SchemaUpdate problem
Foreig key must have same number of columns as the referenced primary key
Please explain me the hibernate generated sql queries in console