מפתח זר קושר עמודה למפתח הראשי, או לעמודה ייחודית, של טבלה אחרת. PostgreSQL דוחה כל ערך שאין לו שורה תואמת שם. כותבים אותו כ-column type REFERENCES other_table (column). הוסיפו ON DELETE CASCADE או ON DELETE SET NULL כדי לקבוע מה קורה לשורות הבן כשההורה נמחק.
לעמודה שמפנים אליה חייב להיות אילוץ PRIMARY KEY או UNIQUE. עם REFERENCES customers בלי עמודה, PostgreSQL משתמש במפתח הראשי של customers.
איך נראית הפרה
הזמנה ללקוח שלא קיים נדחית:
השגיאה מציינת את האילוץ, orders_customer_id_fkey, שזה השם ש-PostgreSQL נותן למפתח זר בלי שם. מחיקה של לקוח שעדיין יש לו הזמנות נכשלת באותה צורה, אלא אם המפתח קובע אחרת.
אפשרויות ON DELETE
| אפשרות | כששורת ההורה נמחקת |
|---|---|
NO ACTION (ברירת מחדל) | המחיקה נכשלת כל עוד יש שורות בן |
RESTRICT | אותו דבר, אבל אי אפשר לדחות את הבדיקה לסוף הטרנזקציה |
CASCADE | גם שורות הבן נמחקות |
SET NULL | עמודת המפתח הזר של הבן הופכת ל-NULL |
SET DEFAULT | העמודה של הבן מקבלת את ערך ברירת המחדל שלה |
ON UPDATE מקבל את אותן אפשרויות. הוא חל כשערך המפתח של ההורה משתנה, וזה נדיר עם ids מיוצרים.
CASCADE מתאים לשורות שאין להן משמעות בלי ההורה, כמו פריטי השורה של הזמנה:
נשאר רק chair. מחיקות מדורגות ממשיכות במפתחות נוספים, כך שמחיקה אחת יכולה להסיר שורות בכמה טבלאות.
SET NULL שומר את שורת הבן ומנתק אותה:
Ada נשארת, עם team_id שהפך ל-NULL. מאז PostgreSQL 15, ON DELETE SET NULL (column) יכול לנקות רק חלק מהעמודות של מפתח מרובה עמודות.
להוסיף מפתח זר לטבלה קיימת
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
הפקודה בודקת כל שורה קיימת ונכשלת אם לאחת מהן אין הורה. בטבלה גדולה, הוסיפו את המפתח עם NOT VALID, שבודק רק שורות חדשות. אחר כך הריצו ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; כדי לבדוק את השורות הישנות בלי לחסום כתיבות. כדי לשנות את פעולת ה-ON DELETE של מפתח קיים, מחקו את האילוץ והוסיפו אותו מחדש ב-ALTER TABLE אחד.
אינדקס על עמודת המפתח הזר
PostgreSQL יוצר אינדקס בצד שמפנים אליו, לא על העמודה המפנה. בלי אינדקס על orders.customer_id, כל מחיקה מ-customers סורקת את כל orders כדי לחפש בנים, וכך גם שליפת ההזמנות של לקוח אחד:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
כדי לראות את רשימת המפתחות הזרים של טבלה, שאלו את pg_constraint:
צירופים לאורך המפתחות האלה מוסברים בדף על צירופים, ופרק האילוצים במדריך מפרט את כל הכללים, כולל מפתחות שאפשר לדחות (deferrable).
שאלות נפוצות
האם PostgreSQL יוצר אינדקס למפתח זר?
לא על העמודה המפנה. לעמודה שמפנים אליה כבר יש אינדקס, כי היא חייבת להיות מפתח ראשי או ייחודית. צרו בעצמכם אינדקס על עמודת המפתח הזר, כי צירופים ומחיקות מטבלת ההורה מחפשים בה.
איך מוחקים מפתח זר ב-PostgreSQL?
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. מפתחות זרים בלי שם נקראים table_column_fkey; \d orders ב-psql מציג את השם המדויק.
האם עמודת מפתח זר יכולה להיות NULL ב-PostgreSQL?
כן. NULL בעמודת המפתח הזר לא נבדק, כך שהשורה לא צריכה הורה. הוסיפו NOT NULL כשלכל שורה חייב להיות הורה.
האם מפתח זר יכול להפנות לאותה טבלה?
כן. עמודה כמו manager_id int REFERENCES employees (id) מצביעה על שורה אחרת באותה טבלה. כך שומרים עצים כמו מבנה ארגוני ושרשורי תגובות.