Foreign key constraint check

In MatrixOne, foreign_key_checks is a system variable that controls the checking of foreign key constraints. This variable can be global or session-level. When set to 1 (the default value), MatrixOne checks the integrity of foreign key constraints to ensure the reference integrity of the data. If set to 0, these checks are skipped.

Note

Inconsistent with MySQL's behavior, when the foreign key constraint check is turned off, the parent table is deleted. MySQL will not delete the foreign key relationship that the child table refers to the parent table, but MatrixOne will delete the foreign key relationship that the child table refers to the parent table, and after rebuilding the parent table, the foreign key relationship will be re-established.

View foreign_key_checks

Use the following command in MatrixOne to view foreign_key_checks:

--Global Mode
SELECT @@global.foreign_key_checks;
SHOW global VARIABLES LIKE 'foreign_key_checks';

--Session mode
SELECT @@session.foreign_key_checks;
SHOW session VARIABLES LIKE 'foreign_key_checks';

Set foreign_key_checks

Set foreign_key_checks in MatrixOne using the following command:

--Global mode, reconnection to the database takes effect
set global foreign_key_checks = 'xxx'

--Session mode
set session foreign_key_checks = 'xxx'

Example

mysql> SELECT @@session.foreign_key_checks;
+--------------------------+
| @@foreign_key_checks |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)

create table t2(a int primary key,b int);
create table t1( b int, constraint `c1` foreign key `fk1` (b) references t2(a));

insert into t2 values(1,2);
mysql> insert into t1 values(3);--When turning on foreign key constraint check, the value that violates the constraint cannot be inserted
ERROR 20101 (HY000): internal error: Cannot add or update a child row: a foreign key constraint fails

mysql> drop table t2;--When turning on foreign key constraint check, the parent table cannot be deleted
ERROR 20101 (HY000): internal error: can not drop table 't2' referenced by some foreign key constraint

set session foreign_key_checks =0;--Close foreign key constraint check
mysql> SELECT @@session.foreign_key_checks;
+--------------------------+
| @@foreign_key_checks |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(3);--When closing foreign key constraint check, you can insert the value that violates the constraint
Query OK, 1 row affected (0.01 sec)

mysql> drop table t2;--When closing foreign key constraint check, you can delete the parent table.
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1;--Delete the parent table, foreign key constraints are also deleted
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
`b` INT DEFAULT NULL
) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> create table t2(n1 int);--Reconstruct the deleted parent table t2, which must contain the original foreign key reference column of the child table
ERROR 20101 (HY000): internal error: column 'a' no exists in table ''

mysql> create table t2(n1 int,a int primary key);--Contains the referenced primary key column a, and the reconstruction is successful
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;--After rebuilding t2, foreign key relationship will be automatically re-established
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
`b` INT DEFAULT NULL,
CONSTRAINT `c1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT
) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)