Hello Jason,

> 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.

Well, they ARE different.

>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.
>

No, this is known behaviour.

Thing is, it goes against the SQL standard. Also this is a known issue.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com