You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Dan Meany <da...@yahoo.com> on 2005/10/27 00:36:34 UTC
Unique constraints on multiple fields with one nullable
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.
Re: Unique constraints on multiple fields with one nullable
Posted by "Michael J. Segel" <ms...@segel.com>.
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 row returned when I query on the tuple that are part of the
> > defined constraint.
>
> Nope.
>
Junior, allow me to rephrase. With a Unique constraint in place, when I query
against the tuple of the constraint, I should get at the most one row
returned. This is the anticipated and implied result when the term unique is
used.
Now I looked down and read your response. You seem to be able to regurgitate
text book language, yet fail to comprehend its meaning.
Lets simplify what you read....
You have a table, where there are no rows. You apply the constraint.
When you attempt an insert in to the table, the constraint is tested. That is
to say that there is an inner select statement which will return true if no
rows meeting the constraint is found. Note that the exact manner of how the
constraint is applied is going to be database dependent.
When you attempt to insert another row that has a matching tuple, the
constraint will return false because it will find a row that matches.
Now the problem. What happens when a column in the constraint is NULL?
Pay attention Junior, this is the important part....
A NULL value is a special case. Derby avoids this by not allowing NULL values
in a column that has been defined by a constraint. Did you think that those
who wrote Cloudscape did this by accident? KISS in action. (You do know the
technical term KISS, right?)
In all that you wrote, you are in agreement with what I am saying until you
hit a situation of a NULL. I seriously doubt that you took the time to
understand the analogy to Schrodinger's Cat.
This is why I suggested that you consider the implied meaning of a Unique
constraint. When you tell a developer that you have a unique constraint on a
row, you expect either the empty set, or one row back. Because of this, how
Sybase handles a NULL value is a bug. And it can be very dangerous. By
looking at the implied meaning, you avoid Schrodinger's Cat. After all,
what's the definition of the word unique?
But hey, what do I know? I'm old school. K&R created a near perfect
language. ;-)
I'll just leave this in your capable hands. After all, you're paid to play
here. I'm not.
-G
--
Michael Segel
Principal
MSCC
(312) 952-8175
Re: Unique constraints on multiple fields with one nullable
Posted by Jeffrey Lichtman <sw...@rcn.com>.
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 up on this:
http://troels.arvin.dk/db/rdbms/#constraints-unique
This site quotes the following language from the standard on unique
constraints:
"there are no two rows in [the relation] such that the value of each
column in one row is non-null and is not distinct from the value of
the corresponding column in the other row"
Don't laugh (or cry) - this sort of language creates a lot of jobs
for consultants. What it means with respect to unique constraints and
nulls is that the constraint is enforced only for rows not containing
nulls in the constrained columns.
The logic behind this has to do with the standard interpretation of
nulls. A null in SQL stands for "value unknown." Since the value of
null is unknown, the result of comparing a null to anything is also
unknown (e.g. there's no way of knowing whether a null int is equal
to 1, since the null could stand for 1 or for any other int value).
So boolean logic in SQL has three values: true, false and unknown.
You can think of "unknown" as a null boolean value.
So when a column named in a unique constraint contains null, and you
want to know whether that value violates the constraint, the answer
is neither "yes" nor "no" - it's "I don't know". But when that value
is inserted, it must either go into the table or not - the INSERT
statement must either succeed or fail ("I don't know" is not a choice
when deciding whether to accept or reject data). The SQL standard
goes with the rule that a uniqueness constraint is only violated if
the DBMS knows that a row contains non-unique values. Thus, multiple
nulls are allowed.
Not all database systems follow the SQL standard closely with respect
to unique constraints and nulls. According to the web site I quoted
above, some of them don't allow unique constraints on nullable
columns (the standard does allow this restriction). Other database
systems treat nulls as distinct values for the purposes of unique
constraints - that is, they allow only one null. The quoted site
claims that Oracle considers it a violation for multi-column unique
constraints if the non-null values in a row are not unique.
There are a few places in standard SQL where nulls are considered to
be distinct values. For example, with ORDER BY nulls are supposed to
sort together, and with GROUP BY they are supposed to group together.
There aren't many of these exceptions to the general rule that nulls
are supposed to be treated an unknown values that could be equal to anything.
Now, having said all this, I must add that I really object to the
sarcastic language used by one poster in this thread. It is uncalled
for and counterproductive to insult people who are only trying to help.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: Unique constraints on multiple fields with one nullable
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
>>>>> "MJS" == Michael J Segel <ms...@segel.com> 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 equality expressions.
For a range expressions, you may of course get more than a single row.
Since NULL values can not be tested by equality expressions in SQL
(you have to use 'IS NULL'), I think your logic is faulty.
MJS> Using our existing example, If I say the following:
MJS> SELECT *
>> FROM foo
MJS> WHERE id = 1
MJS> AND name = "AAA";
MJS> I should expect that I will get back at the most 1 record.
MJS> Now why is Sybase a bug?
MJS> Suppose we had the following:
MJS> SELECT *
>> FROM foo
MJS> WHERE id = 1
MJS> AND name IS NULL;
MJS> How many rows will I get back?
MJS> (Again its rhetorical...)
I do not think this example is relevant since the where-clauses are
not of the same type. The latter query does not contain an equality
predicate for name.
I think your view limits the expressiveness of the SQL language. In
my view, NOT NULL and UNIQUE are orthogonal constraints:
NOT NULL - All rows must have a value for this column
UNIQUE - All values for this column must be unique
This will give you four possible combinations. The combination of
UNIQUE and NULLs allowed becomes meaningless with your interpretation
since it would mean that all rows, except one, must have distinct
values, and there will be not way to express 'nulls allowed and all
values should be unique'.
The main point is that NULL is not a value. It represent the absence
of a value.
--
Øystein
Re: Unique constraints on multiple fields with one nullable
Posted by Dan Meany <da...@yahoo.com>.
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
Re: Unique constraints on multiple fields with one nullable
Posted by Daniel John Debrunner <dj...@debrunners.com>.
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 bug in Sybase.
>>>(Note: Bug because by allowing multiple tuples where n-1 of the
>>>constraint columns match and the nth column is NULL, the guaranteed
>>>uniqueness fails. [Where n = number of columns in the table constraint] )
>>
>>Is it a bug? NULL is not equal to NULL in SQL land, so 6,NULL is not
>>equal to 6,NULL so uniqueness has not been violated.
>>
>
> 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 row
> returned when I query on the tuple that are part of the defined constraint.
Nope.
>
> Using our existing example, If I say the following:
> SELECT *
> FROM foo
> WHERE id = 1
> AND name = "AAA";
>
> I should expect that I will get back at the most 1 record.
with that exact query, but that's not the definition of a unique constraint.
> Now why is Sybase a bug?
> Suppose we had the following:
>
> SELECT *
> FROM foo
> WHERE id = 1
> AND name IS NULL;
>
> How many rows will I get back?
> (Again its rhetorical...)
>
> Hint: If what Dan M says is true, I will not be guaranteed at the most one row
> returned. Hence the CONSTRAINT FAILED.
IS NULL predicate has nothing to do with the definition of a unique
constraint.
SQL standard says defines the search condition SC for a unique constraint as
sql> UNIQUE (SELECT id, name from FOO)
Then goes onto say
sql> The unique constraint is not satisfied if and only if
sql> EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
sql> is True
Which means (in this case)
EXISTS (SELECT * FROM FOO WHERE NOT UNIQUE (SELECT id, name from FOO))
So to move onto the definition of the UNIQUE predicate
sql> If there are no two rows in T such that the value of each column in
sql> one row is non-null and is not distinct
sql> from the value of the corresponding column in the other row, then
sql> the result of the <unique predicate> is
sql> True; otherwise, the result of the <unique predicate> is False.
Got to love the double negatives!
So that is basically saying, since 'value of *each* column in one row is
non-null', that rows with null in them do not partake in the uniqueness
checking. At least that's my reading of it, maybe someone with more SQL
standard experience could chime in.
thus with our two rows of 6,NULL and 6,NULL then
UNIQUE (SELECT id, name from FOO) will return True,
so we are down to
EXISTS (SELECT * FROM FOO WHERE NOT TRUE)
EXISTS (SELECT * FROM FOO WHERE FALSE)
EXISTS (<empty-set>)
False
So the unique constraint is satisified from
sql> The unique constraint is not satisfied if and only if
sql> EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
sql> is True
So multiple rows with the NULL in some columns and other values
identical are allowed.
Dan.
>
>
>>>With respect to constraints in Derby...
>>>
>>>You really need to consider allowing NULLs in columns that are part of a
>>>table constraint. In fact, you should really reconsider how you handle
>>>constraints in general.
>>>
>>>But hey, what do I know? Meow. ;-)
>>
>>Hmmmmm, since we are not mind readers, well I'm not, others may be, it's
>>hard to know what you know and more importantly why you think
>>constraints should be handled differently. Throwing out a comment such
>>as 'In fact, you should really reconsider how you handle constraints in
>>general.' doesn't really add any value to any discussion. Starting up a
>>new discussion on the developer list on possible improvements to
>>constraints would be great, even better would be contributing the
>>improvements yourself.
>>
>>Dan.
>
> Well, its a good thing you're not a mind reader. ;-)
>
> With respect to constraints, under the current design, you have an
> inconsistency within Derby.
>
> I can create a constraint on an index that is applied to a table, while I can
> not create the same constraint directly on the table. To me, thats a
> design/implementation fault.
>
> Again, this goes back to how you consider and handle constraints in the first
> place.
>
> -G
Re: Unique constraints on multiple fields with one nullable
Posted by "Michael J. Segel" <ms...@segel.com>.
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.
> > (Note: Bug because by allowing multiple tuples where n-1 of the
> > constraint columns match and the nth column is NULL, the guaranteed
> > uniqueness fails. [Where n = number of columns in the table constraint] )
>
> Is it a bug? NULL is not equal to NULL in SQL land, so 6,NULL is not
> equal to 6,NULL so uniqueness has not been violated.
>
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 row
returned when I query on the tuple that are part of the defined constraint.
Using our existing example, If I say the following:
SELECT *
FROM foo
WHERE id = 1
AND name = "AAA";
I should expect that I will get back at the most 1 record.
Now why is Sybase a bug?
Suppose we had the following:
SELECT *
FROM foo
WHERE id = 1
AND name IS NULL;
How many rows will I get back?
(Again its rhetorical...)
Hint: If what Dan M says is true, I will not be guaranteed at the most one row
returned. Hence the CONSTRAINT FAILED.
> > With respect to constraints in Derby...
> >
> > You really need to consider allowing NULLs in columns that are part of a
> > table constraint. In fact, you should really reconsider how you handle
> > constraints in general.
> >
> > But hey, what do I know? Meow. ;-)
>
> Hmmmmm, since we are not mind readers, well I'm not, others may be, it's
> hard to know what you know and more importantly why you think
> constraints should be handled differently. Throwing out a comment such
> as 'In fact, you should really reconsider how you handle constraints in
> general.' doesn't really add any value to any discussion. Starting up a
> new discussion on the developer list on possible improvements to
> constraints would be great, even better would be contributing the
> improvements yourself.
>
> Dan.
Well, its a good thing you're not a mind reader. ;-)
With respect to constraints, under the current design, you have an
inconsistency within Derby.
I can create a constraint on an index that is applied to a table, while I can
not create the same constraint directly on the table. To me, thats a
design/implementation fault.
Again, this goes back to how you consider and handle constraints in the first
place.
-G
--
Michael Segel
Principal
MSCC
(312) 952-8175
Re: Unique constraints on multiple fields with one nullable
Posted by Daniel John Debrunner <dj...@debrunners.com>.
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 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.
> (Note: Bug because by allowing multiple tuples where n-1 of the constraint
> columns match and the nth column is NULL, the guaranteed uniqueness fails.
> [Where n = number of columns in the table constraint] )
Is it a bug? NULL is not equal to NULL in SQL land, so 6,NULL is not
equal to 6,NULL so uniqueness has not been violated.
> With respect to constraints in Derby...
>
> You really need to consider allowing NULLs in columns that are part of a table
> constraint. In fact, you should really reconsider how you handle constraints
> in general.
>
> But hey, what do I know? Meow. ;-)
Hmmmmm, since we are not mind readers, well I'm not, others may be, it's
hard to know what you know and more importantly why you think
constraints should be handled differently. Throwing out a comment such
as 'In fact, you should really reconsider how you handle constraints in
general.' doesn't really add any value to any discussion. Starting up a
new discussion on the developer list on possible improvements to
constraints would be great, even better would be contributing the
improvements yourself.
Dan.
Re: Unique constraints on multiple fields with one nullable
Posted by "Michael J. Segel" <ms...@segel.com>.
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 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.
(Note: Bug because by allowing multiple tuples where n-1 of the constraint
columns match and the nth column is NULL, the guaranteed uniqueness fails.
[Where n = number of columns in the table constraint] )
With respect to constraints in Derby...
You really need to consider allowing NULLs in columns that are part of a table
constraint. In fact, you should really reconsider how you handle constraints
in general.
But hey, what do I know? Meow. ;-)
-G
--
Michael Segel
Principal
MSCC
(312) 952-8175
Re: Unique constraints on multiple fields with one nullable
Posted by Rick Hillegas <Ri...@Sun.COM>.
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 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.
>>>
>>>
>
>
>
Re: Unique constraints on multiple fields with one nullable
Posted by Dan Meany <da...@yahoo.com>.
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" <ms...@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
>
Re: Unique constraints on multiple fields with one nullable
Posted by "Michael J. Segel" <ms...@segel.com>.
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
Re: Unique constraints on multiple fields with one nullable
Posted by Dan Meany <da...@yahoo.com>.
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" <ms...@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
>
Re: Unique constraints on multiple fields with one nullable
Posted by "Michael J. Segel" <ms...@segel.com>.
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
Re: Unique constraints on multiple fields with one nullable
Posted by Rick Hillegas <Ri...@Sun.COM>.
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.
>
>
>