On Thursday 27 October 2005 07:40, Dan Meany wrote:
> The posted code does not provide a workaround as it
> does not allow duplicate rows with nulls:
>
> INSERT INTO foo VALUES (6, NULL);
> INSERT INTO foo VALUES (6, NULL); <-- this fails
>
> Error: org.apache.derby.client.am.SqlException: The
> statement was aborted because it would have caused a
> duplicate key value in a unique or primary key
> constraint or unique index identified by 'FIDX'
> defined on 'FOO'., SQL State: 23505, Error Code: -1
>
>
> Dan
>
Hmmm.
Maybe I'm confused due to the lack of sleep from watching the CHICAGO WHITE
SOX kick BUTT!
The original question:
I noticed that in Derby a unique constraint on two
columns A and B, with B nullable, will prevent
inserting two identical records that contain NULL in
B.
-=-
Well first you have two issues.
1) Derby will not allow NULL values in columns that are part of a constraint.
2) If you want a unique constraint that allows for a duplicate tuple of
(6,NULL)? Its a question of the uniqueness of NULL... ;-)
So is the poster asking "I want a unique constraint except when I have rows
that include a NULL value in a column used by the constraint" or
"How can I have a constraint that will allow NULLS in columns that are used by
the constraint"?
Since #2 doesn't make sense, and would require an SP that is triggered prior
to insert, I'm going to go out on a limb and say that the user wanted to find
a way to work around #1.
(Note: again #2 isn't a bug but a design issue...)
No?
Ok, so what am I missing?
Maybe Dan M. can clarify his question?
--
Michael Segel
Principal
MSCC
(312) 952-8175
