Skip to content

How Oracle Remove NOT NULL Constraint

  • by

Before we talk about how to remove NOT NULL constraint, I assume that you have already know how to add a NOT NULL constraint to a column, they are really relevant.

Remove NOT NULL Constraint

1. Revert NOT NULL by NULL

Removing a NOT NULL constraint is pretty easy, no matter the constraint name was provided by system or user, you can just declare the column as NULL at attribute-level to revert the constraint.

SQL> alter table countries modify (region_id null);

Table altered.

SQL> select a.constraint_name, b.status, b.generated from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

no rows selected

This is the easiest way to drop NOT NULL constraint. As you can see, we just switch it off. Of course, you can use the formal syntax to delete the constraint explicitly like this:

2. System Generated Name

System-generated constraint name is not easy to remember, you should lookup the constraint name first.

SQL> select a.constraint_name, b.status, b.generated from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

CONSTRAINT_NAME           STATUS   GENERATED
------------------------- -------- --------------
SYS_C007822               ENABLED  GENERATED NAME

Then drop it.

SQL> alter table countries drop constraint SYS_C007822;

Table altered.

3. User Provided Name

Same as above, we explicitly delete the constraint by name.

SQL> alter table countries drop constraint RID_NN;

Table altered.

If you just want the NOT NULL constraint to stop working for a while, then you don't need to remove it, just disable NOT NULL constraint.

Leave a Reply

Your email address will not be published.