אינטרנט ישראל
  • ראשי
  • אודות רן בר-זיק ואינטרנט ישראל
  • ערוץ טלגרם
  • מסטודון
  • התחברו אלי בטוויטר
  • התחברו אלי בלינקדאין
  • ספר ג'אווהסקריפט
  • ראשי
  • אודות רן בר-זיק ואינטרנט ישראל
  • ערוץ טלגרם
  • מסטודון
  • התחברו אלי בטוויטר
  • התחברו אלי בלינקדאין
  • ספר ג'אווהסקריפט
ראשי » מדריכים » לימוד MySQL » מפתחות זרים ב-MySQL

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

רן בר-זיק פברואר 15, 2010 7:52 am אין תגובות

Foreign keys והסבר מפורט עליהם

כדאי תמיד להשאר מעודכנים! אם יש לכם טלגרם, בדקו את ערוץ הטלגרם של האתר שבו אני מעדכן על פוסטים חדשים 🙂 אם אתם רוצים ללמוד תכנות באופן מקיף ומסודר, הצטרפו לאלפי הלומדים בפרויקט "ללמוד ג'אווהסקריפט בעברית" שמלמד לתכנת בג'אווהסקריפט, ב-Node.js ובריאקט וגם מלמד על תרומה לקוד פתוח. גם ספרים דיגיטליים וגם ספרים מודפסים. בשיתוף הקריה האקדמית אונו ובתמיכת חברות מובילות כגון Wix, Outbrain, Elementor, Iron Source, Chegg, Really Good ועוד.

במאמר הקודם למדנו על שני סוגי מפתחות – 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 על מנת לבדוק, לתחזק ולהפוך את מסד הנתונים שלנו למהיר הרבה יותר.

כדאי תמיד להשאר מעודכנים! אם יש לכם טלגרם, בדקו את ערוץ הטלגרם של האתר שבו אני מעדכן על פוסטים חדשים 🙂 אם אתם רוצים ללמוד תכנות באופן מקיף ומסודר, הצטרפו לאלפי הלומדים בפרויקט "ללמוד ג'אווהסקריפט בעברית" שמלמד לתכנת בג'אווהסקריפט, ב-Node.js ובריאקט וגם מלמד על תרומה לקוד פתוח. גם ספרים דיגיטליים וגם ספרים מודפסים. בשיתוף הקריה האקדמית אונו ובתמיכת חברות מובילות כגון Wix, Outbrain, Elementor, Iron Source, Chegg, Really Good ועוד.
למדריך ה-MySQL הקודםמפתחות ראשיים ב-MySQL
למדריך ה-MySQL הבאאופטימיזציה בסיסית של MySQL
לתפריט המדריכים ל-MySQL
MySQL
ללמוד ג'אווהסקריפט בעברית

ללמוד לתכנת ג'אווהסקריפט בעברית שגייס יותר משלוש מאות אלף שקל ולמעלה מ-2000 תומכים - בואו ללמוד עכשיו איך לתכנת.

רשימת הנושאים
  • מדריכים
    • ריאקט
    • טייפסקריפט
    • ECMAScript 6
    • ES20XX
    • Node.js
    • Express
    • רספברי פיי
    • Babel
    • docker
    • MongoDB
    • Git
    • לימוד MySQL
    • SASS
    • jQuery
    • CSS3
    • HTML 5
    • SVN
    • LESS
  • פיתוח אינטרנט
    • פתרונות ומאמרים על פיתוח אינטרנט
    • jQuery Scripts
    • jQuery למתקדמים
    • יסודות בתכנות
    • נגישות אינטרנט
  • חדשות אינטרנט
  • מידע כללי על אינטרנט
    • רשת האינטרנט
    • בניית אתרי אינטרנט
  • rss logo

    לכל המאמרים

    לכל המאמרים שפורסמו באינטרנט ישראל משנת 2008 ועד עכשיו.
  • rss logo

    RSS Feed

    משתמשים בקורא RSS? אם כן, עקבו אחרי אינטרנט ישראל באמצעות פיד ה-RSS!
    מה זה RSS?
  • Twitter logo

    עקבו אחרי בטוויטר

    בחשבון הטוויטר שלי אני מפרסם עדכונים מהירים על חדשות בתחום התכנות והיזמות, התרעות על מצבי חירום ורכילות בוערת על תחום הווב.
    מה זה טוויטר?
  • facebook like image

    ערוץ הטלגרם של אינטרנט ישראל

    בערוץ הטלגרם של אינטרנט ישראל אני מפרסם את הפוסטים של באתר וכן עדכונים טכנולוגיים נוספים.
    מה זה טלגרם?
  • github logo

    הפרויקטים שלי בגיטהאב

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

כל הזכויות שמורות לרן בר-זיק ולאינטרנט ישראל | מדיניות הפרטיות של אתר אינטרנט ישראל | אתר אינטרנט ישראל נגיש לפי תקן WCAG 2.0 AA | הצהרת הנגישות של האתר | אבטחת מידע ודיווח על בעיית אבטחת מידע

גלילה לראש העמוד