#1541 disable foreign keys check for current operation

[grek]

Target: version 4.5

Add option to (import ! , delete, truncate, sqlquery) - disable foregin key check.

im symfony php developer, offen i need remove or make some changes i must add SET FOREIGN_KEY_CHECKS=0;
query
SET FOREIGN_KEY_CHECKS=1;

i think nice will by option auto disable foregin key checks.

But for import is necessary - i export db from phpmyadmin. try import and this is not possible when i have foregin keys (popular doctrine php orm add it many)

so must manualy edut file - add
SET FOREIGN_KEY_CHECKS=0;

and at the end:

SET FOREIGN_KEY_CHECKS=1;

and when file is big - is hard to edut it. Many problems - so option - disable foregin key check for this operation will by very good

[Thejdeep]

Would like to pick this up. As a summary of the feature, we have to disable foreign key checks when importing a database ?

[Marc Delisle]

Thejdeep,
please pick a feature request without an owner. This one is scheduled for GSOC 2015 during the summer.

[grek]

Yes, add it as checkbox - disable foregin key check for this query.

phpmyadmin allow to make changes on database - without that in symfony like
database is hard to manage.

Import is not possible - and if i have big file is not easy to add to top
and end foregin key check.

I know - is possible to add this options on export but if i have dump
without it is not possible to import database.

i think for developers this option should by avaible not only on import but
like i say in truncate, remove and other options.

thanks

[tapanhalani]

Won't this feature make data integriyt vulnerable? I mean if we truncate the parent(referenced) table, then who will the referring(child) table point to?

I mean this check was only meant to maintain data integrity.I cannot find a use-case when this optional implementation of foreign key check will be useful, because it is making data integrity vulnerable.

Please give me an example on how will we imply data integrity constraints if we call off the check?

[Marc Delisle]

The person using this option would be aware of the impact. In fact, grek is already forcing the correct options manually to avoid the checks.

[tapanhalani]

Also,when I export the database, and create a new empty database and then import the dump there, then I am, as expected , not having any problems importing and hence creating an exact replica db,both in data and relational views.

This is why import and export facility is used for, if I am not wrong. So , I couldn't quite understand the problem and why this feature is needed.

Please enlighten me.

[tapanhalani]

I understand your point Marc.

But for import, I found that even after I export a db enabled foreign key checks,and then when I import it without disabling foreign key checks, I am perfectly able to do so, and it makes an exact replica even in relational terms.

And for import, there is already a checkbox available on the page for "disable foreign key checks". So why do we need to add it again?

I understand why we need it for delete and truncate.

[Marc Delisle]

Maybe you only tested importing in a empty database, but we can also import when the database is not empty.

About "Disable foreign key checks", I only saw this when exporting via phpMyAdmin, but there are other ways to export. So the import phase via phpMyAdmin needs this option as well.

[tapanhalani]

I am showing this option of "disable foreign key checks" on import page as well. I am uploading an image of my import page.

[Marc Delisle]

Which version is this?

[tapanhalani]

It's 4.5

[tapanhalani]

And Marc,

Do we have to give complete detailed implementation details for this task? I am actually a lot confused, as this feature could be implemented in many ways, and contains more complexity. Or is it ok, if I give basic implementation designs?

[Marc Delisle]

In the proposal you can give basic stuff and we'll ask questions if needed.

[tapanhalani]

I have 2 types of implementations in my mind :-

(a.) We can add a checkbox for "disable foreign key checks" in the confirmation dialogs themselves, along with OK and CANCEL buttons.

(b.) We can remove the above confirmation dialogs, instead we can reload the same page (structure page of db ) with divisions containing confiramtion messages, required YES and NO buttons , and an additional "disable foreign key checks" checkbox (same as what happens when we select "DROP" from multi-submit list for all the selected tables.)

Which one is preferrable ? Should I include both these implementations in my project proposal?

[Marc Delisle]

a) looks fine to me

[tapanhalani]

Do me a favour Marc , and please check my proposal for this feature's
implementation. I have implemented option b. Please tell me if that works
for you or not.

Meanwhile, i am gonna work on option a.

[Marc Delisle]

We do our best to review all of the proposals.

[tapanhalani]

I didn't mean that. Sorry if you felt that I am forcing you to review my proposal, because that wasn't my intention. I just wanted you to check my option b. implementation design , whenever you have time.

I have already started my work on option a.

[tapanhalani]

Marc,

I have uploaded the details of both my options a. and b. to my proposal. Is it ok, or shall I include only option a ?

[Marc Delisle]

It's correct to mention both options.