NOT NULL constraints

In OceanBase Database, a NOT NULL constraint is used to ensure that a specified column of a table is never assigned a null value.

For versions earlier than OceanBase Database V3.1.X, a NOT NULL constraint is considered a property of a column and can be defined only by using the following syntax. After the table is created, you cannot define a NOT NULL constraint on a nullable column.

CREATE TABLE employee(
id NUMBER NOT NULL,
name VARCHAR(100) NOT NULL,
mgr_id NUMBER);

OceanBase Database V3.1.X and later support various operations on NOT NULL constraints. The following list describes the supported operations:

• You can specify the name and states of a NOT NULL constraint when you create a table. If you do not specify a name for a NOT NULL constraint, a name is automatically generated by the database based on the current point in time. The states of a NOT NULL constraint can be RELY or NORELY, ENABLE or DISABLE, and VALIDATE or NOVALIDATE. If you do not specify the states for a NOT NULL constraint, the default states NORELY ENABLE VALIDATE are used.

  • RELY: The database considers that the column does not contain null values and performs optimization on the column.

  • ENABLE: No null value can be inserted into the column.

  • VALIDATE: The existing values of the column do not contain null values.

  CREATE TABLE employee(
  id NUMBER CONSTRAINT ID_NOT_NULL NOT NULL NORELY ENABLE VALIDATE,
  name VARCHAR(100) CONSTRAINT NAME_NOT_NULL NOT NULL,
  mgr_id NUMBER);
  

• You can view definitions of your NOT NULL constraints in the ALL_CONSTRAINTS view. The type of NOT NULL constraints is C. You can also query information about a NOT NULL constraint by using the DBMS_METADATA.GET_DDL function.

  SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'employee';
  SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', 'ID_NOT_NULL') FROM DUAL;
  

• You can use the ALTER TABLE statement to add or drop a NOT NULL constraint. You can also modify the states of a NOT NULL constraint as needed. To insert null values into the ID column, execute one of the following statements to change the states of the ID_NOT_NULL constraint to DISABLE or drop the constraint.

  ALTER TABLE employee MODIFY CONSTRAINT ID_NOT_NULL DISABLE;
  ALTER TABLE employee DROP CONSTRAINT ID_NOT_NULL;
  

  To prevent inserting null values into the mgr_id column, execute the following statement to define a NOT NULL constraint on the mgr_id column:

  ALTER TABLE employee MODIFY mgr_id NOT NULL;
  

  After you execute the preceding statement to define the NOT NULL constraint on the mgr_id column, OceanBase Database checks whether the existing data contains null values. If the existing data contains null values, an error message is returned.