Hi Daniel,

> G>> I would like to have the ability to set or remove the "NOT NULL"
> G>> requirement on a field using the alter table statement.
>
> > I think it's already recorded as a feature request.
>
> Although probably discussed to death before, it's not in the feature
> requests(I checked).
>
> I think that a person should be allowed to remove the NOT NULL flag
> via ALTER TABLE, since there's no major implication with other record
> versions. But, also the constraints and dependencies would have to be
> checked as well. I.e.: If the field is the primary key, then that
> constraint and corresponding index will have to be dropped first,
> otherwise the DDL will fail.

No, instead, it should return an error saying "cannot drop the
not null constraint from a primary key column" or something.
Why? Because FKs can point to the PK. You have to cascade
them all in dropping if you don't return an error.

> But, allowing to set NOT NULL does require more work and could take
> some time to execute the DDL, depending on what needs to be done. If
> setting the NOT NULL without changing the NULL values that could be
> found in that field in the table, FB would have to query the table to
> make sure that there is no rows containing NULL in that field, and if
> a NULL value is found, the DDL will fail with an exception, otherwise
> it executes.

No problem there.

Or require a DEFAULT clause (or another clause that can convert
a NULL into a specific value).

And, if setting the NOT NULL attribute and also

> specifying a default value for that field, then that value would be
> replacing the NULL value, also making sure that it complies with all
> of the fields constraints(including UNIQUE constraints, since they now
> allow NULLs), before the NOT NULL flag is set. And, the most difficult
> part of the implementation would be that to set the NOT NULL flag of a
> field, it would probably require an exclusive access to the database
> or table, otherwise other record versions created after in other
> transactions that started after the transaction with the ALTER TABLE
> DDL statement started might interfere with setting the NOT NULL flag,
> especially if ALTER TABLE is committed after the other transaction.
> The only way to allow this without exclusive access would be to defer
> the actual execution, until it can be done appropriately and that no
> other transactions can interfere.
>
>
> I think that this is 2 separate feature requests, since one is easier
> to implement than the other.

From a user POV, I don't see what the problem is?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com