db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Thu, 25 Aug 2005 21:04:21 GMT
Dan Scott wrote:

> For what it's worth, in this scenario DB2 on Linux also fails with an
> error (SQL0542, to be exact, with SQLSTATE 42831):
> dan@localhost ~ $ db2 'create table testy (id integer)'
> DB20000I  The SQL command completed successfully.
> dan@localhost ~ $ db2 'insert into testy (id) values (NULL)'
> DB20000I  The SQL command completed successfully.
> dan@localhost ~ $ db2 'alter table testy add constraint pk_1 primary key  (id)'
> DB21034E  The command was processed as an SQL statement because it was not a 
> valid Command Line Processor command.  During SQL processing it returned:
> SQL0542N  "ID" cannot be a column of a primary key or unique key because it 
> can contain null values.  SQLSTATE=42831

That's somewhat different, that's rejecting an primary key because the
column *by defintion* could contain NULL, not because it actually does.
I think if you run the same test without the insert you will get the
same failure.

If you could try with a check constraint, say id < 20 and have a
existing value > 20 in there, then that would be comparable.


View raw message