Commented by: @paulvink

I agree that this would be useful.

Meanwhile, Pierre, it *can* be done with DDL. Cookbook is here:

http://www.firebirdsql.org/manual/nullguide-alter-pop-tables.html#nullguide-make-column-non-nullable
http://www.firebirdsql.org/manual/nullguide-alter-pop-tables.html#nullguide-make-column-nullable

Commented by: Mirco Malaguti (mirmalag)

Hi Pierre and Paul.
That's a minor deficiency. Nontheless it requires some boring workaround as you pointed out.
I vote for the improvement, adding that the application of the command should leave the database in a consistent state, otherwise an error should be returned. I agree that this similar inconsistent state can occur when you add a NOT NULL column without a default value.
Someone has argued about this point. My position is that no valid operation should leave an inconsistent state by design.
Maybe it's possible to add an "ASSIGN <value> TO NULLS" clause for those cases.

Thank you

Mirco

Commented by: PizzaProgram Ltd. (szakilaci)

Please re-OPEN this case, because since Firebird 3.0 > this workaround does not work any more!
1.] -- This works on FB 1.0, 2.0, 2.1, 2.5 :
update rdb$relation_fields set rdb$null_flag = null /* or 0 */
where rdb$relation_name = '<TableName>'
and rdb$field_name = '<FieldName>'
FB3 disabled the possibility to alter system tables.

We need a solution for this that works on ALL versions of FB the same way. (from 1.5- to 3.x)

Tried everything, like:
2.]
alter table rendeles_tetelek drop constraint (
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 = 'ORDER_DETAILS'
and cc.rdb$trigger_name = 'ITEM_ID')

3.]
alter table rendeles_tetelek alter column ITEM_ID set DEFAULT null;
-- no error msg, and constraint remained not null !!!

... but no solution.

Thank you,
László

Commented by: @dyemanov

There's no solution that would work the same way in all FB versions. You need to use either "update rdb$relation_fields" or "alter table", depending on FB version.

Commented by: PizzaProgram Ltd. (szakilaci)

"alter table.." is currently NO SOLUTION to 3.0, because you can not insert the constraint_name directly, without an intermediate API. (See my not-working No.2 example).
(Maybe it would be possible with a very complicated EXECUTE BLOCK + EXECUTE STATEMENT procedure ... but most of the currently available Script-running API components can not handle those.)

Isn't it possible at least to make this format work on 3.x and than back-port to FB2.5:

alter table <table_name> alter column <column_name> set DEFAULT NULL;
?
Because this is causing currently a non-logical metadata setup at all versions. (having a not-null field with default-null).

Thanks ;)

Commented by: PizzaProgram Ltd. (szakilaci)

By the way, these pages needs to be upgraded to exclude FB3 from "solutions and Tips" until this issue has been solved:

http://www.firebirdfaq.org/faq103/
https://www.firebirdsql.org/manual/nullguide-alter-pop-tables.html#nullguide-make-column-nullable

Commented by: PizzaProgram Ltd. (szakilaci)

Please reopen this ticket and increase priority.
We still can not migrate to FB3.0 at any of our clients because of this problem.
(It's not just an "improvement" any more, but we need a FIX!)
Thank you very much.

Commented by: @dyemanov

You don't need to know the constraint name:
alter table rendeles_tetelek alter column ITEM_ID drop not null;

Commented by: PizzaProgram Ltd. (szakilaci)

It WORKS ! Thanks :)

please add these lines to the manual + FAQ !
(see links above)

Firebird 3.0 up:

ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP NOT NULL;
-- removes the "not null" constraint

Commented by: @dyemanov

It's documented in the release notes:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#rnfb30-ddl-managenull

The Null Guide wasn't updated for FB v3 yet.