> and had to workaround this by similiar construct.
>
> if (field is null) then
> INSERT INTO MY_TABLE(ID) VALUES (:ID);
> else
> INSERT INTO MY_TABLE(ID, MY_FIELD) VALUES (:id, :my_field);
> end
>
>
> question? is it correct?

yes

> does anyone else feel this counter-intuitive?

yes in one way, but in another way, by stating MY_FIELD in the insert
statement, you are saying that "I have a specific value/state for this
field". You "know" that MY_FIELD is NULL (unknown / undefined). But
you also have a constraint that says it can't be NULL, hence the
exception.

>is there a need for such explicit code?

A better solution in this case would be to create a trigger to convert
the NULL to a 0. Some people may not want data containing NULLs to be
inserted at all, and the logic you are proposing simple substitutes
with 0 (which may not be what they want).

Here is a sample trigger.

SET TERM ^ ;

----

CREATE TRIGGER MY_TABLE_BI FOR MY_TABLE
ACTIVE BEFORE INSERT POSITION 1
AS
BEGIN
IF (NEW.MY_FIELD IS NULL) THEN
BEGIN
NEW.MYFIELD = 0;
END
END
^

SET TERM ; ^

COMMIT WORK;

----

Now it will work as you want. If you pass it a null, the null is
automatically converted to a 0. If you don't pass the field at all,
the default 0 is placed in.

Using a trigger rather than client logic is better (providing this is
the single interpretation on what it means in your business rules when
one tries to add a NULL). It is better because you don't need to
litter your code with your work around, it automatically converts it
for you.

Adam