A have a DBISAM test table defined as:

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE "TestingImport"
(
  "ID" INTEGER NOT NULL,
  "FirstName" VARCHAR(10) NOT NULL,
  "LastName" VARCHAR(10) NOT NULL,
  "Sex" VARCHAR(1) MIN 'F' MAX 'M',
  "SeqNo" INTEGER MIN 1 MAX 7,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

In DBISAM, I can add a row that has a NULL SeqNo , but it will not let me add a NULL Sex entry. (I should be able to add a row that has a NULL value for SEX).

When I migrate to EDB, migration fails if there are NULLS in either the SEX or SEQNO fields.  It should pass validation if the condition is either TRUE or UNKNOWN, not simply fail if the
condition is FALSE.

Also, after the table fails validation, I cannot perform the migration a second time because it states the table already exists.  It does not appear in the table list for the database so I cannot
drop the table.

Thank you for your assistance.

Richard,

<< In DBISAM, I can add a row that has a NULL SeqNo , but it will not let me
add a NULL Sex entry. (I should be able to add a row that has a NULL value
for SEX). >

This is most likely because DBISAM treats '' and NULL as the same and
interchangeable.

<< When I migrate to EDB, migration fails if there are NULLS in either the
SEX or SEQNO fields.  It should pass validation if the condition is either
TRUE or UNKNOWN, not simply fail if the condition is FALSE. >>

This isn't correct.  EDB has to migrate the min/max expressions over as
this:

"Sex" VARCHAR(1) CHECK Sex >= 'F' AND Sex <= 'M',

If Sex is NULL, then the CHECK expression fails.  However, given the way
that DBISAM behaves, we might want move the MIN and MAX values over as:

"Sex" VARCHAR(1) CHECK Sex IS NULL or (Sex >= 'F' AND Sex <= 'M'),

I'm going to have to think about this a little longer, but as long as there
aren't any major downsides, I'll modify the migration code as indicated for
build 2.

<< Also, after the table fails validation, I cannot perform the migration a
second time because it states the table already exists.  It does not appear
in the table list for the database so I cannot drop the table. >>

Hit the Refresh toolbar button and the table will appear.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< This isn't correct.  EDB has to migrate the min/max expressions over as
this:

"Sex" VARCHAR(1) CHECK Sex >= 'F' AND Sex <= 'M',

If Sex is NULL, then the CHECK expression fails.  >>

I believe Richard is correct - the CHECK constraint is satisfied if the
condition evaluates to TRUE or UNKNOWN.

Here's the relevant evaluation rule, ref. ISO/IEC 9075-2:2003 sub-clause
11.6 <table constraint definition> :

ISO/IEC 9075-2:2003
11.6 <table constraint definition>

3) If the <table constraint> is a <check constraint definition>, then let SC
be the <search condition> immediately contained in the <check constraint
definition> and let T be the table name included in the corresponding table
constraint descriptor; the table constraint is not satisfied if and only if

  EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )

is True.

The expression (Sex >= 'F' AND Sex <= 'M') evaluates to UNKNOWN for null
values in the Sex column, and the expression NOT (UNKNOWN) also evaluates to
UNKNOWN, hence the condition does not validate the CHECK constraint. It
seems that the current implementation of the CHECK constraint in ElevateDB
is incorrect.

Ole Willy Tuv

> .. hence the condition does not validate the CHECK constraint.

<sigh>, should read:

hence the condition does not violate the CHECK constraint.

Ole Willy Tuv

Ole,

<< I believe Richard is correct - the CHECK constraint is satisfied if the
condition evaluates to TRUE or UNKNOWN. >>

Actually, that's not exactly what Richard was reporting, but I did check the
reference and you are correct.  A fix will be in build 2.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< .. hence the condition does not validate the CHECK constraint.

<sigh>, should read:

hence the condition does not violate the CHECK constraint. >>

I knew what you meant.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Ole,

<< I believe Richard is correct - the CHECK constraint is satisfied if the
condition evaluates to TRUE or UNKNOWN. >>

Actually, that's not exactly what Richard was reporting, but I did check the
reference and you are correct.  A fix will be in build 2.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you to both Ole and Tim . . . Ole explained it much more eloquently than I did.