Ograniczenie klucza obcego może powodować cykle lub wiele ścieżek kaskadowych?


176

Mam problem, kiedy próbuję dodać ograniczenia do moich tabel. Wyskakuje mi błąd:

Wprowadzenie ograniczenia klucza obcego „FK74988DB24B3C886” w tabeli „Pracownik” może spowodować cykle lub wiele ścieżek kaskadowych. Podaj opcję ON DELETE NO ACTION lub ON UPDATE NO ACTION lub zmodyfikuj inne ograniczenia klucza obcego.

Moje ograniczenie jest między Codestołem a employeestołem. CodeTabela zawiera Id, Name, FriendlyName, Typei Value. employeePosiada szereg pól, które kody referencyjne, tak że nie może być punktem odniesienia dla każdego typu kodu.

Potrzebuję, aby pola były ustawione na null, jeśli kod, do którego się odwołuje, zostanie usunięty.

Jakieś pomysły, jak mogę to zrobić?


Jedno z rozwiązań jest tutaj
IsmailS

Odpowiedzi:


180

SQL Server wykonuje proste zliczanie ścieżek kaskadowych i zamiast próbować ustalić, czy rzeczywiście istnieją jakiekolwiek cykle, zakłada najgorsze i odmawia tworzenia akcji referencyjnych (CASCADE): możesz i nadal możesz tworzyć ograniczenia bez akcji referencyjnych. Jeśli nie możesz zmienić projektu (lub zrobienie tego naraziłoby rzeczy na szwank), powinieneś rozważyć użycie wyzwalaczy jako ostateczności.

Rozwiązanie FWIW ścieżek kaskadowych jest złożonym problemem. Inne produkty SQL po prostu zignorują problem i pozwolą na tworzenie cykli, w którym to przypadku będzie to wyścig, który nadpisze wartość jako ostatnią, prawdopodobnie z powodu ignorancji projektanta (np. ACE / Jet to robi). Rozumiem, że niektóre produkty SQL będą próbowały rozwiązać proste przypadki. Fakt pozostaje faktem, SQL Server nawet nie próbuje, zachowuje się wyjątkowo bezpiecznie, blokując więcej niż jedną ścieżkę, a przynajmniej tak mówi.

Microsoft sam zaleca użycie wyzwalaczy zamiast ograniczeń FK.


2
Jedyną rzeczą, której wciąż nie mogę zrozumieć, jest to, że jeśli ten „problem” można rozwiązać za pomocą wyzwalacza, to dlaczego wyzwalacz nie „powoduje cykli lub wielu ścieżek kaskadowych…”?
armen

5
@armen: ponieważ wyzwalacz jawnie dostarczy logikę, której system nie mógł samodzielnie wykryć, np. jeśli istnieje wiele ścieżek do akcji referencyjnej usuwania, wówczas kod wyzwalacza określi, które tabele są usuwane iw jakiej kolejności.
kiedy

6
A także wyzwalacz jest wykonywany po zakończeniu pierwszej operacji, więc nie ma wyścigu.
Bon

2
@dumbledad: Mam na myśli, używaj wyzwalaczy tylko wtedy, gdy ograniczenia (być może w kombinacji) nie mogą wykonać zadania. Ograniczenia są deklaratywne, a za ich implementację odpowiada system. Wyzwalacze są kodem proceduralnym i musisz kodować (i debugować) implementację i znosić ich wady (gorsza wydajność itp.).
onedaywhen

1
Problem polega na tym, że wyzwalacz działa tylko tak długo, jak długo usuniesz ograniczenie klucza obcego, co oznacza, że ​​nie masz wtedy sprawdzania integralności referencji przy wstawianiu bazy danych, więc potrzebujesz jeszcze więcej wyzwalaczy, aby to obsłużyć. Rozwiązaniem wyzwalającym jest królicza nora prowadząca do zdegenerowanego projektu bazy danych.
Neutrino

99

Typowa sytuacja z wieloma ścieżkami kaskadowymi będzie wyglądać tak: tabela główna z dwoma szczegółami, powiedzmy „Główna” i „Szczegół1” i „Szczegół2”. Oba szczegóły są usuwane kaskadowo. Jak dotąd żadnych problemów. Ale co, jeśli oba szczegóły mają relację jeden do wielu z inną tabelą (powiedz „SomeOtherTable”). SomeOtherTable ma kolumnę Detail1ID i kolumnę Detail2ID.

