How to Add or Drop NOT NULL Constraint

Add NOT NULL Constraint

As I mentioned in How to Resolve ORA-00904 for ALTER TABLE ADD CONSTRAINT, NOT NULL is a constraint, but it's more like an attribute of a column. If you add an outline constraint for NOT NULL, you will get ORA-00904 like this:

SQL> alter table countries add constraint "RID_NN" not null (region_id);
alter table countries add constraint "RID_NN" not null (region_id)
                                              *
ERROR at line 1:
ORA-00904: : invalid identifier

In Oracle constraint syntax, it's called out_of_line_constraint and NOT NULL constraints must be declared inline (inline_constraint).

Now, let's learn the correct ways to add NOT NULL constraint to a column of a table.

System Generated Name

You don't have to think a name for your constraint, we can add NOT NULL constraint at attribute-level, the system will generate a constraint name for it.

SQL> alter table countries modify (region_id not 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';

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

User Provided Name

If you'd like to name your NOT NULL constraint, we can should add NOT NULL constraint inline.

SQL> alter table countries modify (region_id constraint "RID_NN" not 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';

CONSTRAINT_NAME           STATUS   GENERATED
------------------------- -------- --------------
RID_NN                    ENABLED  USER NAME

Drop NOT NULL Constraint

Revert NOT NULL by NULL

Dropping a NOT NULL constraint is pretty easy, no matter the constraint is named 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 simplest way to drop NOT NULL constraint. Of course, you can use the formal syntax to drop the constraint explicitly like this:

System Generated Name

SQL> alter table countries drop constraint SYS_C007822;

Table altered.

User Provided Name

SQL> alter table countries drop constraint RID_NN;

Table altered.

Disable NOT NULL Constraint

To disable a NOT NULL constraint, you can do this:

SQL> alter table employees modify constraint SYS_C007818 disable;

Table altered.

Please note that, the constraint is not vanished, it's just hided. If you try to add a NOT NULL constraint on such column, you will run into ORA-01442: column to be modified to NOT NULL is already NOT NULL.

To make the constraint function again, you can enable it.

SQL> alter table employees modify constraint SYS_C007818 enable;

Table altered.