--- In firebird-support@yahoogroups.com, Jason Dodson <jasond@...> wrote:

>
> Ok, first thing, DON'T ask me WHY I am doing this.
>
> Anyway, I found a peculiarity with adding a check constraint to a

varchar field I had. If I declare a check constraint:

>
> Alter Table MyTable
> Add Constraint MyConstraint Check (MyField <> '');
>
> Seems simple enough. I want no empty stings in this field. Only NULL

or a real value. The problem is, trying to insert a

> NULL into MyField will cause a constraint violation... like it, for

that moment, can't tell the difference between a

> NULL and an empty string.

You know my opinion of this. Of course I also understand the real
world too and although I suggest you should review this weakness in
the future, it has nothing to do with your issue.

Look at your constraint

MyField <> ''

NULL means "Unknown Value", so in the cases where MyField is NULL, you
are evaluating

NULL <> ''

which means

"Unknown Value" is different to ''

The above statement can not be true so it returns false. Incidentally,
reversing it would be the same issue.

NULL = ''

would mean

Unknown Value is equal to ''

Which is still a statement that is not true.

> Now if I modify the check constraint to look like this:
>
> Alter Table MyTable
> Add Constraint MyConstraint Check (MyField <> '' or MyField Is Null);
>
> Everything works peachy. Obviously it is unclear as to why I need

the supplemental "or MyField is Null". Any incite

> would be appreciated. If it turns out to be a bug, Ill file a report.

It is the Firebird behaviour with respect to nulls and for most if not
all a correct interpretation of the SQL standard. (I have not checked
if every nuance is correct)

Stop thinking that NULL is just another value and treat it as a field
state and it makes more sense. Google for the Firebird NULL guide for
more info.

Adam