כיצד להסיר שורות כפולות מטבלה ב- SQL Server

עבוד בכל מקום מכל מכשיר באמצעות Microsoft 365

שדרג ל-Microsoft 365 כדי לעבוד בכל מקום באמצעות התכונות והעדכונים האחרונים.

שדרג עכשיו

סיכום

טבלאות Microsoft SQL Server לעולם אינן יכולות להכיל שורות כפולות, או מפתחות ראשיים שאינם ייחודיים. לשם קיצור, לעתים נתייחס במאמר זה אל מפתחות ראשיים כאל "מפתח", אך הכוונה תמיד ל"מפתח ראשי". מפתחות ראשיים כפולים מהווים הפרה של תקינות ישויות, ואין לאפשר את קיומה במערכת יחסית. SQL Server כולל מנגנונים שונים לאכיפת תקינות ישויות, כולל אינדקסים, אילוצי UNIQUE, אילוצי PRIMARY KEY וגורמים מפעילים.

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

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

Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key. (יצירת אינדקס ייחודי בוטלה במפתח כפול)

אם אתה משתמש ב- SQL Server 2000 או ב- SQL Server 2005, ייתכן שתופיע הודעת השגיאה הבאה:

Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name '%.*ls' and index name '%.*ls'. The duplicate key value is %ls. (יצירת אינדקס ייחודי הסתיימה מאחר שנמצא מפתח כפול עבור שם האובייקט '%.*ls' ועבור שם האינדקס '%.*ls'. ערך המפתח הכפול הוא %ls.

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

מידע נוסף

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

create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')

השלב הראשון הוא לזהות אילו שורות כוללות ערכים כפולים של מפתח ראשי:

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

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

col1

col2

1

1

2



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

set rowcount 1
delete from t1
where col1=1 and col2=1

ערך ספירת השורה צריך להיות n-1 מספר הכפילויות של ערך מפתח נתון. בדוגמה זו, יש 2 כפילויות כך שספירת ה-rowcount מוגדרת ל-1. ערכי עמודה 1/col2 נלקחים מתוצאת השאילתה שלעיל GROUP BY. אם השאילתה GROUP BY מחזירה שורות מרובות, השאילתה "set rowcount" תצטרך לפעול פעם אחת עבור כל אחת משורות אלה. בכל פעם שהשאילתה פועלת, יש להגדיר את ספירת השורה ל- n-1 מספר הכפילויות של ערך המפתח הראשי המסוים.

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

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

  1. תחילה, הפעל את השאילתה GROUP BY לעיל כדי לקבוע כמה קבוצות של ערכי מפתח ראשי כפולים קיימים וספור את הכפילויות בכל קבוצה.

  2. בחר את ערכי המפתח הראשי הכפולים לתוך טבלה מכילה. לדוגמה:

    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
  3. בחר את השורות הכפולות לתוך הטבלה המכילה. הקפד למנוע כפילויות בתהליך זה. לדוגמה:

    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
  4. בשלב זה, הטבלה holddups אמורה לכלול מפתחות ראשיים ייחודיים, עם זאת, זה לא המצב אם t1 כלל מפתחות ראשיים כפולים אך שורות ייחודיות (כמו בדוגמת מס' ת. הזהות לעיל). ודא שכל מפתח בטבלה holddups הוא ייחודי, וכי לא מתקיים מצב של מפתחות כפולים אך שורות ייחודיות. אם זה המצב, עליך לעצור כאן ולהחליט אילו שורות ברצונך להשאיר עבור ערך מפתח ראשי כפול נתון. לדוגמה, השאילתה:

    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2

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

  5. מחק את השורות הכפולות מהטבלה המקורית. לדוגמה:

    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
  6. החזר את השורות הייחודיות לטבלה המקורית. לדוגמה:

    INSERT t1 SELECT * FROM holddups

זקוק לעזרה נוספת?

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Microsoft insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×