Re: Unique constraints on multiple fields with one nullable
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
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: Unique constraints on multiple fields with one nullable
> "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 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
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
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
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 is sql> True; otherwise, the result of the 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 () 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. ;-) >> >>Hm, 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
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. ;-) > > Hm, 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
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. ;-) Hm, 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
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
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
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 >
Re: Unique constraints on multiple fields with one nullable
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
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" <[EMAIL PROTECTED]> 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
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
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.
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.
