วิธีแก้ไข MySQL ไม่สามารถเพิ่มข้อผิดพลาดข้อ จำกัด ของคีย์ต่างประเทศ

คุณจะแก้ไขข้อผิดพลาด " MySQL Cannot Add Foreign Key Constraint " ได้อย่างไร คุณอาจได้รับข้อความแสดงข้อผิดพลาดต่อไปนี้เมื่อพยายามเพิ่มข้อจำกัดของคีย์นอกไปยังตาราง MySQL: " ข้อผิดพลาด 1215 (HY000): ไม่สามารถเพิ่มข้อจำกัดของคีย์นอก "

ข้อผิดพลาดเช่นนี้เป็นเรื่องทั่วไปและไม่ได้ให้รายละเอียดเฉพาะเจาะจงเกี่ยวกับสาเหตุที่ทำให้ MySQL ไม่สามารถแทรกข้อจำกัดของคีย์นอกลงในตารางได้ ในการแก้ไขปัญหานี้ คุณจะต้องระบุสาเหตุที่แท้จริงของปัญหา

บทความนี้จะอธิบายสาเหตุของข้อผิดพลาด “#1215 – Cannot Add Foreign Key Constraint” ของ MySQL วิธีค้นหาสาเหตุและวิธีแก้ไขด้วยสี่วิธีที่ได้ผล

อะไรคือสาเหตุของข้อผิดพลาด “#1215 – ไม่สามารถเพิ่มข้อ จำกัด ของคีย์ต่างประเทศ”

ข้อผิดพลาดนี้อาจมีสาเหตุหลายประการ เพื่อระบุสาเหตุของข้อผิดพลาดนี้ วิธีที่ดีที่สุดคือการตรวจสอบส่วนข้อผิดพลาดล่าสุดของคีย์ต่างประเทศของการแสดงสถานะ INNODB ของเครื่องยนต์

ด้วยวิธีนี้คุณจะเข้าใจปัญหาได้ดีขึ้น คุณจะสามารถระบุกรณีของคุณได้จากรายการต่อไปนี้:

โดยปกติแล้ว เมื่อโหลดดัมพ์ ตารางหรือดัชนีที่ข้อจำกัดอ้างถึงจะไม่มีอยู่

บางครั้ง ตารางฐานข้อมูลที่ลูกค้าต้องการเพิ่มข้อจำกัดไม่มีอยู่ ส่งผลให้เกิดข้อความแสดงข้อผิดพลาด 1215

อย่างไรก็ตาม ไม่มีข้อบ่งชี้ว่าตารางนั้นไม่มีอยู่ ดังนั้นขอแนะนำให้คุณใช้คำสั่งเพื่อกำหนดว่าตารางใดบ้างที่มีอยู่ในฐานข้อมูล

เรียกใช้ SHOW TABLES หรือ SHOW CREATE TABLE ในแต่ละตารางหลัก ตารางใดๆ ที่ส่งคืนข้อผิดพลาด 1146 แสดงว่าตารางถูกสร้างขึ้นในลำดับที่ไม่ถูกต้อง

วิธีแก้ไขคือเรียกใช้ CREATE TABLE ที่ขาดหายไปแล้วลองอีกครั้งหรือปิดการตรวจสอบคีย์ต่างประเทศชั่วคราว จำเป็นต้องดำเนินการขั้นตอนนี้ระหว่างการคืนค่าการสำรองข้อมูลซึ่งอาจมีการอ้างอิงแบบวงกลม

เรียกใช้รหัสต่อไปนี้เพื่อแก้ไขข้อผิดพลาด:

 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; -- restore your backup within THIS session SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

ตารางหรือดัชนีในเครื่องหมายคำพูดอ้างอิงในทางที่ผิด

