none
בעיה לא מובנת RRS feed

  • שאלה

  • אהלן, אני מנסה לכתוב פעולת DELETE יחסית פשוטה (אני מתחיל בעניין הSQL) ואני נתקל שוב ושוב באותה בעיה:

    The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Orders". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'OrderID'.

    אשמח לעזרה בהבנת הבעיה ואיך פותרים אותה. תודה רבה!

    יום שלישי 02 יולי 2019 15:14

כל התגובות

  • ברוך הבא לפורום איתמר😃

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

    אני חושב שההודעה בעקרון מאוד ברורר לדוברי אנגלית :-)
    אני אנסה לתרגם ולהסביר

    החלק הראשון של ההודעה אומר שיש סתירה בין הבקשה שלך למחוק רשומה לבין REFERENCE constraint
    REFERENCE constraint הוא למשל מפתח חיצוני (foreign key)

    מפתחות במסד הנתונים הם סוג של constraint

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

    למה אני מנחש את זה? מכיוון שאם היית יוצר את המפתחות לבד אז היית יודע שמפתח או בעצם סוג של constraint וכניראה שהיית מבין את הודעת השגיאה :-)

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

    USE AdventureWorks2012;    
    GO    
    CREATE TABLE Sales.TempSalesReason (TempID int NOT NULL, Name nvarchar(50),     
    CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID),     
    CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)     
        REFERENCES Sales.SalesReason (SalesReasonID)     
        ON DELETE CASCADE
        ON UPDATE CASCADE
    );
    GO    

    שים לב שמפתח הראשי הוא PK_TempSales והמפתח החיצוני הוא FK_TempSales ושניהם זה למעשה הם סוג של CONSTRAINT

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

    נחזור לבעיה אצלך:

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

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

    לשמחתינו הודעת השגיאה גם מביאה לך מידע מלא כדי למצוא את המקום של הבעיה. המשך הודעת השגיאה מציג לנו היכן הבעיה

    The conflict occurred in database "Northwind", table "dbo.Order Details", column 'OrderID'.

    הסתירה קיימת במסד הנתונים Northwind בטבלה dbo.Order.
    המפתח הראשי הבעייתי הוא OrderID

    אז מה הפתרון:

    אתה צריך להחליט מה מתאים לך מפני שלא ניתן למחוק באופן פשוט שורה אם היא משמשת בטבלה אחרת

    הנה דוגמה מלאה עם הסברים (באגלית אבל פשוטה):

    USE tempdb
    GO
    
    CREATE TABLE dbo.Ari_Persons (
        PersonID int NOT NULL,
        FullName varchar(255) NOT NULL,
    	CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonID)
    );
    
    CREATE TABLE dbo.Ari_Orders (
        OrderID int NOT NULL PRIMARY KEY,
        PersonID int FOREIGN KEY REFERENCES Ari_Persons(PersonID),
    	-- I add the CONSTRAINT type FOREIGN KEY
    	-- This will make the relations between the tables rows
    	CONSTRAINT FK_Orders_Person 
    		FOREIGN KEY (PersonID) 
    		REFERENCES dbo.Ari_Persons (PersonID)
    );
    GO
    
    -- add elemnt to the PRIMARY table
    INSERT Ari_Persons(PersonID, FullName) 
    VALUES (1, 'Ronen')
    GO
    
    -- add row to the child table
    -- This new row is related to the external row in the other table (father table)
    INSERT Ari_Orders(OrderID, PersonID) 
    VALUES (11, 1) -- this row connected to a row in the Ari_Persons table!
    GO
    
    -- Now we cannot delete the row with "PersonID = 1" in the PRIMARY table
    -- Since it is used by the other table 
    DELETE Ari_Persons
    WHERE PersonID = 1
    GO
    -- ERROR! The DELETE statement conflicted with the REFERENCE constraint "FK__Ari_Order__Perso__38996AB5". The conflict occurred in database "tempdb", table "dbo.Ari_Orders", column 'PersonID'.
    
    
    /**************** Solution 1: 
    ** First delete the related row in the child table and free it
    ***************************************/
    DELETE Ari_Orders
    WHERE OrderID = 11 -- this row use the row with PersonID = 1 in the Ari_Persons table
    GO
    -- and now we can delete the father row as well
    -- Sicne this row is not in used
    DELETE Ari_Persons
    WHERE PersonID = 1
    GO
    
    /**************** Solution 2: 
    ** First change the related row in the child table so it will not use the row in the father table
    ***************************************/
    UPDATE Ari_Orders SET PersonID = null
    	where OrderID = 11
    GO
    -- and now we can delete the father row as well
    -- Sicne this row is not in used
    DELETE Ari_Persons
    WHERE PersonID = 1
    GO
    
    /**************** Solution 3: 
    ** use CASCADE when you ceate/alter the child table
    ** and configure the child table to automatically delete the father row
    ** when the child row is deleted
    ***************************************/
    -- For more information check the documentation:
    -- https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-table-constraint-transact-sql
    
    ------------ CLEAN THE TABLES - notice that we first drop the table with the FOREIGN KEY
    DROP TABLE IF EXISTS Ari_Orders
    DROP TABLE IF EXISTS Ari_Persons
    GO
    


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    יום שני 08 יולי 2019 03:25
    מנחה דיון