מפתחות זרים ב-MySQL

במאמר הקודם למדנו על שני סוגי מפתחות – Primary keys ו-unique keys. במאמר זה אנו נלמד על מפתח זר שהוא סוג שלישי של מפתח.

מפתח זר הוא מפתח ראשי של טבלה מסוימת שנכנס כמפתח ראשי של טבלה אחרת שיש לה קשר לטבלה הראשונה. במאמר על מודלים של מסדי נתונים הראינו שבמודל מסד הנתונים היחסי אפשר ליצור קשרים בין טבלאות ללא קשר בין מקומן ההיררכי. במאמר על הנורמליזציה הראינו את החשיבות שיש לקשרים האלו בבניית טבלאות מנורמלות. את הקשרים האלו אנו עושים באמצעות מפתח זר.

בואו וניקח כדוגמא שתי טבלאות, אחת היא טבלת לקוחות פשוטה מהדוגמא של המפתח הראשי. לטבלה זו יש ארבעה שדות – מספר סידורי של הלקוח (שהוא ה-PRIMARY KEY), שם משפחה, שם פרטי ותאריך לידה.
טבלה שניה היא טבלה של חיובי לקוחות – כמה כסף הלקוח חייב לנו. בשדה אחד יש לנו מספר סידורי של לקוח ובשדה השני כמה כסף הוא חייב.

העיצוב של מסד הנתונים נראה כך:

על מנת ליצור את העיצוב הזה, עלינו ליצור שתי טבלאות – הראשונה של לקוחות והשניה של חיובים. בואו וניצור את הטבלה הראשונה:

CREATE TABLE customers (customer_id INT NOT NULL, customer_first_name VARCHAR(20), customer_last_name VARCHAR(20), customer_birth_age 

SMALLINT, PRIMARY KEY (customer_id)) TYPE = INNODB;

כפי שניתן לראות זו טבלה רגילה לחלוטין ואין על מה להרחיב את הדיבור. באופן תיאורטי אני יכול ליצור טבלה נוספת לא לשבור את הראש אלא להגדיר שני PRIMARY KEYS – אחד לכל טבלה ולגמור את הסיפור. אבל אם אנו משתמשים במנוע טבלה מסוג InnonDB אפשר בקלות להגדיר שהמפתח של טבלת חיובי הלקוחות יהיה המפתח של טבלת הלקוחות. מה אנו משיגים בזה? זהות חד חד ערכית בין חיובי הלקוחות ללקוחות עצמם ללא סיכוי רב לטעות.

כאשר אני יוצר מפתח זר, נוצרת תלות אמיתית מהטבלה הבת לטבלה האם. אם אני אנסה להכניס ערך כלשהו שאינו נמצא בטבלה האם, אני לא אצליח לעשות את זה. אם אני אנסה למחוק את טבלת האם כאשר יש נתונים התלויים בה בטבלת הבת, אני לא אצליח לעשות את זה. שימוש במפתח זר מבטיח מסד נתונים עמיד הרבה יותר בפני טעויות של מפתחים.

בואו ונדגים. טבלה אחת של לקוחות כבר יצרנו ואין הרבה מה לומר עליה, טבלה רגילה ופשוטה של לקוחות כאשר ה-customer_id הוא PRIMARY KEY. הכנסתי לתוכה כמה נתונים:

+-------------+---------------------+--------------------+--------------------+
| customer_id | customer_first_name | customer_last_name | customer_birth_age |
+-------------+---------------------+--------------------+--------------------+
|           1 | Ran                 | Bar-Zik            |               1977 |
|           2 | Moshe               | Cohen              |               1979 |
|           3 | Gal                 | Mizrachi           |               1986 |
|           4 | David               | Levi               |               1973 |
+-------------+---------------------+--------------------+--------------------+

עכשיו ניצור את הטבלה השניה, invoice. היא טבלה מאד פשוטה – יש שם רק שני שדות – שדה של סכום ושדה של customer_id שהוא יהיה המפתח הזר שלנו, הוא יגיע מהטבלה של ה-customer. יצירת טבלת invoice נעשית באופן הבא:

