How to add a not null constraint on column containing null values

Question

I have a table with a column that contains a few null values. I want to add a NOT NULL constraint on that column without updating the existing nulls to a non-null value. I want to keep the existing null values and check for future rows that they contain a not null value for this column. Is this possible? How?

Answer 1

You can add an unvalidated constraint - it will not look at existing rows, but it will be checked for any new or updated rows.

ALTER TABLE mytable MODIFY mycolumn NOT NULL NOVALIDATE; 

Just be aware that you won't be able to update an existing row unless it satisfies the constraint.

Also, be aware of the downside that the optimizer will not be able to take advantage of this constraint in making its plans - it has to assume that some rows may still have a null.