หากคุณต้องการวินิจฉัยข้อผิดพลาดนี้ ให้ตรวจสอบการประกาศ FOREIGN KEY แต่ละรายการและตรวจสอบให้แน่ใจว่าไม่มีเครื่องหมายอัญประกาศรอบๆ ตัวระบุออบเจกต์ หรือมีอัญประกาศรอบๆ ตารางและหนึ่งคู่รอบๆ ชื่อคอลัมน์แต่ละคอลัมน์

สิ่งนี้สามารถแก้ไขได้โดยการไม่อ้างถึงสิ่งใดๆ หรืออ้างตารางและคอลัมน์แยกกัน

คีย์ท้องถิ่น ตารางต่างประเทศ หรือคอลัมน์ในการอ้างอิงข้อจำกัดมีการพิมพ์ผิด

เป็นไปได้ที่จะวินิจฉัยข้อผิดพลาดนี้โดยการเรียกใช้ SHOW TABLES และ SHOW COLUMNS และเปรียบเทียบสตริงกับสตริงที่ระบุในการประกาศ REFERENCES ของคุณ การพิมพ์ผิดควรได้รับการแก้ไขทันทีที่พบ

คอลัมน์ที่ข้อจำกัดอ้างถึงไม่ใช่ประเภทหรือความกว้างเดียวกันกับคอลัมน์ต่างประเทศ

สำหรับการวิเคราะห์ ใช้พาเรนต์ SHOW CREATE TABLE เพื่อให้แน่ใจว่าคอลัมน์ภายในเครื่องและคอลัมน์ที่อ้างอิงมีข้อมูลและความกว้างประเภทเดียวกัน

ปัญหาสามารถแก้ไขได้โดยแก้ไขคำสั่ง DDL เพื่อให้แน่ใจว่าคำนิยามคอลัมน์ในตารางลูกตรงกับตารางหลัก

วัตถุแปลกปลอมไม่ใช่กุญแจทุกชนิด

เมื่อต้องการวินิจฉัยปัญหานี้ ใช้พาเรนต์ SHOW CREATE TABLE เพื่อตรวจสอบส่วน REFERENCES ของตาราง เพื่อให้แน่ใจว่าคอลัมน์ที่ชี้ไปไม่ได้ถูกจัดทำดัชนี

แก้ไขปัญหาโดยทำให้คอลัมน์เป็น KEY, UNIQUE KEY หรือ PRIMARY KEY

คีย์นอกคือ PK หรือ UK แบบหลายคอลัมน์ โดยที่คอลัมน์ที่อ้างอิงไม่ใช่คอลัมน์ซ้ายสุด

ในการวินิจฉัยข้อผิดพลาดนี้ ให้ดำเนินการคำสั่ง SHOW CREATE TABLE parent เพื่อพิจารณาว่าส่วน REFERENCES ชี้ไปที่คอลัมน์ที่มีอยู่ในดัชนีหลายคอลัมน์บางคอลัมน์แต่ไม่ใช่ส่วนซ้ายสุดในคำจำกัดความหรือไม่

คุณสามารถแก้ไขปัญหาได้โดยการเพิ่มดัชนีลงในตารางหลักโดยที่คอลัมน์ที่อ้างอิงอยู่ทางซ้ายสุดหรือคอลัมน์เดียว

ชุดอักขระ/การเรียงหน้าที่แตกต่างกันระหว่างสองตาราง/คอลัมน์

ในการวินิจฉัยปัญหา ให้รัน SHOW CREATE TABLE parent และตรวจสอบว่า CHARACTER SET และ COLLATE ของคอลัมน์ลูกตรงกับส่วนในตารางพาเรนต์

ในการแก้ไขปัญหานี้ ตรวจสอบให้แน่ใจว่าตารางรอง DDL ตรงกับชุดอักขระและการเรียงของตาราง/คอลัมน์หลัก หรือเปลี่ยนตารางหลักเพื่อให้คำจำกัดความตรงกับคำจำกัดความของตารางรอง

ตารางหลักไม่ได้ใช้ InnoDB

สามารถวินิจฉัยปัญหาได้โดยการรันพาเรนต์ SHOW CREATE TABLE และตรวจหา ENGINE=INNODB

