Jak zmienić akcję referencyjną klucza obcego? (zachowanie)

102

Ustawiłem tabelę, która zawiera kolumnę z kluczem obcym, ustawioną na ON DELETE CASCADE(usuń dziecko, gdy rodzic zostanie usunięty)

Jakie byłoby polecenie SQL, aby to zmienić ON DELETE RESTRICT? (nie można usunąć rodzica, jeśli ma dzieci)

mysql foreign-keys foreign-key-relationship

— Moak
źródło

170

_{Stare pytanie, ale dodanie odpowiedzi, aby można było uzyskać pomoc}

Jego dwuetapowy proces:

Załóżmy, A table1ma klucz obcy z nazwy kolumny fk_table2_id, z więzów nazwę fk_namei table2nazywa tabeli z kluczem t2( coś jak poniżej w moim schemacie ).

   table1 [ fk_table2_id ] --> table2 [t2]

Pierwszy krok , DROP old CONSTRAINT: ( odniesienie )

ALTER TABLE `table1` 
DROP FOREIGN KEY `fk_name`;

_{uwaga ograniczenie zostało usunięte, kolumna nie została usunięta}

Drugi krok , DODAJ nowe OGRANICZENIE:

ALTER TABLE `table1`  
ADD CONSTRAINT `fk_name` 
    FOREIGN KEY (`fk_table2_id`) REFERENCES `table2` (`t2`) ON DELETE CASCADE;

_{dodając ograniczenie, kolumna już tam jest}

Przykład:

Mam UserDetailstabelę odnosi się do Userstabeli:

mysql> SHOW CREATE TABLE UserDetails;
:
:
 `User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`)
:
:

Pierwszy krok:

mysql> ALTER TABLE `UserDetails` DROP FOREIGN KEY `FK_User_id`;
Query OK, 1 row affected (0.07 sec)

Drugi krok:

mysql> ALTER TABLE `UserDetails` ADD CONSTRAINT `FK_User_id` 
    -> FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`) ON DELETE CASCADE;
Query OK, 1 row affected (0.02 sec)

wynik:

mysql> SHOW CREATE TABLE UserDetails;
:
:
`User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES 
                                       `Users` (`User_id`) ON DELETE CASCADE
:

— Grijesh Chauhan
źródło

2

Czy dodane ograniczenie nie powinno mieć wartości ON DELETE RESTRICT, zgodnie z żądaniem pierwotnego pytania?

— Noumenon

Ehm, co to jest „kaskada usuwania” i dlaczego jest to konieczne?

— Lealo

3

@Noumenon RESTRICT jest wartością domyślną, więc otrzymasz to, gdy nie określasz.

— edruid

1

@Lealo „przy usuwaniu kaskadowym” oznacza, że jeśli usuniesz wiersz z tabeli nadrzędnej (w tym przypadku Użytkownicy), wszystkie wiersze odwołujące się z tabeli podrzędnej (UserDetails) również zostaną usunięte.

— edruid

1

dziękuję za notatki "powiadomienie o ograniczeniu zostało usunięte, kolumna nie została usunięta", "dodanie ograniczenia, kolumna już tam jest", przypuszczam, że to oznacza, że dane są praktycznie zachowane i tam zmienia się tylko schemat

— George Birbilis

21

Możesz to zrobić w jednym zapytaniu, jeśli chcesz zmienić jego nazwę:

ALTER TABLE table_name
  DROP FOREIGN KEY `fk_name`,
  ADD CONSTRAINT `fk_name2` FOREIGN KEY (`remote_id`)
    REFERENCES `other_table` (`id`)
    ON DELETE CASCADE;

Jest to przydatne, aby zminimalizować przestoje, jeśli masz duży stół.

— Romuald Brunet
źródło

12

ALTER TABLE DROP FOREIGN KEY fk_name;
ALTER TABLE ADD FOREIGN KEY fk_name(fk_cols)
            REFERENCES tbl_name(pk_names) ON DELETE RESTRICT;

— pascal
źródło

2

pomogło mi znaleźć rozwiązanie ALTER TABLE table_name ADD...ON DELETE RESTRICT

— Moak

3

Nie, fk_name to nazwa ograniczenia. Podanie jednego jest opcjonalne. Nie jestem pewien, ale może możesz to odzyskać za pomocą SHOW CREATE TABLE.

— pascal

1

ON CASCADE RESTRICT prawdopodobnie nie jest przeznaczony.

— jgreep

5

Pamiętaj, że MySQL przechowuje prosty indeks w kolumnie po usunięciu klucza obcego. Jeśli więc chcesz zmienić kolumnę „referencje”, powinieneś to zrobić w 3 krokach

upuść oryginalny FK
upuść indeks (nazwy jak poprzednie fk, używając drop indexklauzuli)
utwórz nowy FK

— Wasilij
źródło

3

Możesz po prostu użyć jednego zapytania, aby rządzić nimi wszystkimi: ALTER TABLE products DROP FOREIGN KEY oldConstraintName, ADD FOREIGN KEY (product_id, category_id) REFERENCES externalTableName (foreign_key_name, another_one_makes_composite_key) ON DELETE CASCADE ON UPDATE CASCADE

— stamster
źródło

1

zadziała tylko wtedy, gdy zmienisz nazwę ograniczenia (jeśli używasz automatycznie wygenerowanej nazwy, prawdopodobnie zadziała, myślę, że MySQL zawsze tworzy unikalne)

— George Birbilis

Zapytanie działa na pewno w MySQL / MariaDB. Kluczem tutaj jest porzucenie starego ograniczenia przez jego nazwę, co jest wykonywane w linii 2.

— stamster

1

Składnia zapytania typu „wszystko w jednym” nie działała dla mnie z MySQL, gdy użyto jawnych nazw ograniczeń

— George Birbilis

3

Miałem do zmiany kilka FK, więc napisałem coś, co za mnie złożyło oświadczenie. Pomyślałem, że podzielę się:

SELECT

CONCAT('ALTER TABLE `' ,rc.TABLE_NAME,
    '` DROP FOREIGN KEY `' ,rc.CONSTRAINT_NAME,'`;')
, CONCAT('ALTER TABLE `' ,rc.TABLE_NAME,
    '` ADD CONSTRAINT `' ,rc.CONSTRAINT_NAME ,'` FOREIGN KEY (`',kcu.COLUMN_NAME,
    '`) REFERENCES `',kcu.REFERENCED_TABLE_NAME,'` (`',kcu.REFERENCED_COLUMN_NAME,'`) ON DELETE CASCADE;')

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
    AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE DELETE_RULE = 'NO ACTION'
AND rc.CONSTRAINT_SCHEMA = 'foo'

— DavidSM
źródło

1

to nie zadziała, jeśli ograniczenie dotyczy wielu kolumn. wygenerowany sql stworzy oddzielne ograniczenia dla każdej kolumny

— zapala się

Wszystkie moje FK były na pojedynczych kolumnach, więc nie myślałem zbytnio o tej możliwości, ale dobra myśl

— DavidSM