db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Tue, 23 Aug 2005 17:27:49 GMT
Is there any database system out there that ignores current data
when adding a constraint to the table?  It seems almost all applications
which require a constraint to be added, will fail if "some" of the
data does not meet the constraint.

For Derby's implementation of uniqueness constaint this just won't work.
When the constraint is added a unique index is created on the existing
data and it will fail when it hits a duplicate.

Michael J. Segel wrote:

> 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. ;-]

Mime
View raw message