แก้ไขตารางพาเรนต์เพื่อเปลี่ยนจาก MySQL เป็น InnoDB เพื่อแก้ไขปัญหานี้

จะค้นหาสาเหตุของข้อผิดพลาด "ไม่สามารถเพิ่มข้อ จำกัด ของคีย์ต่างประเทศ" ได้อย่างไร

ข้อผิดพลาดนี้ไม่ได้ระบุถึงสาเหตุเฉพาะที่ทำให้ MySQL ไม่สามารถใช้ข้อจำกัดของคีย์นอกในตาราง ซึ่งเป็นเรื่องทั่วไป

เพื่อช่วยแก้ไขปัญหานี้ คุณจะต้องระบุสาเหตุที่แท้จริง คุณสามารถทำได้โดยเรียกใช้แบบสอบถามต่อไปนี้ในไคลเอ็นต์ MySQL ของคุณ:

 SHOW ENGINE INNODB STATUS;

ฟิลด์สามฟิลด์จะถูกส่งกลับจากแบบสอบถามนี้: ชนิด ชื่อ และสถานะ ข้อผิดพลาดล่าสุดของคีย์ต่างประเทศสามารถระบุได้โดยการขยายคอลัมน์สถานะของผลลัพธ์

ส่วนนี้มีจุดมุ่งหมายเพื่อระบุว่าเหตุใดการสืบค้นข้อมูลการแก้ไขครั้งล่าสุดจึงล้มเหลว เพื่อที่คุณจะได้แก้ไขปัญหา

มักจะมีความไม่ตรงกันระหว่างประเภทของคอลัมน์ในตารางหลักและตารางต่างประเทศซึ่งเป็นสาเหตุของปัญหานี้

ปัญหาอาจเกิดจากความไม่ตรงกันระหว่างประเภทกลไกของตารางสองประเภท เช่น MyISAM และ InnoDB

นอกจากนี้ยังเป็นไปได้เนื่องจากการเรียงที่แตกต่างกันระหว่างตาราง ตารางหนึ่งใช้ UTF-8 และอีกตารางหนึ่งใช้ latin1

คุณควรใส่ใจกับปัจจัยต่อไปนี้:

Engine : แต่ละตารางควรได้รับการกำหนดค่าด้วยกลไกฐานข้อมูลเดียวกัน เช่น InnoDB

ประเภท ข้อมูล : ในหนึ่งตาราง ทั้งสองคอลัมน์ควรมี datatype. int(11) " เหมือนกัน datatype. int(11) “ ในขณะที่อีกอันหนึ่ง smallint(5) จะทำให้เกิดปัญหา

Collation : เพื่อให้แน่ใจว่าเข้ากันได้ ชุดอักขระทั้งสองคอลัมน์ควรเหมือนกัน เช่น UTF8

ข้อควร ระวัง : คอลัมน์ยังคงสามารถมีคอลัมน์อื่นได้ แม้ว่าตารางของคุณจะมีการเรียงเหมือนกันก็ตาม

ไม่ซ้ำกัน : Foreign Key ควรอ้างถึงฟิลด์ที่ไม่ซ้ำในตารางอ้างอิง ซึ่งโดยปกติจะเป็นฟิลด์หลัก

Ungisned : เป็นไปได้ที่ตารางหนึ่งจะมีคีย์ที่ไม่ได้ลงนามและอีกตารางหนึ่งจะไม่มีคีย์ที่ไม่ได้ลงนาม

วิธีแก้ไขข้อผิดพลาด “#1215 – ไม่สามารถเพิ่มข้อ จำกัด คีย์ต่างประเทศ” (4 วิธี)

เราได้กล่าวถึงสาเหตุต่างๆ ของข้อความแสดงข้อผิดพลาดนี้แล้ว เราได้ไปยังขั้นตอนถัดไป ข้อผิดพลาดนี้จำเป็นต้องได้รับการแก้ไข ดังนั้นเรามาดูวิธีการแก้ไขด้วยสี่วิธีที่ได้ผล

