foreign_key_checks

Description

The foreign_key_checks variable specifies whether to check foreign key constraints in DML/DDL operations.

Privilege requirements

• Query variables

  • Global level

    Tenants of the sys project and all user tenants 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.

  • Session level

    Tenants of the sys project and all user tenants 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.

• Modify variables

  • Global level

    • Tenants of the sys project can directly modify the value of a global system variable.

    • MySQL user tenants require the SUPER or ALTER SYSTEM privilege to modify the value of a global system variable.

  • Session level

    Tenants of the sys project and all user tenants can directly modify the value of a session system variable in the current tenant.

Attributes

Considerations

By default, OceanBase Database checks foreign key constraints in DML/DDL operations. The tenant variable foreign_key_checks controls the check switch for foreign key constraints. When you create a foreign key constraint, if you do not specify the constraint name, the system automatically assigns a constraint name. The automatically assigned constraint name consists of the table name, _OBFK_, and the timestamp of the creation time. For example, t1_OBFK_1627747200000000.

Examples

Here are examples of how to configure the variable to check foreign key constraints in DML/DDL operations.

• Session effect

  obclient> SET foreign_key_checks = ON;
  

  or

  obclient> SET foreign_key_checks = 1;
  

• Global effect

  obclient> SET GLOBAL foreign_key_checks = ON;
  

  or

  obclient> SET GLOBAL foreign_key_checks = 1;
  

References

• Foreign key constraint
• Add a foreign key constraint to a column
• Full direct load
• Incremental direct load