Modifying Tables for NULL Values

You can modify an existing column that formerly permitted NULLs to disallow NULLs, provided that the column contains no NULL values. To do this, specify MODIFY with the same column name and data type and the NOT NULL keywords. Those keywords create a NOT NULL constraint on the column.

You can modify an existing column that did not permit NULLs to permit NULLs. To do this, specify MODIFY with the column name and the existing data type, and omit the NOT NULL keywords. The omission of the NOT NULL keywords drops the NOT NULL constraint on the column. If a unique index exists on the column, you can remove it using the DROP INDEX statement.

An alternative method of permitting NULL values in an existing column that did not permit NULL values is to use the DROP CONSTRAINT clause to drop the NOT NULL constraint on the column.

When you define a PRIMARY KEY constraint, the database server also silently creates a NOT NULL constraint on the same column, or on the same set of columns that make up the primary key.