index already exists

Thread view:

Dave Brown - 22 Jul 2005 09:44 GMT

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,

Dave.

Reply to this Message

Bjorn Abelli - 22 Jul 2005 10:55 GMT

"Dave Brown" wrote...

> I've got a number of table in a hsqldb, i.e DIVES, LOCATIONS
>
[quoted text clipped - 10 lines]
>
> Can anyone shed any light on why I'm getting this message back ?

So what is the name of the index of Location_ID on LOCATIONS?

Probably Location_ID, isn't it?

I suggest you simply try:

"CREATE INDEX Dives_Location_ID on DIVES(Location_ID)"

i.e. give the index a different name than the existing one...

// Bjorn A

Reply to this Message

Dave Brown - 22 Jul 2005 12:25 GMT

Ahh I see, Would I have to specifically reference that new index name
in my select statements or would the database engine use it automatically.

Dave

> "Dave Brown" wrote...
>
[quoted text clipped - 24 lines]
>
> // Bjorn A

Reply to this Message

Bjorn Abelli - 22 Jul 2005 14:52 GMT

"Dave Brown" wrote...

> Ahh I see, Would I have to specifically reference that new
> index name in my select statements or would the database
> engine use it automatically.

You shouldn't need to reference that index, as any DB with good optimization
rules should make use of any existing index if it can improve performance of
a select statement.

I don't know the inner workings of hsqldb, but what I can see after a quick
glance on its documentation, it should work just fine, without specific
references to the indexes.

// Bjorn A

Reply to this Message

Dave Brown - 22 Jul 2005 15:44 GMT

> "Dave Brown" wrote...
>
[quoted text clipped - 11 lines]
>
> // Bjorn A

Reply to this Message

Dave Brown - 22 Jul 2005 15:45 GMT

Ahh I see, Thanks Bjorn.

> "Dave Brown" wrote...
>
[quoted text clipped - 11 lines]
>
> // Bjorn A

Reply to this Message

index already exists

Free Magazines