הבלוג של גרי רשף

10/09/2011

Violation of PRIMARY KEY constraint

Filed under: Uncategorized — גרי רשף @ 20:02

הודעת התקלה נ"ל מופיעה כשמנסים להכניס לטבלה רשומות שיוצרות כפילות במפתח.
כיצד מונעים זאת? ראשית חכמה התהליך צריך להיות בנוי כך שדברים כאלו לא יקרו, הרי לא מכניסים רשומות לטבלה ומקווים לטוב. אמורה להיות סיבה שתמנע תקלה כזו: אולי בכל יום מגיעים נתונים חדשים שנבדקו ובטבלה יש נתונים רק עד אתמול, אולי מתבצעת מראש מחיקה גורפת של נתונים מהטבלה או חלקית לפי הפילטר של אלו העומדים להיכנס, וכו'.
אני אעסוק כאן בטיפול בנקודת הכניסה, בעמדת הש"ג של הטבלה, וזה כמובן אינו מחליף את הטיפול המערכתי המתבקש.
למניעת הכנסת רשומות שיוצרות כפילות במפתח יש לטפל בשני מישורים:
1. מניעת הכנסת רשומות שהמפתח שלהן כבר קיים.
2. מניעת הכנסת רשומות בעלות מפתח זהה.
לצורך ההדגמה אצור שתי טבלאות עם נתונים- טבלת מכירות בה יש מפתח ראשי לפי עובד ותאריך,
וטבלת עדכון שמייצגת נתונים שיש להוסיף לטבלה (בפועל זה יכול להיות Select מורכב ממספר מקורות אך לשם הפשטות אסתפק בטבלה צנועה):

Use tempdb;
Go

If Object_Id('tempdb..T_Mehirot','U') Is Not Null Drop Table T_Mehirot;
Go

Create Table T_Mehirot(Oved Varchar(10) Not Null,
                       Taarih DateTime Not Null,
                       Camut Int);

Alter Table T_Mehirot Add Constraint PK_T_Mehirot Primary Key Clustered (Oved,Taarih)
Go

Insert
Into T_Mehirot
Select 'Tali','20110901',10 Union All
Select 'Tali','20110902',5 Union All
Select 'Tali','20110903',11 Union All
Select 'shaul','20110901',5;
Go

If Object_Id('tempdb..T_Idcun','U') Is Not Null Drop Table T_Idcun;
Go

Create Table T_Idcun(Oved Varchar(10),
                    Taarih DateTime,
                    Camut Int);
Go

Insert
Into   T_Idcun
Select 'Tali','20110904',9 Union All
Select 'Tali','20110904',9 Union All
Select 'Tali','20110905',12 Union All
Select 'Shaul','20110901',5 Union All
Select 'Shaul','20110902',7;
Go

Select  *
From    T_Mehirot
Order By Oved,
        Taarih;

Select  *
From    T_Idcun
Order By Oved,
        Taarih;

clip_image002

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

Insert
Into   T_Mehirot
Select *
From   T_Idcun;

clip_image004

הדרך הנכונה למנוע זאת היא כדלקמן:

Insert
Into    T_Mehirot
Select  Oved,
        Taarih,
        Max(Camut) Camut
From    T_Idcun I
Where   Not Exists (Select 1 From T_Mehirot M Where M.Oved=I.Oved And M.Taarih=I.Taarih)
Group By Oved,
        Taarih;

הפילטר Where מונע מרשומות בעלות מפתח קיים להיכנס, וה-Group By (לפי שדות המפתח) מאחד רשומות בעלות מפתח זהה.

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

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

להלן קוד לדוגמה ("הצעת הגשה" בלשון יצרני המזון):

Begin Try
Begin Tran
Insert
Into    T_Mehirot
Select  *
From    T_Idcun;
Commit;
End Try

Begin Catch
Declare @T_Mehirot Table(Oved Varchar(10), Taarih DateTime, Camut Int);
Insert Into @T_Mehirot Select * From T_Mehirot;
If Object_Id('tempdb..Tmp_Mehirot','U') Is Not Null Drop Table Tmp_Mehirot;
Declare @T_Idcun Table(Oved Varchar(10), Taarih DateTime, Camut Int);
Insert Into @T_Idcun Select * From T_Idcun;
Rollback;
If Object_Id('tempdb..Tmp_Mehirot','U') Is Not Null Drop Table Tmp_Mehirot;
Select * Into Tmp_Mehirot From @T_Mehirot;
If Object_Id('tempdb..Tmp_Idcun','U') Is Not Null Drop Table Tmp_Idcun;
Select * Into Tmp_Idcun From @T_Idcun;
RaisError ('An error has occured',16,1);
End Catch

clip_image006

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

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

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

מודעות פרסומת

4 תגובות »

  1. נראה לי שבמקום הבדיקה של
    Where Not Exists (Select 1 From T_Mehirot M Where M.Oved=I.Oved And M.Taarih=I.Taarih)
    עדיף לעשות LEFT JOIN על טבלת היעד, עם WHERE….IS NULL , משהו כזה:
    LEFT JOIN T_Mehirot M on M.Oved=I.Oved And M.Taarih=I.Taarih
    Where M.Oved IS NULL

    תגובה של איתי — 11/09/2011 @ 09:46

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

      תגובה של גרי רשף — 11/09/2011 @ 20:56

  2. כל נושא ה TRY – CATCH הוא בעייתי והמתכנת במקרה כזה לא מודע שהייתה שגיאה, אלא אם כן החזרתו לו פרמטר דרך ה RETURN לדוגמא או דרך OUTPUT PARAMETER.
    ה RAISERROR שאתה מעלה, איננו חוזר כשגיאה לקוד של המתכנת(העליתי לך את הנושא בעבר כשהצגת את נושא ה TRY CATCH ש DLL לפחות של VB6 לא מודע בכלל לכך שהיתה שגיאה מאחר ואין אפשרל לבצע מיפוי ל RAISERROR עם שגיאה מעל ל 50000)

    תגובה של pelegk1 — 10/09/2011 @ 20:17

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

      תגובה של גרי רשף — 10/09/2011 @ 20:38


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: