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 12:40:34 GMT
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

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

> On Wednesday 26 October 2005 19:22, Rick Hillegas
> wrote:
> 
> Sorry to top post...
> 
> Sigh.
> Seems that some are quick to pull the trigger and
> call everything they see a 
> bug!
> 
> This is not a bug. ;-)
> Its a design issue.
> 
> I'm sure that this distinction is going to be lost
> on a couple of people, and 
> it goes back to an earlier isssue about how each
> database handles 
> constraints.  You can always e-mail me directly and
> take this offline.
> 
> Whomever designed how Derby handles constraints,
> Derby does not allow for 
> NULLs in columns that have been identified in a
> constraint. Its actually in 
> the reference manual. (The exercise of finding it is
> left to the 
> student.... ;-)
> 
> 
> Now if you want a simple work around, just create a
> unique index on the table.
> Here's the code I just ran and it works:
> 
> CREATE TABLE foo 
> 	( id int NOT NULL,
> 	  name char(25)
> 	);
> 	
> CREATE UNIQUE INDEX fidx ON foo (id, name);
> 
> Then I added the following rows:
> 	 INSERT INTO foo VALUES (1,'AAA');
> 	 INSERT INTO foo VALUES (2,'BBB');
> 	 INSERT INTO foo VALUES (3,'CCC');
> 	 INSERT INTO foo VALUES (4,'DDD');
> 	 INSERT INTO foo VALUES (5,'EEE');
> 	 INSERT INTO foo VALUES (1,'FFF');
>  	INSERT INTO foo VALUES (6, NULL);
> 
> 	INSERT INTO foo VALUES (1,'AAA'); -- THIS ROW
> FAILS!
> 	SELECT * FROM foo;
> 
> NOTE THE FOLLOWING:
> 
> Indexes are not the same as Constraints, however
> they can be used to achieve 
> the same goal. 
> 
> Please remember, because you may not get the results
> that you expect, that 
> doesn't mean that you actually have a bug.
> 
> If someone wants to consider how to redesign how
> contraints work, you have a 
> couple of other considerations. For example, which
> takes precedence? SQL 
> statements that manage the container, or SQL
> statements that manage the data?
> (And that's a loaded question.... ;-)
> 
> HTH
> 
> -Mikey
> 
> PS. Again, what do I know?  Rumor has it my older
> siblings dropped me on my 
> head when I was an infant. ;-)
> 
> > Hi Dan,
> >
> > I believe that the Sybase behavior is correct. I
> have logged bug 653 to
> > track this issue.
> >
> > Regards,
> > -Rick
> >
> > Dan Meany wrote:
> > >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.
> > >
> > >This is different from some other databases such
> as
> > >Sybase that do allow it (I assume where the null
> > >records are not in stored as part of the unique
> > >index).
> > >
> > >I noticed this while transfering data from Sybase
> to
> > >Derby using Apache dbutils and getting rejected
> > >duplicate records.
> 
> -- 
> Michael Segel
> Principal
> MSCC
> (312) 952-8175
> 


Mime
View raw message