השימוש ב-Foreign Key

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

ניתן להגדיר על ה-Foreign Key גם Cascade של פעולות Update ו-Delete:
1. אם מעודכן המפתח בצד ה-1, הוא יתעדכן אוטומטית גם בצד ה-N (אחרת היינו צריכים לבטל את הרשומות בצד ה-N, לשנות בצד ה-1, וליצור מחדש בצד ה-N).
2. אם נמחקת שורה בצד ה-1, ימחקו כל השורות המקושרות אליה בצד ה-N.
בנוסף יש לשים לב שהעמודה שבצד ה-1 צריכה להיות Primary Key!

דוגמה פשוטה- ניצור שתי טבלאות המקושרות בינהן עם Foreign Key, ונוסיף לו Cascade גם של Update וגם של Delete:

Use tempdb;

Go

If Object_Id('T2','U') Is Not Null Drop Table T2;

Go

Create Table T2(ID_T1 Int,

                ID_T2 Int);

Go

If Object_Id('T1','U') Is Not Null Drop Table T1;

Go

Create Table T1(ID Int Primary Key);

Go

Alter Table T2

Add Constraint FK_T2_T1 Foreign Key(ID_T1)

References T1(ID) On Delete Cascade On Update Cascade;

Go

כעת נאכלס את הטבלאות בנתונים- קודם את T1 שבצד ה-1 ואחר כך את T2 שבצד ה-N (בשל אילוצי ה-Foreign Key), ונעיין בטבלאות:

Insert Into T1 Values(1);

Insert Into T1 Values(2);

Go

Insert Into T2 Values(1,1);

Insert Into T2 Values(1,2);

Insert Into T2 Values(2,1);

Insert Into T2 Values(2,2);

Insert Into T2 Values(2,3);

Go

Select * From T1;

Select * From T2;

נשנה כעת את קוד 1 שב-T1 ל-3,

ונוכל לראות שגם בטבלה T2 קוד 1 השתנה ל-3:

Update    T1

Set        ID=3

Where    ID=1;

באופן דומה- אם נמחק מטבלה T1 את השורה עם קוד 2,

ימחקו אוטומטית מ-T2 השורות המקושרות אליה:

Delete

From    T1

Where    ID=2;

נבחן כעת מקרה יותר מורכב: נוסיף טבלה T3,

נקשר אותה ל-T2 (באופן בו T2 מקושרת ל-T1),

נוסיף לשם כך עמודת Primary Key ל-T2,

ונאכלס באותה הזדמנות את שלוש הטבלאות בנתונים:

If Object_Id('T_3','U') Is Not Null Drop Table T_3;

Go

Create Table T_3(ID_T2 Int,

                ID_T3 Int);

Go

If Object_Id('T_2','U') Is Not Null Drop Table T_2;

Go

Create Table T_2(ID Int Primary Key,

                ID_T1 Int,

                ID_T2 Int);

Go

If Object_Id('T_1','U') Is Not Null Drop Table T_1;

Go

Create Table T_1(ID Int Primary Key);

Go

Alter Table T_2

Add Constraint FK_T_2_T_1 Foreign Key(ID_T1)

References T_1(ID) On Delete Cascade On Update Cascade;

Go

Alter Table T_3

Add Constraint FK_T_3_T_2 Foreign Key(ID_T2)

References T_2(ID) On Delete Cascade On Update Cascade;

Go

Insert Into T_1 Values(1);

Insert Into T_1 Values(2);

Go

Insert Into T_2 Values(1,1,1);

Insert Into T_2 Values(2,1,2);

Insert Into T_2 Values(3,2,1);

Insert Into T_2 Values(4,2,2);

Insert Into T_2 Values(5,2,3);

Go

Insert Into T_3 Values(1,1);

Insert Into T_3 Values(1,2);

Insert Into T_3 Values(2,1);

Insert Into T_3 Values(2,2);

Insert Into T_3 Values(2,3);

Insert Into T_3 Values(3,1);

Insert Into T_3 Values(5,2);

Insert Into T_3 Values(5,4);

Insert Into T_3 Values(5,6);

Go

Select * From T_1;

Select * From T_2;

Select * From T_3;

מה יקרה אם נמחק את השורה ב-T_1 שה-ID שלה הוא 2?

המערכת תמחק אותה מ-T_1,

את השורות המקושרות אליה ב-T_2,

ואת השורות המקושרות אליהן ב-T_3:

Delete

From    T_1

Where    ID=2;

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

נבנה טבלה כזו:

If Object_Id('T_Ovdim','U') Is Not Null Drop Table T_Ovdim;

Go

Create Table T_Ovdim(OvedID Int Primary Key,

                    Shem Varchar(50),

                    MenahelID Int);

Go

כעת ננסה ליצור Foreign Key עצמי עם Cacade כנ"ל:

Alter Table T_Ovdim

Add Constraint FK_Oved_Menahel Foreign Key(MenahelID )

References T_Ovdim(OvedID ) On Delete Cascade On Update Cascade;

Go

הפעם קיבלנו הודעת שגיאה:

Msg 1785, Level 16, State 0, Line 1

Introducing FOREIGN KEY constraint 'FK_Oved_Menahel' on table 'T_Ovdim' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

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

במקרה זה יש להסתפק ב-Foreign Key ללא Cascade:

Alter Table T_Ovdim

Add Constraint FK_Oved_Menahel Foreign Key(MenahelID )

References T_Ovdim(OvedID);

Go