db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Meany <dan_me...@yahoo.com>
Subject Re: Unique constraints on multiple fields with one nullable
Date Thu, 27 Oct 2005 14:42:11 GMT
Sybase happens to work that way (but MS SQL Server
does not appear to), that is a constraint can be on
nullable columns, and if they contain nulls, they do
not participate in the uniqueness constraint.  I don't
know if this is desirable or not.  I understand Derby
is a different database which may behave differently. 


Dan


--- "Michael J. Segel" <msegel@segel.com> wrote:

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


Mime
View raw message