CREATE TABLE invoice (customer_id INT NOT NULL, sum INT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customers (customer_id)) TYPE = INNODB;

מה שיוצר את המפתח הזר הוא ההכרזה עליו: FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ההכרזה היא די פשוטה – אנו מציינים בתחילה מה השדה שיקושר בטבלה שלנו ואחרי כן לאיזו טבלה ולאיזה שדה בטבלה לקשר. זה הכל. אם נעשה DESCRIBE לטבלה החדשה שיצרנו, נראה את הדבר הבא:

+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| customer_id | int(11) | NO   | MUL | NULL    |       |
| sum         | int(11) | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

ועכשיו להדגמת היכולת. כאמור הכנסתי נתונים של 4 לקוחות ל-customers שלי. בואו וננסה להכניס לטבלת invoice נתונים על הלקוחות האלו:

INSERT INTO invoice (customer_id , sum) VALUES (1,245), (2,347);

הכנסת הנתונים תעבור ללא בעיה, כיוון שאנו מכניסים נתונים עבור מספר לקוח 1 ו-2 שקיימים בטבלת customer. בואו וננסה להכניס נתון עבור לקוח מספר 5 – שאינו נמצא בטבלת customer.

INSERT INTO invoice (customer_id , sum) VALUES (5,700);

במקרה זה אנו נקבל הודעת שגיאה:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`internet_database/invoice`, CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

אם ננסה למחוק שורה מטבלת customer שעשינו לה כבר קישור לטבלת invoice (כמו לקוח מספר 2) אז גם נקבל שגיאה.

DELETE FROM customers WHERE customer_id=2;

זו השגיאה שנקבל:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`inte
rnet_database/invoice`, CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `cus
tomers` (`customer_id`))

מחיקת מפתח זר

אם אנו לא צריכים את המפתח הזר מסיבה כלשהי, אנו יכולים פשוט למחוק אותו ובכך לחתוך את הקשר בין שתי הטבלאות. על מנת למחוק את המפתח הזר אני צריך לדעת את מספר הזיהוי שלו. מה זה מספר הזיהוי? אנו יודעים אותו אם אנו נכתוב את פקודת SHOW CREATE. פקודת SHOW CREATE היא פקודה שמאפשרת לנו לראות סינטקס שמשחזר את

מבנה הטבלה המקורי שלנו. אם נכתוב את הפקודה:

SHOW CREATE TABLE invoice;

אנו נראה את הטקסט הבא:

