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 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 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 constraints

A referential constraint imposes a referential integrity rule on a key, which can be a column or set of columns. This rule ensures that all values of this key match the values of a key in a related table. The values of this key are called dependent values and the matching values in the related table are referenced values.

A referential integrity constraint provides rules on the types of data manipulation operations that are allowed on referenced values and describes how these operations affect dependent values. The following referential integrity rules are supported:

• Restrict: No operation to update or delete the referenced values is allowed.

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

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

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

• No Action: No operation to update or delete the referenced values is allowed. This rule is different from the Restrict rule. The No Action rule performs a check only when the execution of a statement stops or a transaction ends if the constraint is deferred.