מפתחות זרים

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

• סקירה כללית על מפתחות זרים ב-Spanner
• סוגים של מפתחות זרים
• השוואה בין סוגים של מפתחות זרים
• בחירת סוג המפתח הזר שבו רוצים להשתמש
• שימוש במפתחות זרים נאכפים
• שימוש במפתחות זרים אינפורמטיביים
• גיבוי אינדקסים
• שינויים בסכימה שפועלים לאורך זמן

סקירה כללית של מפתחות זרים ב-Spanner

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

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

איור 1. תרשים של מסד נתונים לעיבוד הזמנות

אתם מגדירים טבלה Customers לאחסון נתוני לקוח, טבלה Orders למעקב אחרי כל ההזמנות וטבלה Products לאחסון מידע על כל מוצר.

באיור 1 מוצגים גם קישורים בין הטבלאות, שממפים את הקשרים הבאים בעולם האמיתי:

• לקוח מבצע הזמנה.

• מבצעים הזמנה של מוצר.

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

• אי אפשר ליצור הזמנה ללקוח שלא קיים.

• לקוחות לא יכולים להזמין מוצר שאתם לא מוכרים.

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

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

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

איור 2. תרשים של סכמת מסד נתונים עם מפתחות זרים

בכל טבלה מוצגים עכשיו שמות העמודות והסוגים שלהן. בOrders הטבלה מוגדרים גם שני קשרי גומלין של מפתח זר. הפרמטר FK_CustomerOrder מצפה שכל השורות ב-Orders יכללו ערך תקין של CustomerId. המפתח הזר FK_ProductOrder מצפה שכל הערכים של ProductId בטבלה Orders יהיו תקינים. בטבלה הבאה מפורטים האילוצים האלה בהתאמה לכללים בעולם האמיתי שרוצים לאכוף.

שם המפתח הזר	מגבלה	תיאור מהעולם האמיתי
FK_CustomerOrder	הפונקציה מצפה שכל השורות ב-Orders יכללו ערך תקין של CustomerId	לקוח תקין מבצע הזמנה
FK_ProductOrder	הפונקציה מצפה שכל השורות ב-Orders יכללו ערך תקין של ProductId	הוזמן מוצר תקין

מערכת Spanner אוכפת אילוצים שמוגדרים באמצעות מפתחות זרים שנאכפים. כלומר, Spanner לא יאשר אף טרנזקציה שמנסה להוסיף או לעדכן שורה בטבלה Orders עם ערך CustomerId או ProductId שלא נמצא בטבלאות Customers ו-Products. הוא גם מבטל עסקאות שמנסות לעדכן או למחוק שורות בטבלאות Customers ו-Products, שיגרמו לפסילת המזהים בטבלה Orders. פרטים נוספים על האופן שבו Spanner מאמת אילוצים מופיעים בקטע אימות אילוצים של טרנזקציות.

בניגוד למפתחות זרים עם אכיפה, Spanner לא מאמת אילוצים על מפתחות זרים אינפורמטיביים. המשמעות היא שאם משתמשים במפתח זר אינפורמטיבי בתרחיש הזה, טרנזקציה שמנסה להוסיף או לעדכן שורה בטבלה Orders עם CustomerId או ProductId שלא נמצאים בטבלאות Customers ו-Products לא עוברת אימות, והטרנזקציה לא נכשלת. בנוסף, שלא כמו מפתחות זרים שנאכפים, מפתחות זרים אינפורמטיביים נתמכים רק על ידי GoogleSQL ולא על ידי PostgreSQL.

מאפיינים של מפתח זר

בהמשך מפורטים מאפיינים של מפתחות זרים ב-Spanner.

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

• המפתח הזר מפנה לעמודות המופנות של הטבלה המופנית.

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

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

• עמודה שמפנה לעמודה אחרת והעמודה שאליה היא מפנה צריכות להיות מאותו סוג. צריכה להיות לכם אפשרות ליצור אינדקס לעמודות.

• אי אפשר ליצור מפתחות זרים בעמודות עם האפשרות allow_commit_timestamp=true.

• עמודות מסוג מערך אינן נתמכות.

• אין תמיכה בעמודות JSON.

• מפתח זר יכול להפנות לעמודות באותה טבלה (מפתח זר עם הפניה עצמית). דוגמה: טבלה Employee עם עמודה ManagerId שמפנה לעמודה EmployeeId של הטבלה.

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

