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);

Starting from V3.1.X, the OceanBase Database fully supports the NOT NULL constraint, with the following features:

When creating a table, you can specify the constraint name and its status. If no name is specified for the NOT NULL constraint, the database will generate a constraint name using the current system time. The status can be RELY/NORELY, ENABLE/DISABLE, and VALIDATE/NOVALIDATE. If no status is specified for the NOT NULL constraint when creating a table, the default status is NORELY ENABLE VALIDATE.

• RELY: The database considers that the column does not contain NULL values, allowing for query optimization and other performance enhancements.

• ENABLE: NULL values cannot be inserted into the column.

• VALIDATE: The existing data in the column does 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.