#237 NOT NULL status of field is not cleared correctly

v1.0
closed
nobody
None
1
2013-10-02
2013-05-24
Anonymous
No
0 up votes | 0 down votes | 0%
2 comments

If I right-click on a field name and choose "Properties", there is a checkbox labeled "Not null". When I clear this checkbox and click "Execute", FlameRobin executes the following statement:

UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = NULL
WHERE RDB$FIELD_NAME = '<FieldName>' AND RDB$RELATION_NAME = '<TableName>';

Unfortunately, this only clears the field's NULL flag in the system table. There is still a NOT NULL constraint in effect for the field, and so Firebird will not allow NULLs to be inserted.

To remove this constraint, FlameRobin should first retrieve its name, then drop the constraint from the relevant table, like so:

-- look up the constraint name
SELECT rc.RDB$CONSTRAINT_NAME
FROM   RDB$RELATION_CONSTRAINTS rc
       JOIN RDB$CHECK_CONSTRAINTS cc
       ON rc.RDB$CONSTRAINT_NAME = cc.RDB$CONSTRAINT_NAME
WHERE  rc.RDB$CONSTRAINT_TYPE   = 'NOT NULL'
       AND rc.RDB$RELATION_NAME = '<TableName>'
       AND cc.RDB$TRIGGER_NAME  = '<FieldName>'

-- drop the actual constraint
ALTER TABLE <TableName> DROP CONSTRAINT <ConstraintName>

Once that is done, Firebird will accept NULL values into the field.

Thank you!

Discussion

  • Michael Hieke
    Michael Hieke
    2013-10-02

    Thanks for the report, fixed in git commit cd76bbc0ace3d3be00def5ef03e78e547a74422c

     


Anonymous


Cancel   Add attachments