Add option to Remove Foreign key checks on trucate

Acs's profile image Acs posted 9 years ago in General Permalink
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)..."
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, I can add that. At least when you *drop* tables, HeidiSQL already does that. But I'll add that for truncation too.
Acs's profile image Acs posted 9 years ago Permalink
Cool!! Thanks for the quick reply :)
Code modification/commit from ansgar.becker, 9 years ago, revision 4902
Disable foreign key checks while truncating tables. See http://www.heidisql.com/forum.php?t=17587
ansgar's profile image ansgar posted 9 years ago Permalink
Done in r4902
[expired user #6512]'s profile image [expired user #6512] posted 9 years ago Permalink
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.
ansgar's profile image ansgar posted 9 years ago Permalink
Oh, good point. Didn't spend a thought on orphaned rows.

@Acs: Is there a use case for having foreign keys disabled while truncating?
Acs's profile image Acs posted 9 years ago Permalink
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.
ansgar's profile image ansgar posted 9 years ago Permalink
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?
[expired user #6512]'s profile image [expired user #6512] posted 9 years ago Permalink
I'm fine with that, I've already have "SET FOREIGN_KEY_CHECKS = 0;" under a shortcut (AutoHotkey)
Acs's profile image Acs posted 9 years ago Permalink
Well if you think it would complicate things that much, yeah maybe a button that will popup when that table has foreign keys.
ansgar's profile image ansgar posted 9 years ago Permalink
@thadin: How did you manage to create such a shortcut in HeidiSQL? That's not supported as far as I can remember :)
[expired user #6512]'s profile image [expired user #6512] posted 9 years ago Permalink
I didn't set it in HeidiSQL but in AutoHotkey ;)
lemon_juice's profile image lemon_juice posted 9 years ago Permalink
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.
albertdick's profile image albertdick posted 4 years ago Permalink

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.

ansgar's profile image ansgar posted 4 years ago Permalink

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.

Please login to leave a reply, or register at first.