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 me
> "MJS" == Michael J Segel <[EMAIL PROTECTED]> writes:
MJS> By the definition of a UNIQUE TABLE CONSTRAINT, I should get
MJS> only a single row returned when I query on the tuple that are
MJS> part of the defined constraint.
This is generally not true. It is only true for equalit
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 ge
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
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... ;-)
>>>
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 i
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
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
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 a
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
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
> s
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 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 goe
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 i
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 un
15 matches
Mail list logo
