MariaDB – Disable Foreign Key Checks

foreign keymariadb

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.

Best Answer

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.

Related Solutions

SQL Server 2005 – Foreign Keys Can’t Be Added Because They Exist But They Don’t Exist

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add.

a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers

b) Do the same as the above for tblRoles and RoleID.

c) As I imagine both of the above will return records that are not in the referenced table, you'll need to clean up your data before adding the Foreign Keys

d) Name you foreign keys something useful! If I came to work on your database, how am I supposed to know what Relationship26 means?!

MySQL – How to Delete Row with Self-Referencing Foreign Key Constraint

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider:

Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I haven't tested all cases but you surely need this for the (owner_id) foreign key and possibly for the other as well.

ALTER TABLE forum
    DROP FOREIGN KEY owner_id_frgn,
    DROP FOREIGN KEY parent_id_frgn ;
ALTER TABLE forum
    ADD CONSTRAINT owner_id_frgn
        FOREIGN KEY (owner_id) 
        REFERENCES forum (id)
        ON DELETE CASCADE,
    ADD CONSTRAINT parent_id_frgn
        FOREIGN KEY (parent_id) 
        REFERENCES forum (id)
        ON DELETE CASCADE ;

If you do this, then deleting a node and all the descendants from the tree is simpler. You delete a node and all descendants are deleted through the cascade actions:

DELETE FROM forum
WHERE id = 1 ;         -- deletes id=1 and all descendants

The issue that you stepped into is actually 2 issues. The first is that deleting from a table with self-referencing foreign key is not a serious problem for MySQL, as long as there is no row that references itself. If there is a row, as in your example, the options are limited. Either disable foreign keys or use CASCADE action. But if there are no such rows, deleting becomes a smaller problem.

So, if we decide to store NULL instead of the same id in owner_id, then you could delete without disabling foreign keys and without cascades.

You would then stumble upon the second problem! Running your query would raise a similar error:
```
DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 ; 
```
Error(s), warning(s):
Cannot delete or update a parent row: a foreign key constraint fails (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

The reason for this error would be different than before though. It's because MySQL checks each constraint after every row is deleted and not (as it should) at th eend of the statement. So when a parent is deleted before its child is deleted, we get a foreign key constraint error.

Fortunately, there is a simple solution for this, thnx to the nested set model and to that MySQL allows us to set an order for the deletes. We just have to order by nleft DESC or by nright DESC, which makes sure that all the children are deleted before a parent:
```
DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 
ORDER BY nleft DESC ; 
```
Minor note, we could (or should) use a condition that considers the nested model as well. This is equivalent (and might use an index on (nleft, nright) to find which nodes to delete:
```
DELETE FROM forum 
WHERE nleft >= 1 AND nright <= 8 
ORDER BY nleft DESC ; 
```

Best Answer

Related Solutions

SQL Server 2005 – Foreign Keys Can’t Be Added Because They Exist But They Don’t Exist

MySQL – How to Delete Row with Self-Referencing Foreign Key Constraint

Related Question