Skip to content

How Oracle Add NOT NULL Constraint

  • by

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.

Leave a Reply

Your email address will not be published.