Hi,

At July 29, 2004, 03:15, Nando Dessena wrote:

> Adam,

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.

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

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)