Master { ID, masterfields }

Detail1 { ID, MasterID, detail1fields }

Detail2 { ID, MasterID, detail2fields }

SomeOtherTable {ID, Detail1ID, Detail2ID, someothertablefields }

Innymi słowy: niektóre rekordy w SomeOtherTable są połączone z rekordami Detail1, a niektóre rekordy w SomeOtherTable są połączone z rekordami Detail2. Nawet jeśli jest zagwarantowane, że rekordy SomeOtherTable nigdy nie należą do obu Details, niemożliwe jest teraz kaskadowe usuwanie rekordów SomeOhterTable dla obu szczegółów, ponieważ istnieje wiele ścieżek kaskadowych od Master do SomeOtherTable (jedna przez Detail1 i jedna przez Detail2). Być może już to zrozumiałeś. Oto możliwe rozwiązanie:

Master { ID, masterfields }

DetailMain { ID, MasterID }

Detail1 { DetailMainID, detail1fields }

Detail2 { DetailMainID, detail2fields }

SomeOtherTable {ID, DetailMainID, someothertablefields }

Wszystkie pola identyfikatora są polami kluczowymi i automatycznymi przyrostami. Sedno tkwi w polach DetailMainId tabel Detail. Pola te są zarówno kluczowym, jak i referencyjnym ograniczeniem. Teraz można kaskadowo usuwać wszystko, usuwając tylko rekordy główne. Wadą jest to, że dla każdego rekordu detail1 ORAZ dla każdego rekordu detail2 musi istnieć również rekord DetailMain (który w rzeczywistości jest tworzony jako pierwszy, aby uzyskać poprawny i unikalny identyfikator).


1
Twój komentarz bardzo pomógł mi zrozumieć problem, z którym się zmagam. Dziękuję Ci! Wolałbym wyłączyć usuwanie kaskadowe dla jednej ze ścieżek, a następnie obsłużyć usuwanie innych rekordów innymi sposobami (procedury składowane, wyzwalacze, kod itp.). Ale mam na uwadze twoje rozwiązanie (grupowanie w jedną ścieżkę) dla możliwych różnych zastosowań tego samego problemu ...
wolna

1
Jeden do użycia słowa crux (a także do wyjaśnienia)
masterwok

Czy to lepsze niż pisanie wyzwalaczy? Dodanie dodatkowej tabeli tylko po to, aby kaskada działała, wydaje się dziwne.
dumbledad

Wszystko jest lepsze niż pisanie wyzwalaczy. Ich logika jest nieprzejrzysta i nieefektywne w porównaniu z czymkolwiek innym. Dzielenie dużych tabel na mniejsze w celu uzyskania dokładniejszej kontroli jest naturalną konsekwencją lepiej znormalizowanej bazy danych, a nie czymś, czym należy się martwić.
Neutrino

12

Chciałbym zwrócić uwagę, że (funkcjonalnie) istnieje DUŻA różnica między cyklami i / lub wieloma ścieżkami w SCHEMACIE i DANYCH. Chociaż cykle i być może wielościeżki w DANYCH z pewnością mogą skomplikować przetwarzanie i powodować problemy z wydajnością (koszt „prawidłowej” obsługi), koszt tych charakterystyk w schemacie powinien być bliski zeru.

Ponieważ większość widocznych cykli w RDB występuje w strukturach hierarchicznych (schemat organizacyjny, część, podpunkt, itp.), To niefortunne jest, że SQL Server zakłada najgorsze; tj. cykl schematu == cykl danych. W rzeczywistości, jeśli używasz ograniczeń RI, nie możesz faktycznie zbudować cyklu w danych!

Podejrzewam, że problem wielościeżkowy jest podobny; tj. wiele ścieżek w schemacie niekoniecznie oznacza wiele ścieżek w danych, ale mam mniejsze doświadczenie z problemem wielościeżkowym.

Oczywiście jeśli SQL Server nie pozwalają cykle że to nadal podlegać głębokości 32, ale to chyba wystarczające dla większości przypadków. (Szkoda jednak, że to nie jest ustawienie bazy danych!)

