foreign_key_checks

• See SkySQL details in MariaDB SkySQL previous release

• See SkySQL details in MariaDB SkySQL new release

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'