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" <[EMAIL PROTECTED]> 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 >
