4

In MySQL, it is possible to disable FK checks using something like the following:

SET FOREIGN_KEY_CHECKS=0;

Is there a way to prevent this, i. e. preventing users from disabling foreign keys? I don't ever want to allow that or do it myself.

  • 1
    The issue is that people disable the foreign key checks so that they can write data that does not conform to foreign key constraints. This causes errors down the road, when attempting to work with the data when foreign key checks are enabled. – rich remer Oct 25 '16 at 21:26
  • 3
    It still may be handy when importing data. – dezso Oct 25 '16 at 21:28
  • 1
    Suggestion for improvement? SET FOREIGN_KEY_CHECKS=0 disables the check. I don't want people to be able to disable the check. I want to restrict users from disabling foreign key checks. The problem statement requires a double negative. – rich remer Oct 25 '16 at 21:33
  • 2
    @richremer see my edit - I tried hard to avoid the double negative (even if I like it and would be funny, once you figured out what it wanted to say :) – dezso Oct 25 '16 at 21:35
  • 1
    It's not about responsibility. It's about catching problems early, in an automated way, with minimal hassle. – rich remer Oct 26 '16 at 1:27
1

You can set triggers on the relevant tables to check for foreign_key_checks=0 and signal an error if that's the case.

Call this procedure to check for the setting and signal the error:

DELIMITER $$
CREATE PROCEDURE force_foreign_key_check ()
BEGIN
    IF @FOREIGN_KEY_CHECKS = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'foreign_key_checks is set to 0. Operation canceled.';
    END IF;
END$$
DELIMITER ;

And set your triggers like this:

CREATE TRIGGER force_foreign_key_check_insert_table1
BEFORE INSERT ON table1
FOR EACH ROW CALL force_foreign_key_check;

CREATE TRIGGER force_foreign_key_check_update_table1
BEFORE UPDATE ON table1
FOR EACH ROW CALL force_foreign_key_check;

CREATE TRIGGER force_foreign_key_check_delete_table1
BEFORE DELETE ON table1
FOR EACH ROW CALL force_foreign_key_check;
| improve this answer | |
0

I suppose you can SET foreign_key_checks = 1; and remove SUPER privileges from other database users.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.