The NOT NULL constraint prevents NULL values from being inserted into a column. Oracle provides two ways to define constraint names: system-generated or user-defined.

CREATE TABLE table_not_null_1
(
    id1 NUMBER(1,0) NOT NULL -- the NOT NULL constraint name will be generated automatically by Oracle as "SYS_C007034"
,   id2 NUMBER(1,0) CONSTRAINT cc_table_not_null_1_id2 NOT NULL -- the NOT NULL constraint name is explicitly defined as "CC_TABLE_NOT_NULL_1_ID2"
);

• Check the creation of the constraint:
  

  SELECT constraint_name, constraint_type, generated, search_condition
  FROM user_constraints
  WHERE table_name = 'TABLE_NOT_NULL_1';
  

  Query result:

  +-------------------------+-----------------+----------------+-------------------+
  | CONSTRAINT_NAME         | CONSTRAINT_TYPE | GENERATED      | SEARCH_CONDITION  |
  +-------------------------+-----------------+----------------+-------------------+
  | SYS_C007034             | C               | GENERATED NAME | "ID1" IS NOT NULL |
  +-------------------------+-----------------+----------------+-------------------+
  | CC_TABLE_NOT_NULL_1_ID2 | C               | USER NAME      | "ID2" IS NOT NULL |
  +-------------------------+-----------------+----------------+-------------------+
  

  Note: CONSTRAINT_TYPE 'C' indicates a CHECK constraint, which is how Oracle internally implements NOT NULL constraints.
  
  
• Check the nullability of the table columns:
  

  SELECT column_name, CASE WHEN nullable = 'N' THEN 'NO' ELSE 'YES' END AS nullable
  FROM user_tab_columns
  WHERE table_name = 'TABLE_NOT_NULL_1';
  

  Query result:

  +-------------+----------+
  | COLUMN_NAME | NULLABLE |
  +-------------+----------+
  | ID1         | NO       |
  +-------------+----------+
  | ID2         | NO       |
  +-------------+----------+
  

  The NULLABLE column in USER_TAB_COLUMNS shows 'N' for columns with NOT NULL constraints.
  
  
• Insert NULL values into the table:
  

  INSERT INTO table_not_null_1(id1, id2) VALUES(NULL, 1);
  INSERT INTO table_not_null_1(id1, id2) VALUES(1, NULL);
  

  Query result:

  Error starting at line 1 in command:
  INSERT INTO TABLE_NOT_NULL_1(ID1, ID2) VALUES(NULL, 1)
  Error report: ORA-01400: cannot insert NULL into ("HR"."TABLE_NOT_NULL_1"."ID1")
  *Cause: A NULL value was inserted into a column defined as NOT NULL.
  *Action: Do not insert NULLs or modify the column definition.
  
  Error starting at line 2 in command:
  INSERT INTO TABLE_NOT_NULL_1(ID1, ID2) VALUES(1, NULL)
  Error report: ORA-01400: cannot insert NULL into ("HR"."TABLE_NOT_NULL_1"."ID2")
  *Cause: A NULL value was inserted into a column defined as NOT NULL.
  *Action: Do not insert NULLs or modify the column definition.
  

  Oracle returns ORA-01400 error when attempting to insert NULL values into NOT NULL columns.
  

While the standard NOT NULL syntax is preferred, you can achieve the same result using an explicit CHECK constraint. This approach provides more control over constraint naming but does not modify the column's NULLABLE property in the data dictionary.

CREATE TABLE table_not_null_2
(
    ID1 NUMBER(1,0) CONSTRAINT CC_table_not_null_2_ID1 CHECK ( ID1 IS NOT NULL )
);

• Check the creation of the constraint:
  

  SELECT constraint_name, constraint_type, generated, search_condition
  FROM user_constraints
  WHERE table_name = 'TABLE_NOT_NULL_2';
  

  Query result:

  +-------------------------+-----------------+----------------+-------------------+
  | CONSTRAINT_NAME         | CONSTRAINT_TYPE | GENERATED      | SEARCH_CONDITION  |
  +-------------------------+-----------------+----------------+-------------------+
  | CC_TABLE_NOT_NULL_2_ID1 | C               | USER NAME      | ID1 IS NOT NULL   |
  +-------------------------+-----------------+----------------+-------------------+
  

• Check the nullability of the table columns:
  

  SELECT column_name, CASE WHEN nullable = 'N' THEN 'NO' ELSE 'YES' END AS nullable
  FROM user_tab_columns
  WHERE table_name = 'TABLE_NOT_NULL_2';
  

  Query result:

  +-------------+----------+
  | COLUMN_NAME | NULLABLE |
  +-------------+----------+
  | ID1         | YES      |
  +-------------+----------+
  

  Note: Using CHECK constraints to enforce NOT NULL does not update the column's NULLABLE property to 'N' in the data dictionary, even though NULL values are still prevented.
  
  
• Insert NULL values into the table:
  

  INSERT INTO table_not_null_2(ID1) VALUES(NULL);
  

  Query result:

  
  Error starting at line 1 in command:
  INSERT INTO table_not_null_2(ID1) VALUES(NULL)
  Error report: ORA-02290: check constraint (HR.CC_TABLE_NOT_NULL_2_ID1) violated
  *Cause: The values being inserted do not satisfy the named check constraint.
  *Action: Do not insert values that violate the constraint.
  

  Oracle returns ORA-02290 error for CHECK constraint violations, which differs from the ORA-01400 error for standard NOT NULL constraints.