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 Thu, 25 Aug 2005 19:15:14 GMT
On Thursday 25 August 2005 09:25, Daniel John Debrunner wrote:
> Michael J. Segel wrote:
> > On Wednesday 24 August 2005 21:50, Jean T. Anderson wrote:
> > This post may require the readers donning flame retardant clothing. ;-)
> It seems to me that Susan and Michael are discussing different aspects
> of constraints and maybe that is where the confusion is coming in.
Uhm not exactly.

Before I begin, let me be clear that while I am not flaming anyone or trying 
to start a flame war, I sense that some may have misinterpreted my comments.
Hence my warning about flame retardant clothing. :-) 

Ok, lets recap.

Susan indicated that she saw what she thought was an inconsistency on how a 
constraint worked on an index. (HINT: UNIQUE or PRIMARY KEY specifications is 
a CONSTRAINT. ) She thought that 1) This was a bug. and 2) That the 
documentation should better explain how this can occur.

What she saw was not a bug or an inconsistency at all. Constraints only impose 
their rules on an object after they have been invoked.  

I chose the example of altering a column in a table that accepted NULLS to now 
not except NULLS. If you then do a select on the table, you will see that 
NULLS can still exist in the table.  This would show the behavior of a 
constraint without having to deal with Indexes and also implied constraints. 
This is actually the simplest examples of implementing a constraint....

So, yes, I'm talking about the behavior in a more general sense. Why? Because 
if you understand the underlying principles in general terms, you can better 
understand what occurs when they are applied in a specific situation.

With respect to the "backing index" being created, that appears to be a design 
consideration. Not having written Cloudscape, I would guess that the 
developers thought that rather than trying to check and co-op an existing 
index which requires more overhead in Index management, it would be simpler 
just to create a new index associated with the constraint.  That is a design 
issue, however it would still not change the result set that Susan tried.
(There's more too this, but we won't go there... ;-)

As I had said earlier, the idea of checking multiple databases on this sort of 
behavior would indicate if this were indeed a bug or the correct and 
anticipated result.  

With respect to documentation, I disagree with what Susan and Jean would like 
to see changed. Since the issue really isn't a bug and that there would most 
likely be a better way to cover this issue. 

Let me ask this...(rhetorical question) On a scale of 1-10 (lowest to 
highest), how would you rate your database knowledge.

On a scale of 1-10, what is the target audience of the reference docs?

Maybe I'm off base cause I'm thinking you're writing to a level 5 skill set 
and any level 3 DBA would understand the costs associated with indexes and 
basic relational design, so why gum up the works? ;-)

Does that make sense?

Oh and BTW, if you read c.d.i, I tend to hang around the OTC types so please 
forgive my manner. ;-)


> Here's my perception of what the two of them are talking about.
> Susan is talking about the mechanics of constraints where a backing
> index is automatically created. Susan noticed some inconsistency in the
> way that Derby tries to ensure the user/application does not create an
> index that will be redundant due to the backing index.
> Michael is talking about the behaviour of constraints and is stating
> that if a constraint is added to a table with rows (using ALTER TABLE)
> then the constraint will be added successfully even if there are rows
> that do fail the constraint.
> I actually fail to see where any conflict is, Susan's discussion and
> proposed text doesn't seem to have anything to do with constraints on
> existing tables with failing rows.
> I know that this disallowing of creating an index in such a case is only
> for performance reasons, not any behaviour reasons. We wanted to avoid
> having multiple physical index on the same columns, thus wasting space
> and slowing DML.
> Michael, I do think you need to step back, and re-read the e-mails, you
> are challenging Susan's understanding on the issue you are talking about
> and as far as I can tell, Susan hasn't even discussed that issue.
> Also Michael, you are challenging Susan to run tests on other databases
> to prove your assertion, but that's not her 'itch to scratch', so she
> has no reason to do such a thing. In fact you assertion seems incorrect
> and is not the way Derby behaves. Having a constraint that cannot be
> guaranteed seems to be of little value to applications. Thus if you want
> to show that Derby is wrong here, or not in line with other databases,
> or some standard, then that's your 'itch to scratch' and you should be
> running the tests on other databases to back up your assertion.
> Am I missing something?
> Dan.

Michael Segel
(312) 952-8175

View raw message