מפתח זר (foreign key) ב-SQL

במדריכים קודמים, עבדנו עם טבלה (workers) שכללה את שמות העובדים בחברה ופרטים נוספים. להורדת הטבלה:

להורדה

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

הפתרון, כפי שמוסבר בסרטון, מורכב משני שלבים:

• הוספת טבלה חדשה (cars), שתכלול את פרטי הרכבים.
• הוספת עמודה מפתח זר לטבלה הקיימת (workers), שתצביע על המזהה הייחודי (id) של הרכב בטבלת הרכבים (cars).

העמודה שתצביע על המזהה הייחודי (id) מורכבת מ מפתחות זרים (foreign key), שהם ערכים מספריים שמצביעים על המזהים הייחודיים (id) בטבלה האחרת.

נוסיף טבלה ששמה cars כדי שנוכל להצביע על ה-id שלה מהטבלה שלנו:

CREATE TABLE IF NOT EXISTS `cars` 
(
  `cars_id` int(6) NOT NULL AUTO_INCREMENT,
  `cars_model` varchar(20) DEFAULT NULL,
  `license_renewal` date DEFAULT NULL,
PRIMARY KEY (`cars_id`)
);

נזין ערכים לטבלת cars:

INSERT INTO `cars`(`cars_model`, `license_renewal`)
VALUES
('Talbot', '2013-10-01'),
('Sussita', '2014-01-01');

נוסיף עמודה חדשה של מפתחות זרים לטבלה המקורית. שמה של העמודה refer_cars_id, וחשוב מאוד שהיא תתאם בדיוק את התכונות של העמודה שהיא מצביעה עליה (סוג נתונים, קידוד וכיו"ב), ושברירת המחדל תהיה NULL.

ALTER TABLE workers
ADD COLUMN refer_cars_id int(6)
DEFAULT NULL;

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

ALTER TABLE workers
ADD FOREIGN KEY (refer_cars_id)
REFERENCES cars(cars_id)

עכשיו נזין את הערכים לעמודת refer_cars_id בטבלת workers.

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

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

ALTER TABLE workers
ADD FOREIGN KEY (refer_cars_id)
REFERENCES cars(cars_id)
ON DELETE CASCADE
ON UPDATE CASCADE

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

ALTER TABLE `workers`
MODIFY `refer_cars_id`
INT(11) UNSIGNED NOT NULL

ALTER TABLE `cars`
MODIFY `cars_id`
INT(11) UNSIGNED NOT NULL AUTO_INCREMENT

במדריך הבא נלמד על צירוף טבלאות SQL באמצעות JOIN

לכל מדריכי ה- mySQL

אהבתם? לא אהבתם? דרגו!

0 הצבעות, ממוצע 0 מתוך 5 כוכבים