A. 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, the above syntax of adding constraints is 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
B. 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.
C. Disable NOT NULL Constraint
Table altered.
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.