• המפתחות שאליהם מתייחסים חייבים להיות ייחודיים. ‫Spanner משתמש ב-PRIMARY KEY של הטבלה המופנית אם העמודות המופנות של מפתח זר תואמות לעמודות המפתח הראשי של הטבלה המופנית. אם Spanner לא יכול להשתמש במפתח הראשי של הטבלה שאליה מתייחסים, הוא יוצר UNIQUE NULL_FILTERED INDEX בעמודות שאליהן מתייחסים.

• מפתחות זרים לא משתמשים באינדקסים משניים שיצרתם. במקום זאת, הם יוצרים אינדקסים משלהם. אפשר להשתמש באינדקסים תומכים בהערכות של שאילתות, כולל בהנחיות force_index מפורשות. אפשר להריץ שאילתות על השמות של האינדקסים התומכים מ-INFORMATION_SCHEMA של Spanner. מידע נוסף זמין במאמר בנושא גיבוי אינדקסים.

סוגים של מפתחות זרים

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

מפתחות זרים נאכפים

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

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

• מחיקת שורה מטבלה שהופנתה אליה שורה בטבלה המפנה נכשלת.

כל המפתחות הזרים של PostgreSQL נאכפים. כברירת מחדל, מתבצעת אכיפה של מפתחות זרים ב-GoogleSQL. מכיוון שמפתחות זרים נאכפים כברירת מחדל, השימוש במילת המפתח ENFORCED כדי לציין שמפתח זר ב-GoogleSQL נאכף הוא אופציונלי.

מפתחות זרים למידע

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

משתמשים במילת המפתח NOT ENFORCED כדי לציין שמפתח זר ב-GoogleSQL הוא לצורכי מידע. ‫PostgreSQL לא תומך במפתחות זרים אינפורמטיביים.

השוואה בין סוגים של מפתחות זרים

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

הבדלים ברמת מפתח זר

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

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

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

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

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

טבלת ההבדלים במפתחות זרים

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

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

בחירת סוג המפתח הזר שבו רוצים להשתמש

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

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

מומלץ להשתמש במפתחות זרים אינפורמטיביים אם כל התנאים הבאים מתקיימים:

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

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

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

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

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

שימוש במפתחות זרים למידע

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

• שינויים בסכימה שפועלים לאורך זמן
• גיבוי אינדקסים

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

אתם יוצרים ומסירים מפתחות זרים למידע ממסד הנתונים של Spanner באמצעות הצהרות DDL. מוסיפים מפתחות זרים לטבלה חדשה באמצעות ההצהרה CREATE TABLE. באופן דומה, אפשר להוסיף או להסיר מפתחות זרים מטבלה קיימת באמצעות ההצהרה ALTER TABLE.

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

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductId INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
   REFERENCES Customers (CustomerId) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL

Not Supported

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

הערה: אפשר גם להוסיף ולהסיר מפתחות זרים באמצעות מסוף Google Cloud . בוחרים באפשרות עריכה כטקסט בממשק ליצירת טבלאות ומזינים את ה-DDL כדי לנהל מפתחות זרים בטבלה.

שימוש במפתחות זרים אינפורמטיביים לאופטימיזציה של שאילתות

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

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

