MariaDB: disable foreign key checks

Question

On MySql, in order to truncate a table or delete rows when it's normally impossible because of foreign keys (InnoDB only), we use this command:

SET FOREIGN_KEY_CHECKS=0;

On MariaDB, while this command is accepted, it does nothing.

The documentation says I have to run these commands instead:

On a per-table basis:

ALTER TABLE `...` DISABLE KEYS;

or, globally:

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

So I tried to run this script:

SET FOREIGN_KEY_CHECKS=0;
SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;
ALTER TABLE `country` DISABLE KEYS;
DELETE FROM `country` WHERE 1;

And that leads me to:

Cannot delete or update a parent row: a foreign key constraint fails (database.region, CONSTRAINT FK_F62F176F92F3E70 FOREIGN KEY (country_id) REFERENCES country (id))

Using TRUNCATE has the same effect.

If you have any idea on what I may have missed, thank you by advance. Because the documentation and the existing questions weren't much of help here.

Answer 1

It should work with:

SET FOREIGN_KEY_CHECKS=0

I tried the following:

create table parent (x int not null primary key) engine = innodb;
create table child (x int not null primary key, constraint aaa foreign key (x) references parent (x) on delete restrict) engine = innodb;
insert into parent (x) values (1),(2);

-- test if f.k is active
insert into child (x) values (1),(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test"."child", CONSTRAINT "aaa" FOREIGN KEY ("x") REFERENCES "parent" ("x"))

insert into child (x) values (1);
SET FOREIGN_KEY_CHECKS=0;
delete from parent;
select * from parent;
Empty set (0.00 sec)

select * from child;
+---+
| x |
+---+
| 1 |
+---+

select @@version
-> ;
+-----------------+
| @@version       |
+-----------------+
| 10.1.16-MariaDB |
+-----------------+

So the problem is not with MariaDB, but with PhpMyAdmin. It has a checkbox on the pages where you can execute SQL, that overrides SET FOREIGN_KEY_CHECKS=. One must uncheck it if one wants to disable foreign key validation.

Answer 2

We had a similar issue while using MariaDB4j with Hibernate and C3P0 connection pooling.

Disabling foreign keys with SET FOREIGN_KEY_CHECKS=0; seems not to work when using C3P0 connection pooling with multiple connections. Disabling foreign keys worked perfectly after limiting the number of connections to 1. We had to set the following properties in the hibernate.properties file:

connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=1

Answer 3

For anyone else stumbling upon this with recent MariaDB versions. This behavior was disabled for the update of character set due to issues with importing and exporting. A

Therefore, even with set FOREIGN_KEY_CHECKS=0; any modify tables that change the character set will fail. As this change is done in a patch version, the behavior could be different between different machines.

For more background information and a possible workaround (delete foreign keys and recreate), check the issue tracker in MariaDB.