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

תרגומי מאמרים תרגומי מאמרים
Article ID: 139444 - View products that this article applies to.
הרחב הכל | כווץ הכל

תקציר

טבלאות 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
				
פעולה זו תחזיר שורה אחת עבור כל ערכי מפתח ראשי כפולים בטבלה. העמודה האחרונה בתוצאה זו היא מספר הכפילויות של ערך מפתח ראשי מסוים.

כווץ את הטבלההרחב את הטבלה
col1col2
112


אם ישנן רק כמה קבוצות של ערכי מפתח ראשי כפולים, ההליך הטוב ביותר הוא למחוק אותם באופן ידני, בזה אחר זה. לדוגמה:
set rowcount 1
delete from t1
where col1=1 and col2=1
				
ערך ספירת השורה צריך להיות n-1 מספר הכפילויות של ערך מפתח נתון. בדוגמה זו, ישנן 2 כפילויות כך שספירת השורות מוגדרת ל- 1. הערכים col1/col2 values נלקחו מתוצאת השאילתה 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
    					

מאפיינים

Article ID: 139444 - Last Review: יום שישי 18 פברואר 2011 - Revision: 5.0
המידע במאמר זה חל על:
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
מילות מפתח 
kbsqlserverengine kbinfo kbusage KB139444

ספק משוב

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com