csdnceshi58
Didn"t forge
采纳率25%
2013-03-19 14:03

如何在 MySQL 中临时禁用外键约束?

已采纳

Is it possible to temporarily disable constraints in MySQL?

I have two Django models, each with a ForeignKey to the other one. Deleting instances of a model returns an error because of the ForeignKey constraint:

cursor.execute("DELETE FROM myapp_item WHERE n = %s", n)
transaction.commit_unless_managed()  #a foreign key constraint fails here

cursor.execute("DELETE FROM myapp_style WHERE n = %s", n)
transaction.commit_unless_managed()

Is it possible to temporarily disable constraints and delete anyway?

转载于:https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

11条回答

  • csdnceshi64 游.程 8年前

    Try DISABLE KEYS or

    SET FOREIGN_KEY_CHECKS=0;
    

    make sure to

    SET FOREIGN_KEY_CHECKS=1;
    

    after.

    点赞 26 评论 复制链接分享
  • weixin_41568184 叼花硬汉 7年前

    To turn off foreign key constraint globally, do the following:

    SET GLOBAL FOREIGN_KEY_CHECKS=0;
    

    and remember to set it back when you are done

    SET GLOBAL FOREIGN_KEY_CHECKS=1;
    

    WARNING: You should only do this when you are doing single user mode maintenance. As it might resulted in data inconsistency. For example, it will be very helpful when you are uploading large amount of data using a mysqldump output.

    点赞 21 评论 复制链接分享
  • csdnceshi58 Didn"t forge 3年前

    To turn off foreign key constraint globally:

    SET GLOBAL FOREIGN_KEY_CHECKS = 0;
    

    and for active foreign key constraint

    SET GLOBAL FOREIGN_KEY_CHECKS = 1;
    
    点赞 9 评论 复制链接分享
  • csdnceshi80 胖鸭 7年前

    If the key field is nullable, then you can also set the value to null before attempting to delete it:

    cursor.execute("UPDATE myapp_item SET myapp_style_id = NULL WHERE n = %s", n)
    transaction.commit_unless_managed() 
    
    cursor.execute("UPDATE myapp_style SET myapp_item_id = NULL WHERE n = %s", n)
    transaction.commit_unless_managed()
    
    cursor.execute("DELETE FROM myapp_item WHERE n = %s", n)
    transaction.commit_unless_managed()
    
    cursor.execute("DELETE FROM myapp_style WHERE n = %s", n)
    transaction.commit_unless_managed()
    
    点赞 8 评论 复制链接分享
  • weixin_41568196 撒拉嘿哟木头 6年前

    I normally only disable foreign key constraints when I want to truncate a table, and since I keep coming back to this answer this is for future me:

    SET FOREIGN_KEY_CHECKS=0;
    TRUNCATE TABLE table;
    SET FOREIGN_KEY_CHECKS=1;
    
    点赞 5 评论 复制链接分享
  • weixin_41568174 from.. 4年前

    A very simple solution with phpmyadmin : in your table, go to SQL tab, after you edit the SQL command that you want to run, next to GO there is a check box 'Enable foreign key checks' .Disable this check box and run your SQL . It will be then automatically re-checked.

    点赞 2 评论 复制链接分享
  • csdnceshi70 笑故挽风 8年前

    Instead of disabling your constraint, permanently modify it to ON DELETE SET NULL. That will accomplish a similar thing and you wouldn't have to turn key checking on and off. Like so:

    ALTER TABLE tablename1 DROP FOREIGN KEY fk_name1; //get rid of current constraints
    ALTER TABLE tablename2 DROP FOREIGN KEY fk_name2;
    
    ALTER TABLE tablename1 
      ADD FOREIGN KEY (table2_id) 
            REFERENCES table2(id)
            ON DELETE SET NULL  //add back constraint
    
    ALTER TABLE tablename2 
      ADD FOREIGN KEY (table1_id) 
            REFERENCES table1(id)
            ON DELETE SET NULL //add back other constraint
    

    Have a read of this (http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) and this (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html).

    点赞 2 评论 复制链接分享
  • csdnceshi59 ℙℕℤℝ 3年前

    For me just SET FOREIGN_KEY_CHECKS=0; wasn't enough. I was still having a com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException.

    I had to add ALTER TABLE myTable DISABLE KEYS;.

    So:

    SET FOREIGN_KEY_CHECKS=0;
    ALTER TABLE myTable DISABLE KEYS;
    DELETE FROM myTable;
    ALTER TABLE myTable ENABLE KEYS;
    SET FOREIGN_KEY_CHECKS=1;
    
    点赞 评论 复制链接分享
  • csdnceshi79 python小菜 6年前

    In phpMyAdmin you can select multiple rows then click the delete action. You'll enter a screen which lists the delete queries, you can uncheck the Foreign key check, and click on Yes to execute them.

    This will enable you to delete rows even if there is a ON DELETE restriction constrain.

    点赞 评论 复制链接分享
  • csdnceshi51 旧行李 3年前

    To Disable: SET GLOBAL FOREIGN_KEY_CHECKS=0;

    To Enable: SET GLOBAL FOREIGN_KEY_CHECKS=1;

    点赞 评论 复制链接分享
  • csdnceshi74 7*4 6年前

    It's not a good idea to set a foreign key constraint to 0, because if you do, your database would not ensure it is not violating referential integrity. This could lead to inaccurate, misleading, or incomplete data.

    You make a foreign key for a reason: because all the values in the child column shall be the same as a value in the parent column. If there are no foreign key constraints, a child row can have a value that is not in the parent row, which would lead to inaccurate data.

    For instance, let's say you have a website for students to login and every student must register for an account as a user. You have one table for user ids, with user id as a primary key; and another table for student accounts, with student id as a column. Since every student must have a user id, it would make sense to make the student id from the student accounts table a foreign key that references the primary key user id in the user ids table. If there are no foreign key checks, a student could end up having a student id and no user id, which means a student can get an account without being a user, which is wrong.

    Imagine if it happens to a large amount of data. That's why you need the foreign key check.

    It's best to figure out what is causing the error. Most likely, you are trying to delete from a parent row without deleting from a child row. Try deleting from the child row before deleting from the parent row.

    点赞 评论 复制链接分享