บทความนี้ต่อจาก การสร้าง FOREIGN KEY Constraints ใน MySQL เพื่อทดสอบการ เพิ่ม แก้ไข ลบ ข้อมูล โดยในเวลาเริ่มแต่ละหัวข้อจะใช้ข้อมูลนี้เป็นหลัก
mysql> SELECT * FROM groups; +----------+------------+ | group_id | group_name | +----------+------------+ | 101 | Accounting | | 102 | Engineer | | 103 | IT | | 104 | Manager | +----------+------------+ 4 rows in set (0.00 sec)
mysql> SELECT * FROM users; +---------+----------+-----------+ | user_id | group_id | user_name | +---------+----------+-----------+ | 501 | 101 | Ms.A | | 502 | 102 | Ms.B | | 503 | 102 | Mr.C | | 504 | 103 | Mr.D | | 505 | 104 | Mr.E | +---------+----------+-----------+ 5 rows in set (0.00 sec)
การ DELETE ข้อมูล
หลังจากใส่ FOREIGN KEY ในตาราง users เรียบร้อยแล้ว ทดสอบการลบ DELETE กลุ่ม ‘IT’ ออกจาก table ‘groups’
mysql> DELETE FROM groups WHERE group_id='103';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`test_db`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`))
ข้อมูลกลุ่ม ‘IT’ ยังอยู่ใน table ‘groups’
mysql> SELECT * FROM groups; +----------+------------+ | group_id | group_name | +----------+------------+ | 101 | Accounting | | 102 | Engineer | | 103 | IT | | 104 | Manager | +----------+------------+ 4 rows in set (0.00 sec)
เราไม่สามารถลบ group ‘IT’ ได้ เนื่องจากมีการอ้างอิงจาก table ‘users’
หากต้องการจะลบ ต้องแก้ไขข้อมูลที่มีการอ้างอิงมายัง group ‘IT’ นี้ (group_id=103) เช่นในที่นี้ต้องเปลี่ยน group_id ของ ‘Mr.D’ ไปเป็นกลุ่มอื่น เช่นเปลี่ยนเป็น กลุ่ม ‘Engineer’
ตรวจสอบว่าใครอยู่ group ‘IT’ บ้าง
mysql> SELECT * FROM users WHERE group_id=103; +---------+----------+-----------+ | user_id | group_id | user_name | +---------+----------+-----------+ | 504 | 103 | Mr.D | +---------+----------+-----------+ 1 row in set (0.00 sec)
เปลี่ยนกลุ่มของ ‘MR.D’ เป็น Engineer
mysql> UPDATE users SET group_id=102 WHERE user_id=504; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0
ตรวจสอบว่าใครอยู่ group ‘IT’ อีกครั้ง
mysql> SELECT * FROM users WHERE group_id=103; Empty set (0.00 sec)
ทดสอบการลบกลุ่ม IT
mysql> DELETE FROM groups WHERE group_id=103; Query OK, 1 row affected (0.00 sec)
ครั้งนี้จะสามารถลบได้แล้ว เพราะไม่มี user คนไหนอยู่ในกลุ่มนี้แล้ว
การ INSERT, UPDATE ข้อมูล
จะด้วยความผิดพลาดของโปรแกรมหรือตั้งใจ สมมติเราเพิ่ม users ใหม่ แล้วตั้ง group_id=109 ซึ่งไม่มีอยู่ใน table ‘groups’
mysql> INSERT INTO users (user_id, group_id, user_name) VALUES (506, 109, 'Mr.X'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`))
เราจะไม่สามารถ INSERT ข้อมูลแบบนี้ได้ ต้องไปสร้าง group_id 109 ใน table ‘groups’ ก่อน
เช่นเดียวกัน เราไม่สามารถ UPDATE ข้อมูลที่ไม่ถูกต้องได้
mysql> UPDATE users SET group_id=109 WHERE user_id=505;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test_db`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`))
การ DROP TABLE
หากยังมีการอ้างอิงฟิลด์ข้อมูลอยู่ เราไม่สามารถ DROP TABLE ที่ยังมีข้อมูลอ้างอิงอยู่ได้ เช่นในที่นี้เราไม่สามารถ DROP TABLE groups ได้
mysql> DROP TABLE groups;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ถึงแม้ไม่มีข้อมูล rows ใดเลยใน users ก็ไม่สามารถ DROP TABLE groups ได้
mysql> DELETE FROM users; Query OK, 5 rows affected (0.00 sec)
mysql> DROP TABLE groups; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails