>>> The syntax would be:
>>> alter table <table> alter <field> not null;
>>> alter table <table> alter <field> null;
>>>
>>> It's semantics would be:
>>> When putting "NOT NULL", in DFW (commit time) the table is locked
>>> and a BLR dynamic query is constructed to inspect for NULL values. If
>>> there are, we throw an error.
>>>
>>
>> As a partially related question, what about adding a column with a NOT
>> NULL constraint without de-facto violating that constraint, as now?
>>
>> IIRC, there was a suggestion to use a DEFAULT value (if defined) to
>> populate such a column and reject the operation otherwise. I'm not sure
>> it follows the standard though.
>>
>> Do you think it could be a good idea? Could you suggest anything else?
> Seems good... But didn't we have problem when metadata and data of one
> table is changed in one transaction?

There are multiple things you can do when the table contains records:

1) require a column DEFAULT in order to make the column "not null"
or
2) require a special clause that fills in the data for the NULL-state
columns
in order to make the column "not null"

eg:
alter table <table> alter <field> not null DEFAULT 1
or
alter table <table> alter <field> not null USE 1


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com