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 Defined 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.