Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 11 total |
Is there a way to have a Unique Index and still allow for null values? |
Tue, Jan 16 2007 5:20 PM | Permanent Link |
Sam Lawrence | I want to have a unique index for values entered, but I still want to allow for null
values. If I set the index to Unique, and enter a second row with a null value, it will generate an error saying the key is a duplicate. Is there a way to allow for duplicate null values for a unique key? TIA Sam |
Wed, Jan 17 2007 5:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Sam
No - because then it wouldn't be a unique index The only way I can think to do anything like it is to have a prefix (string field eg !) or starting point (interger eg 1000000) which means this value has yet to be set and then suffix it with an integer then you increment (ie you'll have to store the last used somewhere) eg !1 !2 .... .... !10000 Roy Lambert |
Wed, Jan 17 2007 5:41 AM | Permanent Link |
"Ole Willy Tuv" | Roy,
<< No - because then it wouldn't be a unique index >> I don't agree. Considering that ElevatDB supports standard null handling, multiple null values should be allowed in unique constraints. Ole Willy Tuv |
Wed, Jan 17 2007 8:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ole
Does that include the primary key as well? Roy Lambert |
Wed, Jan 17 2007 8:41 AM | Permanent Link |
"Ole Willy Tuv" | Roy,
<< Does that include the primary key as well? >> The standard behavior is that primary key columns are always not nullable, either explicitly or implicitly. Ole Willy Tuv |
Wed, Jan 17 2007 9:51 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Sam,
<< I want to have a unique index for values entered, but I still want to allow for null values. If I set the index to Unique, and enter a second row with a null value, it will generate an error saying the key is a duplicate. Is there a way to allow for duplicate null values for a unique key? TIA >> Is this DBISAM or EDB ? In DBISAM, the answer is no. However, in EDB unique constraints are only enforced if the column is not NULL. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jan 17 2007 9:52 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< I don't agree. Considering that ElevatDB supports standard null handling, multiple null values should be allowed in unique constraints. >> That's the way it works in EDB. I think Sam was referring to DBISAM in his question. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jan 18 2007 9:41 PM | Permanent Link |
Sam Lawrence | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
::Sam, ::Is this DBISAM or EDB ? In DBISAM, the answer is no. However, in EDB ::unique constraints are only enforced if the column is not NULL. It's DBISAM 4.x and its a text index (non-primary). I know with MySQL I can have a unique index and it will accept multiple rows with Null values. MySQL only checks to see if non-null values are unique which is what I want. It's not a major problem. I'll just have to check for an existing record before a record is saved, and issue my own non-unique error message instead of relying on DBISAM's unique index. Sam |
Fri, Jan 19 2007 8:22 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Sam,
<< It's DBISAM 4.x and its a text index (non-primary). I know with MySQL I can have a unique index and it will accept multiple rows with Null values. MySQL only checks to see if non-null values are unique which is what I want. It's not a major problem. I'll just have to check for an existing record before a record is saved, and issue my own non-unique error message instead of relying on DBISAM's unique index. >> That's basically what EDB does internally - it simply uses a non-unique index and decides if/when it should check for duplicates based upon the NULL status the unique key columns. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jan 19 2007 9:35 AM | Permanent Link |
"Jose Eduardo Helminsky" | Tim
Can it be ported to DBISAM ? IOW, Can DBISAM handle unique index discarding NULL values ? Eduardo |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |