foreign_key_checks

If set to 1 (the default) foreign key constraints (including ON UPDATE and ON DELETE behavior) InnoDB tables are checked, while if set to 0, they are not checked. 0 is not recommended for normal use, though it can be useful in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0.

See also: System Variables for MariaDB Enterprise Server 10.6, in 10.5 ES, and in 10.4 ES

DETAILS

The foreign_key_checks system variable controls whether foreign key constraints are enabled:

  • When it is set to OFF, foreign key constraints are disabled.

  • When it is set to ON, foreign key constraints are enabled.

  • It is not recommended to set this to OFF for normal use.

  • It can be useful to set it to OFF in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships.

  • When it is set back to ON, the server does not retrospectively check for inconsistencies introduced while it was set to OFF.

The foreign_key_checks system variable can also effect the performance of ALTER TABLE statements:

  • In MariaDB Enterprise Server 10.2, when it is set to OFF, foreign key constraints can be added to an InnoDB table with the INPLACE algorithm, which is very efficient. However, no foreign key constraints are checked, which can be unsafe.

  • In MariaDB Enterprise Server 10.3 and later, when it is set to OFF, foreign key constraints can be added to an InnoDB table with the NOCOPY algorithm, which is very efficient. However, no foreign key constraints are checked, which can be unsafe.

  • In all versions of MariaDB Enterprise Server, when it is set to ON, foreign key constraints can only be added to an InnoDB table with the COPY algorithm, which is much slower. However, foreign key constraints are checked, which can be safer.

PARAMETERS

Dynamic

Yes

Scope

Global, Session

Data Type

BOOLEAN (OFF, ON)

Default Value

ON

SKYSQL

Cloud

SkySQL Topology

ES Ver

Default

AWS

HA (Primary/Replica)

10.4

ON

AWS

HA (Primary/Replica)

10.5

ON

AWS

Transactional Standalone

10.4

ON

AWS

Transactional Standalone

10.5

ON

AWS

Distributed Transactions

10.5

true

GCP

ColumnStore

10.5

ON

GCP

ColumnStore Multi

10.5

ON

GCP

Galera

10.4

ON

GCP

Galera

10.5

ON

GCP

HA (Primary/Replica)

10.4

ON

GCP

HA (Primary/Replica)

10.5

ON

GCP

HTAP

10.5

ON

GCP

Transactional Standalone

10.4

ON

GCP

Transactional Standalone

10.5

ON

GCP

Distributed Transactions

10.5

true

PRIVILEGES

The foreign_key_checks system variable requires the SUPER privilege to dynamically set its global value at runtime.

The foreign_key_checks system variable does not require any special privilege to dynamically set its session value at runtime.

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES