>>>>>>>>>>>> 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
pgp8sbGvkijn9.pgp
Description: PGP signature
