MySQL에서 외래 키 제약 조건을 일시적으로 비활성화하는 방법은 무엇입니까?

MySQL에서 외래 키 제약 조건을 일시적으로 비활성화하는 방법은 무엇입니까?

---

MySQL에서 제약 조건을 일시적으로 비활성화 할 수 있습니까?

두 개의 Django 모델이 있는데, 각각 다른 모델에 대한 ForeignKey가 있습니다. 모델의 인스턴스를 삭제하면 ForeignKey 제약으로 인해 오류가 반환됩니다.

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()

제약 조건을 일시적으로 비활성화하고 어쨌든 삭제할 수 있습니까?

---

시도 DISABLE KEYS하거나

SET FOREIGN_KEY_CHECKS=0;

확실히

SET FOREIGN_KEY_CHECKS=1;

후.

---

전체적으로 외래 키 제약 조건을 해제하려면 다음을 수행하십시오.

SET GLOBAL FOREIGN_KEY_CHECKS=0;

완료되면 다시 설정하는 것을 잊지 마십시오.

SET GLOBAL FOREIGN_KEY_CHECKS=1;

경고 : 단일 사용자 모드 유지 관리를 수행하는 경우에만이 작업을 수행해야합니다. 데이터 불일치가 발생할 수 있습니다. 예를 들어 mysqldump 출력을 사용하여 많은 양의 데이터를 업로드 할 때 매우 유용합니다.

---

나는 일반적으로 테이블을 자르고 싶을 때 외래 키 제약 조건을 비활성화 하고이 답변으로 계속 돌아 오기 때문에 이것은 미래를위한 것입니다.

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE table;
SET FOREIGN_KEY_CHECKS=1;

---

제약 조건을 비활성화하는 대신 ON DELETE SET NULL로 영구적으로 수정하십시오. 그것은 비슷한 일을 할 것이며 키 검사를 켜고 끌 필요가 없을 것입니다. 이렇게 :

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

이것 ( http://dev.mysql.com/doc/refman/5.5/en/alter-table.html )과 이것 ( http://dev.mysql.com/doc/refman/5.5/en /create-table-foreign-keys.html ).

---

전체적으로 외래 키 제약 조건을 끄려면 :

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

및 활성 외래 키 제약

SET GLOBAL FOREIGN_KEY_CHECKS = 1;

---

phpmyadmin을 사용한 매우 간단한 솔루션 :

• 테이블에서 SQL탭으로 이동
• After you edit the SQL command that you want to run, there is a check box next to GO, named 'Enable foreign key checks' .
• Uncheck this check box and run your SQL . It will be automatically re-checked after executing.

---

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()

---

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;

---

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.

---

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.

참고URL : https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql