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: Unique constraints on multiple fields with one nullable
Date Thu, 27 Oct 2005 13:18:21 GMT
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);  <-- 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

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

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...)

Ok, so what am I missing?
Maybe Dan M. can clarify his question?

Michael Segel
(312) 952-8175

View raw message