Nie działają też reguły „Zamiast usuwania”. Przy drugiej wizycie w tabeli wyzwalacz jest ignorowany. Tak więc, jeśli naprawdę chcesz zasymulować kaskadę, będziesz musiał użyć procedur składowanych w obecności cykli. Wyzwalacz zamiast usuwania będzie działał jednak w przypadkach wielościeżkowych.

Celko sugeruje „lepszy” sposób przedstawiania hierarchii, który nie wprowadza cykli, ale są kompromisy.


„Jeśli używasz ograniczeń RI, nie możesz w rzeczywistości zbudować cyklu w danych!” -- Słuszna uwaga!
kiedy

Jasne, że możesz tworzyć cykliczne dane, ale z MSSQL tylko przy użyciu UPDATE. Inne RDBM obsługują odroczone ograniczenia (integralność zapewniona w momencie zatwierdzania, a nie w czasie wstawiania / aktualizacji / usuwania).
Carl Krig


3

Po jego dźwiękach masz akcję OnDelete / OnUpdate na jednym z istniejących kluczy obcych, która zmodyfikuje tabelę kodów.

Tworząc ten klucz obcy, tworzysz cykliczny problem,

Np. Aktualizacja pracowników powoduje zmianę kodów przez działanie przy aktualizacji, powoduje zmianę pracowników przez działanie przy aktualizacji ... itd.

Jeśli opublikujesz swoje definicje tabel dla obu tabel i definicje klucza obcego / ograniczeń, powinniśmy być w stanie powiedzieć Ci, gdzie jest problem ...


1
Są dość długie, więc nie sądzę, żebym mógł je tutaj wysłać, ale byłbym bardzo wdzięczny za twoją pomoc - nie wiem, czy jest jakiś sposób, abym mógł je do ciebie wysłać? Spróbuję to opisać: jedyne istniejące ograniczenia pochodzą z 3 tabel, z których wszystkie mają pola odwołujące się do kodów za pomocą prostego klucza INT ID. Wydaje się, że problem polega na tym, że pracownik ma kilka pól, które odwołują się do tabeli kodów i chcę, aby wszystkie były kaskadowane do SET NULL. Wszystko, czego potrzebuję, to to, że po usunięciu kodów odniesienia do nich powinny być wszędzie ustawione na zero.

opublikuj je mimo wszystko ... Nie sądzę, żeby ktoś tutaj miał coś przeciwko, a okno kodu sformatuje je poprawnie w przewijanym bloku :)
Eoin Campbell

2

Dzieje się tak, ponieważ Pracownik może mieć kolekcję innego podmiotu, która twierdzi, że kwalifikacje i kwalifikacje mogą mieć inną kolekcję Uniwersytety, np

public class Employee{
public virtual ICollection<Qualification> Qualifications {get;set;}

}

public class Qualification{

public Employee Employee {get;set;}

public virtual ICollection<University> Universities {get;set;}

}

public class University{

public Qualification Qualification {get;set;}

}

W DataContext mogłoby to wyglądać jak poniżej

protected override void OnModelCreating(DbModelBuilder modelBuilder){

modelBuilder.Entity<Qualification>().HasRequired(x=> x.Employee).WithMany(e => e.Qualifications);
modelBuilder.Entity<University>.HasRequired(x => x.Qualification).WithMany(e => e.Universities);

}

w tym przypadku istnieje łańcuch od pracownika do kwalifikacji i od kwalifikacji do uniwersytetów. Więc rzucał mi ten sam wyjątek.

U mnie zadziałało, kiedy się zmieniłem

    modelBuilder.Entity<Qualification>().**HasRequired**(x=> x.Employee).WithMany(e => e.Qualifications); 

Do

    modelBuilder.Entity<Qualification>().**HasOptional**(x=> x.Employee).WithMany(e => e.Qualifications);

1

Wyzwalacz jest rozwiązaniem tego problemu:

IF OBJECT_ID('dbo.fktest2', 'U') IS NOT NULL
    drop table fktest2
IF OBJECT_ID('dbo.fktest1', 'U') IS NOT NULL
    drop table fktest1
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'fkTest1Trigger' AND type = 'TR')
    DROP TRIGGER dbo.fkTest1Trigger
