Add NOT NULL Constraint
As I mentioned in resolving ORA-00904 for adding a 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.
1. System Generated Name
You don't have to think a name for your constraint, just 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.
Lookup constraint name
To know the current constraint name, you can perform a query like this:
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
2. 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.
Then we lookup the dictionary for the constraint name.
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
Except for some business applications like ERP and CRM, we seldom provide an explicit name for a NOT NULL constraint in practice.