CREATE TABLE `invoice` (
  `customer_id` int(11) NOT NULL,
  `sum` int(11) default NULL,
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

כאשר invoice_ibfk_1 הוא השם של הקישור שלנו. על מנת למחוק אותו, כל מה שעלינו לעשות זה ALTER TABLE ולהשתמש בשם הזה:

ALTER TABLE invoice DROP FOREIGN KEY invoice_ibfk_1;

שליטה במתרחש בטבלאות בנות כאשר יש מחיקה או עדכון בטבלת האם

ראינו שכאשר אני יוצר מפתח זר, מחיקה או עדכון של השדה שאליו נעשה הקישור היא בלתי אפשרית. ניתן לאפשר את המחיקה או את העדכון של השדה הזר אם אני יוצר הוראות

מדויקות מה לעשות כאשר יש מחיקה או עדכון. את זה אנו עושים בשלב יצירת המפתח הזר. אם ניקח את טבלת invoice כדוגמא, אני מעוניין שאם אני מוחק לקוח ממסד הנתונים, גם הערך שלו בטבלת invoice לא יהיה קיים. לפיכך מחיקה בטבלת האם (customers) תביא למחיקת רשומה בטבלת הבת (invoice):

ALTER TABLE invoice ADD FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON
DELETE CASCADE;

בפקודה זו אני מוסיף מפתח זר בסינטקס שזהה לחלוטין להוספת מפתח זר לטבלה כפי שלמדנו והשתמשנו בו בדוגמאות הקודמות, אך פה אני מוסיף גם את ON DELETE CASCADE – ארגומנט שמודיע שאם אני אמחוק רשומה מטבלת customer, היא תמחק גם מ-invoice. מחיקה של רשומה מטבלת customer תגרור מחיקה גם מ-invoice.

ישנן עוד אפשרויות לפעולות כאשר רשומה בטבלת האם נמחקת:

1. ON DELETE CASCADE – מחיקת הרשומה בטבלת הבת כאשר רשומה בטבלת האם נמחקת.
2. ON DELETE SET NULL – הפיכת השדה ל-NULL כאשר רשומה בטבלת האם נמחקת. (שאר המידע נשמר).
3. ON DELETE RESTRICT – זה הדיפולט שלנו – חוסר יכולת למחוק רשימה בטבלת האם.
4. ON DELETE NO ACTION – זהה לחלוטין ל-RESTRICT מלבד הבדל אחד. RESTRICT בודק אם יש קשר בין השדות לפני הניסיון לשינוי וה-NO ACTION עושה את זה רק

לאחר מכן. מבחינה עקרונית ההשפעה היא זהה לגמרי.

אני יכול גם להכניס הנחיות במקרה של עדכון – האם לעדכן גם הרשומה בטבלת הבת? האם למחוק אותה? האם לאסור על עדכונים (שזו ההתנהגות הדיפולטיבית)? הסינטקס זהה כמעט לחלוטין ל-ON DELETE. רק שפה אני משתמש ב-ON UPDATE.

כך למשל, אם אני רוצה שבכל פעם שאני מעדכן את הרשומה הראשית שהרשומה המשנית תתעדכן, אני אשתמש בפקודה הבאה:

ALTER TABLE invoice ADD FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON UPDATE CASCADE;

ואם אני אשנה את ה-customer_id של משתמש מספר 1:

UPDATE customers SET customer_id=6 WHERE customer_id=1;

אני אראה שגם בטבלת invoice מספר הלקוח ישתנה. כפי שציינתי קודם, האופרטורים זהים לחלוטין ל-ON DELETE:

1. ON UPDATE CASCADE – עדכון הרשומה בטבלת הבת כאשר רשומה בטבלת האם מתעדכנת
2. ON UPDATE SET NULL – הפיכת השדה ל-NULL כאשר רשומה בטבלת האם מתעדכנת. (שאר המידע נשמר).
3. ON UPDATE RESTRICT – זה הדיפולט שלנו – חוסר יכולת לעדכן את השדה המקושר בטבלת האם.
4. ON UPDATE NO ACTION – זהה לחלוטין ל-RESTRICT.

אפשר כמובן גם לשלב בין ON UPDATE ל-ON DELETE:

ALTER TABLE invoice ADD FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE NO ACTION ON UPDATE CASCADE;

בדיקת מפתחות זרים

אנו יכולים לראות אם לטבלה מסוימת יש מפתחות זרים אם אנו נשתמש ב-SHOW TABLE STATUS FROM באופן הבא:

SHOW TABLE STATUS FROM internet_database LIKE 'customers';

הפלט הוא טבלה ארוכה כאורך הגלות ושם יש מידע על המפתחות הזרים.

⚠️המדריכים כאן הם טעימה בלבד! יש ספר מקיף המלמד MySQL מרמת בסיס ועד רמה מתקדמת. הספר הדיגיטלי, שרלוונטי גם לכל מסדי הנתונים שעובדים עם SQL – זמין כאן כחלק מהפרויקט "ללמוד ג'אווהסקריפט בעברית" – זו הדרך הטובה ביותר ללמוד באופן מקיף MySQL.

במאמר הבא אנו נלמד על כלים שיש לנו ב-MySQL על מנת לבדוק, לתחזק ולהפוך את מסד הנתונים שלנו למהיר הרבה יותר.