db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Segel" <mse...@segel.com>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Tue, 23 Aug 2005 03:16:31 GMT
On Monday 22 August 2005 15:33, Susan Cline wrote:
> In the 'Create Index' statement documentation of the 10.1 Reference Guide
> this statement is made about creating unique indexes:
>
> Indexes and constraints
>
> Unique, primary key, and foreign key constraints generate indexes that
> enforce or "back" the constraint (and are thus sometimes called backing
> indexes). 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.
>
> However, this SQL succeeds and does create two unique indexes on the same
> column:
>
> First I ran this SQL:
>
> CREATE TABLE APP.table5 (
>   col1 CHAR(3) NOT NULL,
>   col2 INTEGER,
>   col3 VARCHAR(28) NOT NULL);
>
> Then I created a unique index called idx1 first:
>
> CREATE UNIQUE INDEX "APP"."idx1" ON "APP"."TABLE5" ("COL1" ASC);
>
> Then I added  a primary key:
>
> ALTER TABLE "APP"."TABLE5" ADD CONSTRAINT "myprimkey" PRIMARY KEY (COL1);
>
> This added an additional unique index, a backing index:
>
> CREATE UNIQUE INDEX "APP"."SQL050822005949400" ON "APP"."TABLE5" ("COL1"
> ASC);
>
> So either this is a Derby bug, and the second index should not be
> created, or the documentation needs to be updated to say that if a unique
> index exists, but a primary key is added to a column with a unique index it
> will succeed.
>
Its not a bug.
> If I first create a primary key, which subsequently adds the backing index,
> then I try to add the unique index it does fail.  This behaviour does jive
> with the documentation.
>
Yes and no.

Yes, this is what is supposed to happen.
No, while it doesn't "jive", its not to say that the documentation is 
incorrect... ;-)

What you're looking at is a design issue.

There's a couple of issues at work. The ALTER TABLE statement, indexes and 
constraints.

Consider this... You create a table foo, with two columns, bar and retz.
bar is an integer, retz is a character string.  You populate the table so it 
looks like this:

Foo:

BAR	RETZ
1	"abc"
2	"def"
3	NULL
4	"jam"
...
Now you then apply an ALTER TABLE command to restrict column RETZ from 
containing NULLS.

The question is... should the ALTER TABLE command fail because you've already 
added rows that have NULL?

Ok, that was a rhetorical question. The answer is no.  The ALTER TABLE command 
should succeed and any new data added would have to pass that constraint on 
RETZ to see if the value is NOT NULL. So if you insert the pair (10, NULL), 
it would get rejected, however the data pair (3, NULL) can exist within the 
table, just don't update that row. ;-)

In general the design issue is that when you apply a constraint to the table, 
you ignore the current data that resides in the table, and only apply the 
constraint to data that has been added after you altered the table.

The same can be said for the indexes. 
This is really a design issue. KISS applies. ;-) 
At some point, you have to trust that you have a real DBA and that he/she 
knows what they are doing. ;-)

Does that make sense? 

I guess if you want to get fancy, you could set up some logic that would check 
to see if the index already exists and then utilizes that index and just adds 
the constraint, but as I said, KISS applies. Why over complicate things?
(What do you think this is, DB2?) 
[Yeah, I know.  Gumby is back. ;-) If anyone from IBM wants to give me a hard 
time, you've got my e-mail address. ;-]
-- 
Michael Segel
Principal
MSCC
(312) 952-8175

Mime
View raw message