foreign_key_checks

Overview

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.

USAGE

The global value of the foreign_key_checks system variable can be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL foreign_key_checks=ON;

The session value of the foreign_key_checks system variable can also be set dynamically at runtime by executing SET SESSION:

SET SESSION foreign_key_checks=ON;

When the global value of the foreign_key_checks system variable is set dynamically at runtime, its value will be reset the next time the server restarts. The system variable cannot be set in configuration files, so changes to the default value cannot be made to persist on restart.

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 affect 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.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

Not Supported

Configuration file

Not Supported

Dynamic

Yes

Scope

Global, Session

Data Type

BOOLEAN (OFF, ON)

Product Default Value

ON

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

ON

Single Node Analytics

10.6

ON

Replicated Transactions

10.4

ON

10.5

ON

10.6

ON

Single Node Transactions

10.4

ON

10.5

ON

10.6

ON

GCP

Multi-Node Analytics

10.6

ON

Single Node Analytics

10.6

ON

Replicated Transactions

10.4

ON

10.5

ON

10.6

ON

Single Node Transactions

10.4

ON

10.5

ON

10.6

ON

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

ON

Enterprise Server With Replica(s)

10.4

ON

10.5

ON

10.6

ON

Enterprise Server Single Node

10.4

ON

10.5

ON

10.6

ON

GCP

ColumnStore Data Warehouse

10.6

ON

Enterprise Server With Replica(s)

10.4

ON

10.5

ON

10.6

ON

Enterprise Server Single Node

10.4

ON

10.5

ON

10.6

ON

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.

EXAMPLES

Effect on Consistency of Foreign Key Constraints

When the foreign_key_checks system variable is set to ON, a row that fail the foreign key check can not be written to the table. Instead, an error is raised with either the ER_ROW_IS_REFERENCED_2 error code or the ER_NO_REFERENCED_ROW_2 error code, depending on whether the row is in the parent table or the child table:

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT,
   FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a)
);
INSERT INTO tab1 (a, d) VALUES (1, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`tab1`, CONSTRAINT `tab2_fk` FOREIGN KEY (`d`) REFERENCES `tab2` (`a`))

However, when the foreign_key_checks system variable is set to OFF, rows that fail the foreign key check can be written to the table:

SET SESSION foreign_key_checks=OFF;

INSERT INTO tab1 (a, d) VALUES (1, 1);

Effect on Alter Table Performance

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:

CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);
SET SESSION foreign_key_checks=OFF;

ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a), ALGORITHM=INPLACE;

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:

CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SET SESSION foreign_key_checks=OFF;

ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a), ALGORITHM=NOCOPY;
Query OK, 0 rows affected (0.005 sec)

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:

CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);
CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a), ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a), ALGORITHM=COPY;

Dynamically Resetting Global Value to Default

The global value of the foreign_key_checks system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL foreign_key_checks=OFF;

SHOW GLOBAL VARIABLES LIKE 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
SET GLOBAL foreign_key_checks=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+

Invalid Value for Variable

If a user tries to set the foreign_key_checks system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SET GLOBAL foreign_key_checks=-1;
ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the foreign_key_checks system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL foreign_key_checks=1.0;
ERROR 1232 (42000): Incorrect argument type to variable 'foreign_key_checks'

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

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