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 Fri, 26 Aug 2005 13:12:12 GMT
On Thursday 25 August 2005 16:00, Dan Scott wrote:

> Sorry, that was a slightly bogus example; the addition of the primary
> key constraint failed because the column wasn't defined as null, not
> because the column already contained null values. A better example is:
> dan@localhost ~ $ db2 'alter table testy add constraint null_1 check
> (id is not null)'
> DB21034E  The command was processed as an SQL statement because it was not
> a valid Command Line Processor command.  During SQL processing it returned:
> SQL0544N  The check constraint "NULL_1" cannot be added because the table
> contains a row that violates the constraint.  SQLSTATE=23512
> Same result, different SQLCODE and SQLSTATE :)
> Dan
I'll be the first to admit that the bath tub gin did in fact rot my brain. ;-)

Using this as an excuse to get DB2 and Informix loaded on to my DB Server, I 
did a simple test from the Command Center in DB2.
(DB2 V8.2 running on SuSE Linux 9.3 x86 32 bit version)
Created a table foo, with two columns, bar and retz. Retz could accept NULLS.

Instead of adding the constraint, I just altered the table.
(yeah I know. Call me old fashion. Goes back to my Informix daze when SE was 
out and Turbo was brand new. ;-)

Interestingly enough, ALTER creates a temp, copies data from table A to Temp, 
drops Table A, and then rebuilds the new Table A and reloads from Temp. When 
I did this, I got all of the rows, but the one that contained a null. When I 
altered the table back to allow nulls, I could add a row with a null and 
there was no problem....

I'll have to check to see if auto commit was turned on or not.

I'll have Informix up some time today and I'll try this test there.
Also I'll try Susan's original test.

And to Jean's point. 
The reason you test the SQL syntax against another database is to help to 
determine if you truly have a bug or not. Simple rule of thumb. 90% of the 
time, what you think is a bug, is really something that you did wrong.

In this case, if a constraint is rolled back, then when you try your indexes, 
you should see an error when you try to make a primary Key. 

In other databases you may not, if the index that already exists gets 
co-opted, or if the designers didn't consider this situation in their index 
management scheme.

Most likely, this scenario hasn't popped up because its one that doesn't occur 
frequently. (A DBA is going to plan indexes, especially primary key indexes 
out at design time rather than add them later. If they do add them later, the 
odds are they will drop and rebuild the table.)

Michael Segel
(312) 952-8175

View raw message