כברירת מחדל, אופטימיזציית השאילתות משתמשת בNOT ENFORCED מפתחות זרים. כדי לשנות את זה, צריך להגדיר את האפשרות use_unenforced_foreign_key_for_query_optimization במסד הנתונים לערך false. הדוגמה הבאה של GoogleSQL ממחישה את זה (מפתחות זרים אינפורמטיביים לא זמינים ב-PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

ההערה @{use_unenforced_foreign_key} של הצהרת השאילתה הבוליאנית מבטלת את האפשרות של מסד הנתונים על בסיס כל שאילתה, שקובעת אם האופטימיזציה משתמשת במפתחות זרים של NOT ENFORCED. השבתה של הרמז הזה או של אפשרות מסד הנתונים יכולה להיות שימושית כשמנסים לפתור בעיות שקשורות לתוצאות לא צפויות של שאילתות. בדוגמה הבאה מוסבר איך להשתמש ב-@{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

שימוש במפתחות זרים שנאכפים

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

• שינויים בסכימה שפועלים לאורך זמן
• גיבוי אינדקסים

יצירת טבלה חדשה עם מפתח זר מחייב

אפשר ליצור ולהסיר מפתחות זרים מונפקים ממסד הנתונים של Spanner באמצעות DDL. מוסיפים מפתחות זרים לטבלה חדשה באמצעות ההצהרה CREATE TABLE. באופן דומה, אפשר להוסיף מפתח זר לטבלה קיימת או להסיר ממנה מפתח זר באמצעות ההצהרה ALTER TABLE.

אתם יוצרים ומסירים מפתחות זרים ממסד הנתונים שלכם ב-Spanner באמצעות DDL. מוסיפים מפתחות זרים לטבלה חדשה באמצעות ההצהרה CREATE TABLE. באופן דומה, אפשר להוסיף מפתח זר לטבלה קיימת או להסיר ממנה מפתח זר באמצעות הצהרת ALTER TABLE.

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

GoogleSQL

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
OrderId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductId INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

CREATE TABLE Orders (
OrderId BIGINT NOT NULL,
CustomerId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId),
PRIMARY KEY (OrderId)
);

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

הערה: אפשר גם להוסיף ולהסיר מפתחות זרים באמצעות מסוף Google Cloud . בוחרים באפשרות עריכה כטקסט בממשק ליצירת טבלאות, ומזינים את ה-DDL כדי לנהל מפתחות זרים בטבלה.

פעולות של מפתח זר

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

פעולות של מפתח זר קובעות מה קורה לעמודה המוגבלת כשמוחקים או מעדכנים את העמודה שהיא מפנה אליה. ‫Spanner תומך בשימוש בפעולה ON DELETE CASCADE. בפעולה ON DELETE CASCADE action של מפתח זר, כשמוחקים שורה שמכילה מפתח זר שמפנה למפתח אחר, כל השורות שמפנות למפתח הזה נמחקות גם הן באותה טרנזקציה.

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

GoogleSQL

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

בהמשך מפורטת רשימה של מאפיינים של פעולות במפתח זר ב-Spanner.

• הפעולות של המפתח הזר הן ON DELETE CASCADE או ON DELETE NO ACTION.

• אפשר לשלוח שאילתה אל INFORMATION_SCHEMA כדי למצוא אילוצים של מפתח זר שיש להם פעולה.

• אין תמיכה בהוספה של פעולה של מפתח זר לאילוץ קיים של מפתח זר. צריך להוסיף אילוץ חדש של מפתח זר עם פעולה.

אימות מגבלות

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

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

ערך שמוכנס לעמודה המפנה מושווה לערכים של הטבלה המופנית והעמודות המופנות. שורות עם NULL שמפנות לערכים <0x0A>לא נבדקות, מה שאומר שאפשר להוסיף אותן לטבלת ההפניות.

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

האימות מתבצע מיד אחרי כל פקודת DML. לדוגמה, צריך להוסיף את השורה שאליה יש הפניה לפני שמוסיפים את השורות שמפנות אליה. כשמשתמשים ב-API של שינויים, השינויים נשמרים במאגר זמני עד שהעסקה מאושרת. אימות מאולץ של מפתח זר נדחה עד שהעסקה תאושר. במקרה כזה, מותר להוסיף קודם את השורות שמפנות לשורות אחרות.

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

פעולת מחיקה ממושכת של נתונים קשורים

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

חריגה ממגבלת השינויים (Mutation) למחיקה מדורגת של מפתח זר

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

השוואה בין אילוץ של מפתחות זרים לבין שילוב של טבלאות

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

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

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

איור 3. תרשים של סכימת מסד הנתונים עם מפתחות זרים שנאכפים

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

נניח שהלקוחות שלכם רוצים להזמין יותר ממוצר אחד בכל הזמנה. כדי לשפר את העיצוב, אפשר להוסיף OrderItems טבלה שכוללת רשומה לכל מוצר שהלקוח הזמין. אפשר להוסיף עוד מפתח זר מאולץ כדי לייצג את הקשר החדש של אחד לרבים בין Orders ל-OrderItems. עם זאת, אתם יודעים שאתם רוצים לעיתים קרובות להריץ שאילתות על הזמנות ועל פריטי ההזמנות הרלוונטיים. כדי לשפר את הביצועים, כדאי ליצור את יחסי האב-בן באמצעות היכולת של Spanner לשילוב טבלאות, כי שילוב כזה משפר את הביצועים.

כך מגדירים את הטבלה OrderItems, שמשולבת עם Orders.

GoogleSQL

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

CREATE TABLE OrderItems (
OrderId INT64 NOT NULL,
ProductId INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
) PRIMARY KEY (OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL

CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

איור 4 הוא ייצוג חזותי של סכימת מסד הנתונים המעודכנת כתוצאה מהוספת הטבלה החדשה הזו, OrderItems, שמשולבת עם Orders. כאן אפשר לראות גם את היחס של אחד לרבים בין שתי הטבלאות האלה.

איור 4. הוספה של טבלת OrderItems משולבת

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

אם מספר השינויים בעסקה חורג מ-80,000, העסקה נכשלת. מחיקות גדולות כאלה של נתונים שקשורים זה לזה מתאימות לטבלאות עם קשר מסוג interleaved in parent, אבל לא לטבלאות עם קשר של מפתח זר. אם יש לכם קשר של מפתח זר ואתם צריכים למחוק מספר גדול של שורות, אתם צריכים למחוק את השורות מטבלאות הצאצא קודם.

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

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

טבלת השוואה

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

סוג הקשר הורה-צאצא	שילוב טבלאות	מפתחות זרים נאכפים
אפשר להשתמש במקשים הראשיים	כן	כן
אפשר להשתמש בעמודות שאינן עמודות של מפתח ראשי	לא	כן
מספר ההורים הנתמכים	0 .. 1	‫0 .. N
אחסון נתונים של פריטים ראשיים ומשניים יחד	כן	לא
תמיכה במחיקה מדורגת	כן	כן
מצב התאמה מסוג Null	הבדיקה עוברת אם כל הערכים שמפנים לערכים אחרים לא שונים מהערכים שאליהם הם מפנים. ערכים מסוג Null לא שונים מערכים מסוג Null, אבל הם שונים מערכים שהם לא מסוג Null.	הבדיקה עוברת אם יש ערכים ריקים בהפניות. הבדיקה עוברת אם כל הערכים בהפניות לא ריקים, ולטבלה שאליה מתבצעת ההפניה יש שורה עם ערכים ששווים לערכים בהפניות. הבדיקה נכשלת אם לא נמצאה שורה תואמת.
תזמון האכיפה	לכל פעולה כשמשתמשים ב-API של מוטציות. לכל פקודה כשמשתמשים ב-DML.	לכל עסקה כשמשתמשים ב-Mutation API. לכל פקודה כשמשתמשים ב-DML.
ניתן להסרה	לא. אי אפשר להסיר שילוב של טבלאות אחרי שהוא נוצר, אלא אם מוחקים את כל טבלת הצאצא.	כן

אינדקסים לגיבוי

מפתחות זרים לא משתמשים באינדקסים שנוצרו על ידי המשתמש. במקום זאת, הם יוצרים מדדי גיבוי משלהם. מפתחות זרים מסוג Enforced ו-informational יוצרים אינדקסים תומכים ב-Spanner באופן שונה:

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

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

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

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

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

ב-Spanner נעשה שימוש בסכימת המידע של כל מסד נתונים כדי לאחסן מטא-נתונים על אינדקסים תומכים. שורות ב-INFORMATION_SCHEMA.INDEXES עם ערך SPANNER_IS_MANAGED של true מתארות אינדקסים תומכים.

בנוסף לשאילתות SQL שמפעילות ישירות את סכימת המידע,Google Cloud המסוף לא מציג מידע על אינדקסים של גיבוי מסד נתונים.

שינויים ממושכים בסכימה

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

בטבלה הבאה מוצג מה קורה ב-Spanner כשמגדירים מפתח זר חדש או קיים בטבלה חדשה או קיימת:

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

אין תמיכה בתכונות הבאות:

• הוספת פעולה של מפתח זר לאילוץ קיים של מפתח זר שנאכף.
• שינוי האכיפה של מפתח זר קיים.

בשני המקרים, אנחנו ממליצים לבצע את הפעולות הבאות:

1. מוסיפים אילוץ חדש עם הפעולה או האכיפה הנדרשות.
2. מבטלים את האילוץ הישן.

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

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

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

אפשר לשלוח שאילתה אל INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE כדי לבדוק את מצב היצירה של מפתח זר.

המאמרים הבאים

• מידע נוסף על יצירה וניהול של קשרי גומלין של מפתח זר

• מידע נוסף על סכימת המידע