I don't have my copy of the X3H2 SQL standard in front of me, but as
I remember, the rule for unique constraints is that the uniqueness is
applied only to non-null values. The standard is not available
on-line (ANSI/ISO protects its copyright vigorously), but I did find
a web site that backs
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
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
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
Thanks, Michael. You are correct, Derby, like DB2, finesses this issue
by not allowing nullable columns in unique constraints. I have closed
this bug.
Cheers,
-Rick
Michael J. Segel wrote:
On Wednesday 26 October 2005 19:22, Rick Hillegas wrote:
Sorry to top post...
Sigh.
Seems that some
On Thursday 27 October 2005 11:01, Rick Hillegas wrote:
Thanks, Michael. You are correct, Derby, like DB2, finesses this issue
by not allowing nullable columns in unique constraints. I have closed
this bug.
Cheers,
-Rick
NP,
But Dan's reply is an interesting one.
What Sybase did was
Michael J. Segel wrote:
On Thursday 27 October 2005 11:01, Rick Hillegas wrote:
Thanks, Michael. You are correct, Derby, like DB2, finesses this issue
by not allowing nullable columns in unique constraints. I have closed
this bug.
Cheers,
-Rick
NP,
But Dan's reply is an interesting
On Thursday 27 October 2005 13:44, Daniel John Debrunner wrote:
But Dan's reply is an interesting one.
What Sybase did was create a Schrodinger's Cat.
(See http://whatis.techtarget.com/definition/0,,sid9_gci341236,00.html
for a definition... ;-)
This is actually a bug in Sybase.
Michael J. Segel wrote:
On Thursday 27 October 2005 13:44, Daniel John Debrunner wrote:
But Dan's reply is an interesting one.
What Sybase did was create a Schrodinger's Cat.
(See http://whatis.techtarget.com/definition/0,,sid9_gci341236,00.html
for a definition... ;-)
This is actually a
DB2, at least with the version/settings we have, seems
to be able to do a CREATE UNIQUE INDEX WHERE NOT NULL,
but not MS SQL Server.
Here's a further discussion of this topic...
http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx
On Thursday 27 October 2005 15:54, Daniel John Debrunner wrote:
Sigh.
Ok, so you want to play with cats?
What is NULL?
That was a rhetorical question.
Here's a more practical approach and why its a bug.
By the definition of a UNIQUE TABLE CONSTRAINT, I should get only a
single
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
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
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
14 matches
Mail list logo
