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

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

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

Ole


Does that include the primary key as well?

Roy Lambert

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

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

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

"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

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

Tim

Can it be ported to DBISAM ?
IOW, Can DBISAM handle unique index discarding NULL values ?

Eduardo