วิธีที่ 1: ตรวจสอบโต๊ะว่าง

ในการเริ่มต้นแก้ไขข้อผิดพลาด คุณสามารถตรวจสอบว่ามีตารางอยู่หรือไม่โดยเรียกใช้คำสั่งต่อไปนี้:

 show tables;

เห็นได้ชัดจากโค้ดนี้ว่าสามารถสร้างข้อจำกัดได้ตั้งแต่มีตาราง

วิธีที่ 2: ตรวจสอบให้แน่ใจว่าตารางหลักกำลังใช้ InnoDB

การตรวจสอบเพิ่มเติมสามารถทำได้โดยการตรวจสอบกลไกการจัดเก็บข้อมูลที่ตั้งค่าเป็นตารางฐานข้อมูล สามารถใช้คำสั่งต่อไปนี้ได้:

 SHOW CREATE TABLE parent

ผลลัพธ์คือคุณจะพบว่า InnoDB ไม่ใช่เครื่องมือจัดเก็บข้อมูล เนื่องจาก ENGINE=MYISAM ถูกตั้งค่าไว้ คุณสามารถเปลี่ยนกลไกการจัดเก็บข้อมูลได้โดยการรันคำสั่งต่อไปนี้:

 ALTER TABLE parent ENGINE=INNODB;

ในที่สุดก็เป็นไปได้ที่จะแก้ไขข้อผิดพลาดด้วยวิธีนี้

วิธีที่ 3: ตรวจสอบว่ามีคำพูดใดที่ไม่เหมาะสมหรือไม่

สำหรับการประกาศ FOREIGN KEY ทุกครั้ง ตรวจสอบให้แน่ใจว่าไม่มีการใส่เครื่องหมายอัญประกาศรอบตัวระบุอ็อบเจ็กต์หรือเครื่องหมายอัญประกาศแยกต่างหากล้อมรอบชื่อตารางและคอลัมน์

ด้วยวิธีนี้ คุณไม่จำเป็นต้องอ้างอิงสิ่งใดหรืออ้างคอลัมน์และตารางแยกกันเพื่อแก้ไขข้อผิดพลาด

วิธีที่ 4: การตรวจสอบว่าคีย์มีอยู่หรือไม่

เมื่อป้อนคำสั่ง MySQL จำเป็นต้องตรวจสอบให้แน่ใจว่าป้อนคำสั่งที่ถูกต้อง การตรวจสอบข้ามช่วยให้มั่นใจว่าลูกค้าไม่ได้อ้างอิงคีย์ตารางที่ไม่มีอยู่ในตารางเป้าหมาย

ดังนั้นจึงจำเป็นต้องแก้ไขลูกค้าเกี่ยวกับวิธีการใช้คีย์บนโต๊ะ เป็นไปได้ว่าคอลัมน์ที่ใช้สำหรับ ON DELETE SET NULL ไม่มีคำจำกัดความที่เป็นโมฆะในบางกรณี

เพื่อป้องกันข้อผิดพลาด MySQL 1215 คุณต้องแน่ใจว่าคอลัมน์ถูกตั้งค่าเป็นค่า Null เริ่มต้น

บทสรุป

บทความนี้ได้อธิบายสาเหตุของข้อผิดพลาด “#1215 – Cannot Add Foreign Key Constraint” ของ MySQL วิธีค้นหาสาเหตุและวิธีแก้ไขด้วยสี่วิธีที่ได้ผล

ขอขอบคุณที่สละเวลาตรวจสอบบทความนี้ เรามีความยินดีที่จะนำเสนอให้คุณ ในส่วนความคิดเห็น โปรดโพสต์คำถามหรือความคิดเห็นใดๆ

โปรดกดไลค์และติดตามเพจ Facebook และ Twitter ของเราหากคุณต้องการติดตามข่าวสารล่าสุดอยู่เสมอ