none
Cascade foreign key RRS feed

  • שאלה

  • היי

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

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

    בטבלת האנשים יש PK שהוא UID ובטבלת הקשרים יש שתי עמודות uid1 ו-uid2

    הגדרתי על שתי העמודות האלו FK כדי שלמור על הקשר.

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

    ניסיתי רעיון יותר פשוט, כאשר מוחקים רשומה פשוט להגדיר את הקשר כ-NULL ואז למחוק את כל הקשרים בהם יש NULL. לצערי, לא ניתן גם להגדיר On delet set null (פה כבר אין לי מושג מאיפה נוצר Cycle).

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

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

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

    יש דרך יעילה יותר לבצע את המחיקה הזו?

    תודה ושנה טובה!

    יום רביעי 04 ספטמבר 2013 07:26

תשובות

  • שבוע טוב

    הבעיה שלך ברורה ונובעת מתלות בין נתונים בטבלאות שונות.

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

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

    אישית אני לא ממליץ על מחיקה של רשומות בכלל. אני בטוח שמסד הנתונים שלך לא סובל מגודל של מאות טרה בייט ואם אין סיבה טובה מאוד למחוק רשומות אז הדרך המועדפת עלי היא להוסיף טור של סטאטוס בו פשוט משנים את הסטאטוס ל"מחוק" (למעשה עובדים עם טור בינארי ו 1 מסמן פעיל ו 0 מחוק). בזמן מחיקה רק משנים את הסטאטוס ולא מוחקים בפועל (כמובן האפליקציה צריכה לפנות בהתאם תוך דנון רק רשומות עם סטאטוס 1). אם עובדים בגרסת enterprise אז אפשר לחלק ל 2 מחיצות (מחוקים ולא מחוקים) ולשפר ביצועים.

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


    signature

    יום ראשון 08 ספטמבר 2013 16:34
    מנחה דיון
  • זה בדיוק מה שהצעתי לבבצע וזה בדיוק מה שמועדף בדרך כלל

    הסבר כללי:

    אם יש לנו פעולה שאפליקציה שצריכה למחוק 10 נתונים מטבלה מקושרת. הטבלה מקושרת ל 10K נתונים בנים  (ז"א כל ה10 אלף נתונים שלנו מקושרים ל 10 נתונים בטבלה האב). במחיקה בעזרת Cascade בדרך כלל תהיה מחיקה עבור כל נתון מה 10 בנפרד (אפשר לאתר זה בתוכנית ההרצה), לא תהיה לנו שליטה על מה קורה עם הנתונים המקושרים וכמה נמחקו (לא נוכל לאתר בצורה ישירה בתוכנית אלא רק אם תבדוק הודעות), לא נוכל לעלות על נתונים חסרים ובעיות ובקיצור הניהול לא שלנו אלא של מנוע המיטוב של השרת. וכל זה עוד כלום, יותר מכך במקרים קצוניים מאוד בהם הסטאטיסטיקה לא מעודכנת והשרת חושב שעבור כל נתון מה 10 הוא צריך למחוק רק נתון אחד מהטבלה המקושרת אז נקבל תוכנית הרצה איומה מאוד מאוד... מאוד לפעמים. כך למשל לא יהיה שימוש נכון באינדקסים, שימוש בנעילות יההי לא נכון וכו' (אפשר להעלות עוד אפשרויות תיאורטיות אבל מה שחשוב זה מה קורה אצלכם) ועוד הרבה בעיות.

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

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

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

    אישית אני לא ממליץ על מחיקה של רשומות בכלל. אני בטוח שמסד הנתונים שלך לא סובל מגודל של מאות טרה בייט ואם אין סיבה טובה מאוד למחוק רשומות אז הדרך המועדפת עלי היא להוסיף טור של סטאטוס בו פשוט משנים את הסטאטוס ל"מחוק" (למעשה עובדים עם טור בינארי ו 1 מסמן פעיל ו 0 מחוק). בזמן מחיקה רק משנים את הסטאטוס ולא מוחקים בפועל (כמובן האפליקציה צריכה לפנות בהתאם תוך דנון רק רשומות עם סטאטוס 1). אם עובדים בגרסת enterprise אז אפשר לחלק ל 2 מחיצות (מחוקים ולא מחוקים) ולשפר ביצועים.


    signature

    יום רביעי 18 ספטמבר 2013 15:57
    מנחה דיון
  • קדאפי,

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

    אתה טוען שזה פתרון "מכוער", אבל לטעמי זה הפתרון הנכון.

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום שני 09 ספטמבר 2013 11:33
    מנחה דיון

כל התגובות

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

    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    יום חמישי 05 ספטמבר 2013 05:11
  • זה הסקריפט שאני מנסה להריץ

    USE [testDB]
    create table users
    (
    	[user_id] int identity(1,1) primary key clustered,
    	[user_name] varchar(128) null
    )
    
    create table connections
    (
    	user_id1 int null,
    	user_id2 int null
    )
    
    ALTER TABLE [dbo].[connections]  WITH CHECK ADD CONSTRAINT [FK1_connections] FOREIGN KEY([user_id1]) 
    REFERENCES [dbo].[users] ([user_id]) on delete cascade
    
    ALTER TABLE [dbo].[connections]  WITH CHECK ADD CONSTRAINT [FK2_connections] FOREIGN KEY([user_id2]) 
    REFERENCES [dbo].[users] ([user_id]) on delete cascade

    וזו השגיאה שאני מקבל

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

    כמו שאמרתי אם אני משנה את ה-On delete cascade ל-On delete set null  מתקבלת שגיאה דומה.

    יום חמישי 05 ספטמבר 2013 09:33
  • שבוע טוב

    הבעיה שלך ברורה ונובעת מתלות בין נתונים בטבלאות שונות.

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

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

    אישית אני לא ממליץ על מחיקה של רשומות בכלל. אני בטוח שמסד הנתונים שלך לא סובל מגודל של מאות טרה בייט ואם אין סיבה טובה מאוד למחוק רשומות אז הדרך המועדפת עלי היא להוסיף טור של סטאטוס בו פשוט משנים את הסטאטוס ל"מחוק" (למעשה עובדים עם טור בינארי ו 1 מסמן פעיל ו 0 מחוק). בזמן מחיקה רק משנים את הסטאטוס ולא מוחקים בפועל (כמובן האפליקציה צריכה לפנות בהתאם תוך דנון רק רשומות עם סטאטוס 1). אם עובדים בגרסת enterprise אז אפשר לחלק ל 2 מחיצות (מחוקים ולא מחוקים) ולשפר ביצועים.

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


    signature

    יום ראשון 08 ספטמבר 2013 16:34
    מנחה דיון
  • קדאפי,

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

    אתה טוען שזה פתרון "מכוער", אבל לטעמי זה הפתרון הנכון.

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום שני 09 ספטמבר 2013 11:33
    מנחה דיון
  • קדאפי,

    מה המצב?

    למה שלא תעדכן אותנו לגבי הסטטוס של הדיון הזה?

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

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

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

    עדיין, לא הבנתי לגמרי איך הפתרון הזה יותר טוב מלבצע CASCADE.

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

    וגלנצר, תודה לך שלא חשפת את זהותי ;-)

    יום רביעי 18 ספטמבר 2013 14:46
  • האמת שגם אני לא יודע במה זה יותר יעיל, ומעולם לא בדקתי.

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

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


    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    יום רביעי 18 ספטמבר 2013 14:55
  • זה בדיוק מה שהצעתי לבבצע וזה בדיוק מה שמועדף בדרך כלל

    הסבר כללי:

    אם יש לנו פעולה שאפליקציה שצריכה למחוק 10 נתונים מטבלה מקושרת. הטבלה מקושרת ל 10K נתונים בנים  (ז"א כל ה10 אלף נתונים שלנו מקושרים ל 10 נתונים בטבלה האב). במחיקה בעזרת Cascade בדרך כלל תהיה מחיקה עבור כל נתון מה 10 בנפרד (אפשר לאתר זה בתוכנית ההרצה), לא תהיה לנו שליטה על מה קורה עם הנתונים המקושרים וכמה נמחקו (לא נוכל לאתר בצורה ישירה בתוכנית אלא רק אם תבדוק הודעות), לא נוכל לעלות על נתונים חסרים ובעיות ובקיצור הניהול לא שלנו אלא של מנוע המיטוב של השרת. וכל זה עוד כלום, יותר מכך במקרים קצוניים מאוד בהם הסטאטיסטיקה לא מעודכנת והשרת חושב שעבור כל נתון מה 10 הוא צריך למחוק רק נתון אחד מהטבלה המקושרת אז נקבל תוכנית הרצה איומה מאוד מאוד... מאוד לפעמים. כך למשל לא יהיה שימוש נכון באינדקסים, שימוש בנעילות יההי לא נכון וכו' (אפשר להעלות עוד אפשרויות תיאורטיות אבל מה שחשוב זה מה קורה אצלכם) ועוד הרבה בעיות.

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

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

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

    אישית אני לא ממליץ על מחיקה של רשומות בכלל. אני בטוח שמסד הנתונים שלך לא סובל מגודל של מאות טרה בייט ואם אין סיבה טובה מאוד למחוק רשומות אז הדרך המועדפת עלי היא להוסיף טור של סטאטוס בו פשוט משנים את הסטאטוס ל"מחוק" (למעשה עובדים עם טור בינארי ו 1 מסמן פעיל ו 0 מחוק). בזמן מחיקה רק משנים את הסטאטוס ולא מוחקים בפועל (כמובן האפליקציה צריכה לפנות בהתאם תוך דנון רק רשומות עם סטאטוס 1). אם עובדים בגרסת enterprise אז אפשר לחלק ל 2 מחיצות (מחוקים ולא מחוקים) ולשפר ביצועים.


    signature

    יום רביעי 18 ספטמבר 2013 15:57
    מנחה דיון
  • הי קדאפי,

    אם יש לך עוד שאלות פתוחות, שתף אותנו.

    אם לא, אנא סמן את התגובות הרלוונטיות כתשובות על מנת לסגור את השרשור.

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום ראשון 29 ספטמבר 2013 07:17
    מנחה דיון