Overview

OceanBase Database supports multiple types of integrity constraints.

You can use the following integrity constraints to ensure that no invalid values are entered into specified columns:

• NOT NULL constraints

• UNIQUE KEY constraints

• PRIMARY KEY constraints

• Referential integrity constraints

• CHECK constraints

NOT NULL constraints

A NOT NULL constraint imposes a not null rule on a column of a table. This rule does not allow rows to be inserted into or updated in the column to be NULL.

UNIQUE KEY constraints

A UNIQUE KEY constraint imposes a unique value rule on a column or a set of columns. This rule ensures that all values in the column or set of columns are unique.

PRIMARY KEY constraints

A PRIMARY KEY constraint imposes a primary key value rule on a key, which can be a column or set of columns. This rule ensures that the values in the group of one or more columns subject to the constraint uniquely identify the row.

Referential integrity constraints

A referential integrity rule is a rule defined on a key, which can be a column or a set of columns, to ensure that any key value matches a referenced value in a related table.

When you use referential integrity constraints, it is important to determine what types of data operations can be performed on the referenced values, and how these operations will affect the dependent values. The following specific rules can be considered:

• Restrict: Any update or deletion on the referenced values is not allowed.

• Set to Null: When a referenced value is updated or deleted, all affected dependent values are set to NULL.

• Set to Default: When a referenced value is updated or deleted, all affected dependent values are set to the default value.

• Cascade: When a referenced value is updated, all affected dependent values are set to the updated referenced value. When a referenced row is deleted, all affected dependent rows are deleted.

• No Action: Any update or deletion on the referenced values is not allowed. This rule is different from the Restrict rule as it performs a check only when the execution of a statement stops or a transaction ends if the constraint is deferred. In Oracle Database, the default option is No Action.

CHECK constraints

A CHECK constraint imposes a user-defined complex integrity checking rule on a column or a set of columns. You can specify expressions or functions in the rule to allow or disallow inserts or updates of a row based on the value that the row contains for a column or set of columns.