Add option to Remove Foreign key checks on trucate

Hey,

Would it be possible to add an option in the table menu that would remove foreign key checks, truncate the table and then add the foreign key checks again.
Basically this:
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table;
SET FOREIGN_KEY_CHECKS = 1;

The options could be places after the menu option "Empty table(s)..."

Yes, I can add that. At least when you *drop* tables, HeidiSQL already does that. But I'll add that for truncation too.

Cool!! Thanks for the quick reply :)

Done in r4902

Is it possible to disable this behaviour? Now I would get orphaned rows in my other tables.
I'd rather have an additional option to run a delete query first so the necessary rules are triggered to make sure the rows are allowed to be deleted.

Oh, good point. Didn't spend a thought on orphaned rows.

@Acs: Is there a use case for having foreign keys disabled while truncating?

Yes. I am testing code that interacts with the database.
I don't really care for the data at this point but I do need sometimes to truncate the table, without this option I always have to create a query to disable foreign key checks.

That is why I suggested adding an option under Empty Tables. So that the user knows that he is asking to disable foreign key checks before truncating. Maybe an option named Empty Tables (disable FKC) or something like that.

Don't you think that your use case is for testing purposes only? I think another menu item makes it more complicated for new users, which know nothing about foreign keys. We need some alternative here. Probably some button or link somewhere, labeled "Foreign key checks enabled" which can be toggled to "Foreign key checks disabled" (red colored!). Something like that?

I'm fine with that, I've already have "SET FOREIGN_KEY_CHECKS = 0;" under a shortcut (AutoHotkey)

Well if you think it would complicate things that much, yeah maybe a button that will popup when that table has foreign keys.

@thadin: How did you manage to create such a shortcut in HeidiSQL? That's not supported as far as I can remember :)

I didn't set it in HeidiSQL but in AutoHotkey ;)

The problem is that newer versions of mysql will not allow to run TRUNCATE TABLE if there are rows in other tables (related by FKs) to be deleted. A few times I've run into this restriction and had to run DELETE FROM... first.

What I would find most useful is for the Empty Table action to run DELETE followed by TRUNCATE. This could be optional, since on very large tables this would take a lot of time. But anyway, I don't see any other (quick) way of deleting all rows without breaking data integrity so for me personally this could even be the default - Heidi could also intelligently check first if there any other tables in the db that have FKs pointing to the table and use DELETE only if there are any - but I don't know if this would be worth the trouble. Or at least use DELETE only for innodb tables. Or - if there is a large number of rows in the table (like >100 000) then give a warning before emptying so that the user has to accept the potential for slow operation.

Has this already been implemented?

Because an option that applies "SET FOREIGN_KEY_CHECKS = 0" would be very useful before running TRUNCATE through the "Empty table" on context menu option.

I think this feature should exist, perhaps an optional feature in the application settings, and the responsibility for incorrect / accidental use should be solely to the user.

In commit:daae16e5834f51ed1370f34ec2e39f757b07d559 I disabled FOREIGN_KEY_CHECKS, and one day later, in commit:cf6c8e0618a7cf68e64e6574d9bf971890f1daf3 I removed that part of the code again, hoping we find some better solution in this thread here, which was not the case yet.