How to add a not null constraint on column containing null values
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.
Rachcha
Cert CII, OCP PL/SQL Developer, B. E. From Mumbai University. Working as a software developer in insurance domain. SOreadyToHelp
Updated on July 09, 2022Comments
-
Rachcha almost 2 years
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?-
railsdog over 10 yearsSeems a little crazy to be violating your own constraints. Either it is or it isn't allowed to be null. Usually, that kind of, um, flexibility is set in the application feeding your database, not in the database itself. Doing what you want to do, that way lies madness. :)
-
hansvb over 10 yearsYou could have an insert/update trigger to reject any new nulls. But overall, this sounds like a bad idea.
-
Jeffrey Kemp over 10 yearsNot crazy at all. I've used this on occasion in systems where they wanted to keep existing (old) data, but start checking a constraint for any new (or updated) data.
-
-
Rachcha over 10 yearsCould you please provide some external links for further reference? Thanks!
-
Jeffrey Kemp over 10 yearsWhat specifically are you after? Docs: docs.oracle.com/cd/E11882_01/server.112/e41084/…