foreign_key_checks

Description

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

Privilege requirements

• Query variables

  • Global level

    sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the information_schema.GLOBAL_VARIABLES view (MySQL-compatible mode) to obtain the value of a global system variable.

  • Session level

    sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the information_schema.SESSION_VARIABLES view (MySQL-compatible mode) to obtain the value of a session system variable.

• Modify variables

  • Global level

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

    • MySQL user tenants can modify the value of a global system variable only if they have the SUPER or ALTER SYSTEM privilege.

  • Session level

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

Attributes

Usage notes

By default, foreign key constraints are checked in OceanBase Database. You can use the tenant variable foreign_key_checks to enable or disable foreign key constraint checks. If you do not specify a name for a foreign key constraint, the system automatically assigns a constraint name. The automatically assigned constraint name is in the format of table name _OBFK_ creation timestamp. For example, t1_OBFK_1627747200000000.

Examples

Set whether to check foreign key constraints in DML/DDL operations.

• Session level

  obclient> SET foreign_key_checks = ON;
  

  or

  obclient> SET foreign_key_checks = 1;
  

• Global level

  obclient> SET GLOBAL foreign_key_checks = ON;
  

  or

  obclient> SET GLOBAL foreign_key_checks = 1;
  

References

• Foreign key constraints
• Column-level foreign key constraints
• Full bypass import
• Incremental bypass import