מדריך SQL – קשרים בין טבלאות, מפתח ראשי ומפתח זר

עד לשלב זה במדריך SQL, עבדנו על טבלה אחת בודד ששומרת את פרטי המוצרים בחנות:

מפתח ראשי של טבלה (Primary Key)

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

דוגמאות נוספות יכולות להיות:

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

הגדרת מפתח ראשי לטבלה בתוך WebMatrix

כדי להגדיר מפתח ראשי לטבלה, נבחר אותה ברשימת הטבלאות, ונעבור לתצוגת ה- Definition.

נבחר את השדה שנרצה שישמש כמפתח ראשי של הטבלה (במקרה שלנו – שדה ה- ProductID), ונשנה את המאפיין Is Primary Key להיות True.

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

לסיום הפעולה ושמירת השינוי נלחץ על כפתור ה- Save.

יצירת טבלה חדשה

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

נגדיר את הטבלה החדשה הבאה:

ראשית נלחת על כפתור ה- New Table בסרגל הכלים העליון, כדי ליצור טבלה חדשה:

נגדיר את העמודה OrderID – קוד ההזמנה, שתהיה המפתח הראשי של הטבלה.

העמודה ProductID תשמור את קוד הפריט להזמנה:

העמודה Units תשמור את מספר היחידות להזמנה מהפריט.

עד כאן, הגדרת העמודות בטבלה צריכה להיראות כך:

לסיום, נשמור את הטבלה החדשה בשם Orders.

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

• בהזמנה מספר 1 הוזמנו 3 יחידות מפריט עם קוד 1 (ביסלי), ובהזמנה מספר 2 הוזמנו 2 יחידות של אותו פריט.

• בהזמנה מספר 3 הוזמנו 7 יחידות מפריט עם קוד 7 (כיף כיף), ובהזמנה 7 הוזמנו 7 יחידות של אותו פריט.

שימו לב! בהזמנה 6 הוזמנו 3 יחידות מפריט שהקוד שלו הוא 5. מבט חטוף בטבלת הפריטים יגלה לנו שלא קיים שם פריט כזה. איך נוכל לוודא שטבלת ההזמנות תכיל הזמנות רק של פריטים שקיימים בחנות? לשם כך, נכיר את השימוש במפתח זר (Foreign Key).

הגדרת הקשר בין הטבלאות – שימוש במפתח זר (Foreign Key)

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

נגדיר קשר בין טבלת ההזמנות לטבלת המצרים שאומר את הדבר הבא:

• לכל רשומה חדשה בטבלת Orders, הערך שמוזן בעמודה ProductID חייב להיות קיים בטבלת Products.

הגדרה זו מגדירה מפתח זר – ערך בעמודה כלשהי (ProductID) בטבלה (Orders) צריך להופיע בטבלה אחרת (Products) שם הוא משמש כמפתח ראשי.

נפתח את הטבלה Orders בתצוגת Definition, ובסרגל הכלים העליון נלחץ על הכפתור New Relationship.

במסך שנפתח,בצד השמאלי (Foreign Key Table) מופיעה הטבלה Orders ורשימת השדות שלה, כאשר ליד השדה OrderID מופיע סימון שהוא מפתח ראשי.

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

נסמן בשתי הטבלאות את השדה ProductID על מנת להגדיר את הקשר הרצוי בין הטבלאות ונלחץ על OK.

כדי לבצע את השינויים נלחץ על כפתור השמירה בסרגל הכלים העליון של WebMatrix.

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

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

כדי לעשות זאת, נפתח את טבלת Orders בתצוגת Data.

כעת נסמן את הזמנה מספר 6 ונלחת על מקש ה- Delete. נאשר את הודעת השגיאה כדי לבצע את המחיקה.

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

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

בפרק הבא נכיר את פקודת ה- JOIN שמאפשרת לנו לשלוף נתונים ע”י שילוב בין מספר טבלאות.