Note
This variable was introduced in OceanBase Database V2.1.
Description
foreign_key_checks specifies whether to enable FOREIGN KEY constraint check for DML or DDL statements.
Applicability
This variable is applicable to only OceanBase Database in MySQL mode.
Attributes
Attribute |
Description |
|---|---|
| Type | Boolean |
| Default value | 1 |
| Value range | 0: disables FOREIGN KEY constraint check.1: enables FOREIGN KEY constraint check. |
| Effective scope | |
| Modifiable | Yes. You can use the SET statement to modify the variable. |
Considerations
The foreign_key_checks variable controls FOREIGN KEY constraint check. It is enabled by default. It is often enabled during normal operations to enforce referential integrity.
In the following scenarios, disabling foreign_key_checks is useful:
Drop a table referenced by a FOREIGN KEY constraint. The table can only be dropped after you disable
foreign_key_checks. Constraints defined on a table are dropped together with the table.Reload tables in an order different from the order required by their foreign key relationships.
Import data. You can disable FOREIGN KEY constraint check to speed up data import.
Execute
ALTER TABLEoperations on a table in a foreign key relationship.
Disabling foreign_key_checks has the following negative effects:
You are allowed to drop a database that contains tables with foreign keys that are referenced by tables outside the database.
You are allowed to drop a table with foreign keys referenced by other tables.
A table scan is not triggered when foreign_key_checks is enabled. Therefore, rows added to a table during the period when foreign_key_checks is disabled are not checked for consistency when foreign_key_checks is re-enabled.
Examples
Session-level operation
obclient> SET foreign_key_checks=1;Global-level operation
obclient> SET GLOBAL foreign_key_checks=1;