יצירה וניהול של קשרי מפתח זר

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

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

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

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

בסכימה שמוצגת באיור 1 יש שלוש טבלאות:

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

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

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

• קשר של מפתח זר בין הטבלה Orders לבין הטבלה Products מבטיח שלא ניתן ליצור הזמנה למוצר שלא קיים.

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

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

הוספת מפתח זר לטבלה חדשה

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

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

בדוגמה הבאה אפשר לראות איך משתמשים בהצהרת CREATE TABLE DDL כדי ליצור את הטבלה Orders עם אילוץ של מפתח זר שמפנה אל CustomerID בטבלה Customers.

GoogleSQL

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)
) PRIMARY KEY (OrderID);

PostgreSQL

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)
);

ההצהרה הקודמת מכילה סעיף CONSTRAINT עם המאפיינים הבאים:

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

• האילוץ נקרא FK_CustomerOrder. השמות של האילוצים הם בתחום של הסכימה וחייבים להיות ייחודיים בתוך הסכימה.

• הטבלה Orders שבה מגדירים את האילוץ היא הטבלה המפנה. הטבלה Customers היא הטבלה שההפניה מתייחסת אליה.

• העמודה שמפנה בטבלת ההפניות היא CustomerID. הוא מפנה לשדה CustomerID בטבלה Customers. אם מישהו מנסה להוסיף שורה לטבלה Orders עם ערך CustomerID שלא קיים בטבלה Customers, הפעולה תיכשל.

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

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

GoogleSQL

CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);

הוספת מפתח זר לטבלה קיימת

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

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

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

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

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

הוספת מפתח זר עם פעולת מחיקה לטבלה חדשה

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

ההצהרה הבאה של CREATE TABLE DDL לטבלה Orders כוללת את אילוץ המפתח הזר שמפנה לטבלה Customers עם פעולה ON DELETE CASCADE.

GoogleSQL

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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE CASCADE,
PRIMARY KEY (OrderID)
);

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

הערה: אם לא מציינים פעולה של מפתח זר, פעולת ברירת המחדל היא NO ACTION. כלומר, אם מישהו מנסה למחוק שורה מהטבלה Customers, ויש שורות בטבלה Orders שמפנות לערך CustomerID שנמחק, פעולת המחיקה תיכשל עם שגיאה של הפרת אילוץ של מפתח זר.

הוספת מפתח זר עם פעולת מחיקה לטבלה

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

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

מחיקת שורה מהטבלה Products תגרום למחיקה של כל השורות בטבלה Orders שמפנות לערך ProductID שנמחק.

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

שימוש במפתחות זרים לצורכי מידע (GoogleSQL בלבד)

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

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

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

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,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

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

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

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

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

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

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

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

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

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

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

• מוסיפים שורה לטבלה Orders עם ערך CustomerID שלא קיים ב-Customers

  מה יקרה אם תנסו לבצע את השינוי הבא, בהינתן נתוני הדוגמה מהתרשים הקודם?

  INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);
  

  במקרה כזה, המערכת תנסה להוסיף שורה ל-Orders עם CustomerID (447) שלא קיים בטבלה Customers. אם המערכת עשתה זאת, יהיה לכם הזמנה לא תקינה במערכת. עם זאת, בגלל אילוץ המפתח הזר שהוספתם לטבלה Orders, הטבלה מוגנת. הפונקציה INSERT נכשלת עם ההודעה הבאה, בהנחה שהאילוץ נקרא FK_CustomerOrder.

  Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
  Cannot find referenced values in Customers(CustomerID).
  

  בניגוד למפתחות זרים שנאכפים, מפתחות זרים אינפורמטיביים לא אוכפים שלמות הפניה. אם FK_CustomerOrder הוא מפתח זר אינפורמטיבי, הצהרת ה-insert תצליח כי Spanner לא מאמת ש-FK_CustomerOrder התואם קיים בטבלה Customers.CustomerID לכן, יכול להיות שהנתונים לא יעמדו בדרישות של התקינות ההפניה שמוגדרות על ידי FK_CustomerOrder.

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

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

  DELETE FROM Customers WHERE CustomerID = 721;
  

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

  Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

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

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

הצגת המאפיינים של קשר בין מפתח זר

‫INFORMATION_SCHEMA של Spanner מכיל מידע על מפתחות זרים ועל האינדקסים שתומכים בהם. ריכזנו כאן כמה דוגמאות לשאלות שאפשר לענות עליהן באמצעות שאילתות ב-INFORMATION SCHEMA.

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

אילו אילוצים מוגדרים במסד הנתונים שלי?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

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

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

אילו אינדקסים הם אינדקסים משניים למפתחות זרים, שנקראים גם אינדקסים תומכים?

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

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';

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

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

האם מפתח זר נאכף או לא נאכף?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE, tc.ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

מידע נוסף זמין במאמר בנושא סכימת מידע.

הסרת קשר של מפתח זר

הוראת ה-DDL הבאה משמיטה אילוץ של מפתח זר מהטבלה Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

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

תמיכה ביחסי מפתח זר מורכבים יותר

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

עמודות מרובות

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

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

• לכל ערך SongName בטבלה TopHits צריך להיות ערך תואם בטבלה Songs.

• לכל זוג ערכים SingerFirstName ו-SingerLastName צריך להיות זוג ערכים תואם FirstName ו-LastName בטבלה Singers.

GoogleSQL

CREATE TABLE TopHits (
Rank INT64 NOT NULL,
SongName STRING(MAX),
SingerFirstName STRING(MAX),
SingerLastName STRING(MAX),

-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),

-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

PostgreSQL

CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,

-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),

-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName),

PRIMARY KEY (Rank)
);

הפניות מעגליות

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

1. יוצרים TableA, בלי מפתח זר.
2. יוצרים את TableB עם אילוץ של מפתח זר ב-TableA.
3. משתמשים ב-ALTER TABLE ב-TableA כדי ליצור הפניה למפתח זר אל TableB.

טבלאות שמתייחסות לעצמן

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

GoogleSQL

CREATE TABLE Employees (
EmployeeId INT64 NOT NULL,
EmployeeName STRING(MAX) NOT NULL,
ManagerId INT64,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

PostgreSQL

CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);

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

• מידע נוסף על תמיכה במפתחות זרים ב-Spanner

• מידע נוסף על INFORMATION SCHEMA ב-Spanner