db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Tue, 23 Aug 2005 10:17:43 GMT
>>>>>>>>>>>> Michael J. Segel wrote (2005-08-22 22:16:31):
> 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.

I think that this does not make sense. Code optimization for queries
would be impossible (or very difficult, becase the optimizer would
have to know the ALTER-history of the table and which values were in the
table at each change). 

Consider the table you have populated and the following statements

INSERT INTO Foo VALUES(4,"FOO");
ALTER TABLE Foo ADD CONSTRAINT c1 UNIQUE (BAR);
SELECT DISTINCT BAR FROM Foo;

An optimizer could ignore DISTINCT, bot not in this case since the
table actually contains duplicates. A lot of similar examples could be
constructed.

And, an application can not rely on metadata to say anything about the
values in the table.

So generally: if db ignores the values in the table when constraints
is added, optimizations becomes impossible and one whould have no or
little use of indexes and constraints (the constraint no longer says
something about the values in the table, so neither the opimizator nor
the application can assume any constraint on the values already there,
just on values added to the table after the constraint was added).
-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway

Mime
View raw message