วิธีแก้ MySQL ERROR 1451,1452 Cannot add or update a child row: a foreign key constraint fails


640px-MySQL.svg.png (640×331)

หรืออะไรก็ตามที่มันจะแจ้งว่า
a foreign key constraint fails
การที่เราสร้าง foreign key ขึ้นมานั้น ตารางที่เราเชื่อมกัน (relation) จะต้องมีข้อมูลที่สัมพันธ์กัน!

e.g.

ตาราง employee มี {id, name, position} id เป็น PK 
ตาราง emp_position มี {pos_id, pos_name} pos_id เป็น PK
เราต้องการทำ employee.position เป็น FK ของ emp_position ข้อมูลที่เรามีนั้น จะต้องสัมพันธ์กันดังนี้

* ถ้าเพิ่มข้อมูล {1,'myname', 1} ในตาราง employee ค่า 1 คือ FK ที่เราสร้างไว้ ถ้ามันพบค่า 1 ใน pos_id ของตาราง emp_position เราก็สามารถบันทึกข้อมูลได้!

แต่ถ้าไม่พบ มันก็จะแจ้ง a foreign key constraint fails 

ถ้าต้องการลบ record บางอันออก แต่ field นั้นถูกสร้าง FK อยู่ ก็อาจจะลบไม่ได้ ขึ้นอยู่กับว่ามันอนุญาตให้ลบหรือป่าว แต่โดยส่วนมากแล้ว การทำ FK นั้น เป็นการป้องกันไม่ให้ลบข้อมูลที่ถูกใช้งานอยู่ เพื่อป้องกันความผิดพลาดที่อาจเกิดขึ้นได้
เช่น

e.g. มีสมาชิก id หนึ่ง ทำการซื้อสินค้าไว้ตลอดระยะเวลา 2 ปี ใน record ก็จะมีรายการสั่งซื้อ รวมทั้ง order ของเราเก็บเอาไว้ แต่วันหนึ่ง มีการลบ id ของสมาชิกคนนี้ออก เมือ่เรา query ข้อมูลการซื้อขายในช่วง 2 ปี จะพบข้อผิดพลาดในรายการสั่งซื้อ เนื่องจากไม่ทราบว่าเป็นของใครนั่นเอง

วิธีตรวจสอบ โดยการข้อมูลที่มันไม่สัมพันธ์กัน ทำได้แบบนี้ครับ
SELECT * FROM employee
WHERE employee.position NOT IN (SELECT pos_id FROM emp_position)

เมื่อพบแล้ว วิธีแก้ไขก็คือ ทำข้อมูลให้สัมพันธ์กัน "ทั้งตาราง" (ส่วนมากแล้วถ้าเป็นตารางเปล่าที่ create ใหม่ จะไม่ค่อยพบปัญหา) ปัญหาจะพบตอนที่ INSERT ข้อมูล ถ้าเป็นตารางที่สร้างมาแล้ว จะพบปัญหาตอน UPDATE ข้อมูล เราก็ใช้วิธียังไงก็ได้ครับ ให้มันตรงกัน ไม่ว่าจะเพิ่ม หรือ แก้ไข หรือ ลบ employee บาง record ทิ้งไปเลยก็ได้ เพื่อให้เราสร้าง foreign key สำเร็จ

คำอธิบายเกี่ยวกับ Foreign Key On delete & update

ดูเพิ่มเติมที่
http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa

http://spalinux.com/2010/01/creating_mysql_foreign_key_constraints

ป้ายกำกับ: , , ,