creating an index on a column that has a UNIQUE constraint just fails silently?

[Stacey Brown <st...@yahoo.com>]

http://db.apache.org/derby/docs/10.3/ref/

In the section "CREATE INDEX statement" it says,

    "If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns."


I'm using derby 10.3.1.4 embedded in my application. When the application starts it drops indices and then recreates them. One of those indices was on a column that had a UNIQUE constraint, but the CREATE INDEX statement for that column completes. When I list all the indices though, I only find the index that backs the UNIQUE constraint. Although executing my CREATE INDEX statement didn't throw an exception, it didn't create the index.

I'm not trying to argue anything here and I'm not claiming expertise, but when I read "you can not create an index on those columns" I thought that I would get an exception if I attempted to create an index on a column that has a UNIQUE or PRIMARY KEY constraint.

My question is whether the behavior is intentional or not. Should I have gotten an exception when I executed a CREATE INDEX statement on a column that already had a UNIQUE constraint on it?




       
____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/  

Re: creating an index on a column that has a UNIQUE constraint just fails silently?

[Dy...@Sun.COM]

Stacey Brown <st...@yahoo.com> writes:

> http://db.apache.org/derby/docs/10.3/ref/
>
> In the section "CREATE INDEX statement" it says,
>
>     "If a column or set of columns has a UNIQUE or PRIMARY KEY
>     constraint on it, you can not create an index on those columns."
>
>
> I'm using derby 10.3.1.4 embedded in my application. When the
> application starts it drops indices and then recreates them. One of
> those indices was on a column that had a UNIQUE constraint, but the
> CREATE INDEX statement for that column completes. When I list all
> the indices though, I only find the index that backs the UNIQUE
> constraint. Although executing my CREATE INDEX statement didn't
> throw an exception, it didn't create the index.
>
> I'm not trying to argue anything here and I'm not claiming
> expertise, but when I read "you can not create an index on those
> columns" I thought that I would get an exception if I attempted to
> create an index on a column that has a UNIQUE or PRIMARY KEY
> constraint.

I tend to agree, but I'm not an expert. Unless someone tells you
otherwise in the next couple of days, I think you should file a jira
about this. See

http://db.apache.org/derby/DerbyBugGuidelines.html

If you could include a minimal test program that exhibits the problem
it will be much appreciated.

> My question is whether the behavior is intentional or not. Should I
> have gotten an exception when I executed a CREATE INDEX statement on
> a column that already had a UNIQUE constraint on it?

Seems unreasonable to silently ignore the user's request. At least you
should get a warning IMHO.

-- 
dt