go
create table fktest1 (id int primary key, anQId int identity)
go  
    create table fktest2 (id1 int, id2 int, anQId int identity,
        FOREIGN KEY (id1) REFERENCES fktest1 (id)
            ON DELETE CASCADE
            ON UPDATE CASCADE/*,    
        FOREIGN KEY (id2) REFERENCES fktest1 (id) this causes compile error so we have to use triggers
            ON DELETE CASCADE
            ON UPDATE CASCADE*/ 
            )
go

CREATE TRIGGER fkTest1Trigger
ON fkTest1
AFTER INSERT, UPDATE, DELETE
AS
    if @@ROWCOUNT = 0
        return
    set nocount on

    -- This code is replacement for foreign key cascade (auto update of field in destination table when its referenced primary key in source table changes.
    -- Compiler complains only when you use multiple cascased. It throws this compile error:
    -- Rrigger Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, 
    -- or modify other FOREIGN KEY constraints.
    IF ((UPDATE (id) and exists(select 1 from fktest1 A join deleted B on B.anqid = A.anqid where B.id <> A.id)))
    begin       
        update fktest2 set id2 = i.id
            from deleted d
            join fktest2 on d.id = fktest2.id2
            join inserted i on i.anqid = d.anqid        
    end         
    if exists (select 1 from deleted)       
        DELETE one FROM fktest2 one LEFT JOIN fktest1 two ON two.id = one.id2 where two.id is null -- drop all from dest table which are not in source table
GO

insert into fktest1 (id) values (1)
insert into fktest1 (id) values (2)
insert into fktest1 (id) values (3)

insert into fktest2 (id1, id2) values (1,1)
insert into fktest2 (id1, id2) values (2,2)
insert into fktest2 (id1, id2) values (1,3)

select * from fktest1
select * from fktest2

update fktest1 set id=11 where id=1
update fktest1 set id=22 where id=2
update fktest1 set id=33 where id=3
delete from fktest1 where id > 22

select * from fktest1
select * from fktest2

0

To jest błąd typu zasad wyzwalacza bazy danych. Wyzwalacz jest kodem i może dodawać inteligencje lub warunki do relacji kaskadowej, na przykład kaskadowej delecji. Może zajść potrzeba wyspecjalizowania powiązanych opcji tabel w tym zakresie, takich jak Wyłączanie CascadeOnDelete :

protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
    modelBuilder.Entity<TableName>().HasMany(i => i.Member).WithRequired().WillCascadeOnDelete(false);
}

Lub całkowicie wyłącz tę funkcję:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

-2

Moje rozwiązanie tego problemu napotkane przy użyciu ASP.NET Core 2.0 i EF Core 2.0 polegało na wykonaniu następujących czynności w kolejności:

  1. Uruchom update-databasepolecenie w konsoli zarządzania pakietami (PMC), aby utworzyć bazę danych (powoduje to błąd „Wprowadzenie ograniczenia klucza obcego ... może powodować cykle lub wiele ścieżek kaskadowych”).

  2. Uruchom script-migration -Idempotentpolecenie w PMC, aby utworzyć skrypt, który można uruchomić niezależnie od istniejących tabel / ograniczeń

  3. Weź wynikowy skrypt, znajdź ON DELETE CASCADEi zamień naON DELETE NO ACTION

  4. Wykonaj zmodyfikowany kod SQL w bazie danych

Teraz Twoje migracje powinny być aktualne, a usuwanie kaskadowe nie powinno mieć miejsca.

Szkoda, że ​​nie mogłem znaleźć żadnego sposobu na zrobienie tego w Entity Framework Core 2.0.

Powodzenia!


Możesz zmienić plik migracji, aby to zrobić (bez zmiany skryptu sql), tj. W pliku migracji możesz ustawić akcję onDelete na Restrict from Cascade
Rushi Soni

Lepiej jest to określić za pomocą płynnych adnotacji, aby nie musieć o tym pamiętać, jeśli w końcu usuniesz i ponownie utworzysz folder migracji.
Allen Wang

Z mojego doświadczenia wynika, że ​​płynne adnotacje mogą być używane i powinny być używane (używam ich), ale często są dość błędne. Samo określenie ich w kodzie nie zawsze daje oczekiwany rezultat.
user1477388
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.