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

sql oracle plsql oracle11g constraints
Rachcha picture Rachcha · Aug 23, 2013 · Viewed 52.1k times · Source

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

Jeffrey Kemp picture Jeffrey Kemp · Aug 23, 2013

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.

Related questions

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL. For example, I know that this is valid: CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job …

Read More
Sleep function in ORACLE

I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function: CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP ( TIME_ IN NUMBER ) RETURN INTEGER IS BEGIN DBMS_LOCK.sleep(seconds => TIME_); …

Read More
PLS-00428: an INTO clause is expected in this SELECT statement

I am wanting to store a Rownum as a variable rather than use a costly Join. I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a literal …

Read More