foreign_key_checks

Description

foreign_key_checks specifies whether to enable FOREIGN KEY constraint check for DML or DDL statements.

Privilege requirements

• Query variables

  • Global level

    You can execute the SHOW VARIABLES statement or query the information_schema.GLOBAL_VARIABLES view (MySQL mode) to view the value of a Global system variable in the sys tenant or any user tenant.

  • Session level

    You can execute the SHOW VARIABLES statement or query the information_schema.SESSION_VARIABLES view (MySQL mode) to view the value of a Session system variable in the sys tenant or any user tenant.

• Modify variables

  • Global level

    • You can directly modify the value of a Global system variable in the sys tenant.

    • A MySQL user tenant must have the SUPER or ALTER SYSTEM privilege to modify the value of a Global system variable.

  • Session level

    You can directly modify the value of a Session system variable in the sys tenant or any user tenant.

Attributes

Considerations

The foreign key check is controlled by the foreign_key_checks variable, which is enabled by default. It is typically enabled during normal operations to enforce referential integrity.

Disabling foreign_key_checks can have positive impacts on the database in the following scenarios:

• Deleting a table referenced by a foreign key constraint. The referenced table can only be deleted after disabling foreign_key_checks. When a table is deleted, the constraints defined on the table are also removed.

• Reloading tables in a different order than required by the foreign key relationships.

• Speeding up data imports by disabling foreign key checks during data import operations.

• Performing ALTER TABLE operations on tables with foreign key relationships.​

However, disabling foreign_key_checks can have some negative impact in the following scenarios:

• Allowing the deletion of databases containing tables with foreign keys referenced by tables outside of the database.

• Allowing the deletion of tables with foreign keys referenced by other tables.

Enabling foreign_key_checks does not trigger a scan of table data. This means that the rows added to the table while foreign_key_checks was disabled will not be checked for consistency when foreign_key_checks is enabled again.

Examples

• Session level

  obclient> SET foreign_key_checks=1;
  

• Global level

  obclient> SET GLOBAL foreign_key_checks=1;
  

References

FOREIGN KEY constraints