Jak obciąć tabelę ograniczoną kluczem obcym?

Dlaczego nie robi TRUNCATE na mygrouppracę? Mimo że ON DELETE CASCADE SETdostaję:

BŁĄD 1701 (42000): Nie można obciąć tabeli, do której odwołuje się ograniczenie klucza obcego ( mytest. instance, ODNIESIENIA instance_ibfk_1KLUCZA ZAGRANICZNEGO ( GroupID) mytest. mygroup( ID))

drop database mytest;
create database mytest;
use mytest;

CREATE TABLE mygroup (
   ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE instance (
   ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   GroupID      INT NOT NULL,
   DateTime     DATETIME DEFAULT NULL,

   FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
   UNIQUE(GroupID)
) ENGINE=InnoDB;

Nie można TRUNCATEtabeli z zastosowanymi ograniczeniami FK ( TRUNCATEto nie to samo coDELETE ).

Aby obejść ten problem, użyj jednego z tych rozwiązań. Oba wiążą się z ryzykiem uszkodzenia integralności danych.

Opcja 1:

1. Usuń ograniczenia
2. Wykonać TRUNCATE
3. Usuń ręcznie wiersze, które mają teraz odniesienia do nikąd
4. Utwórz ograniczenia

Opcja 2: sugerowana przez użytkownika447951 w odpowiedzi

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

Tak, możesz:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

Dzięki tym instrukcjom ryzykujesz wpuszczeniem wierszy do tabel, które nie są zgodne z FOREIGN KEYograniczeniami.

Po prostu zrobiłbym to z:

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;

możesz to zrobić

DELETE FROM `mytable` WHERE `id` > 0

Zgodnie z dokumentacją mysql , TRUNCATE nie może być stosowany w tabelach z relacjami klucza obcego. Nie ma pełnej alternatywnej AFAIK.

Usunięcie ograniczenia nadal nie wywołuje opcji ON DELETE i ON UPDATE. Jedyne rozwiązanie, jakie mogę wymyślić ATM, to:

• usuń wszystkie wiersze, upuść klucze obce, obetnij, ponownie utwórz klucze
• usuń wszystkie wiersze, zresetuj auto_increment (jeśli jest używany)

Wydaje się, że TRUNCATE w MySQL nie jest jeszcze pełną funkcją (nie wywołuje też wyzwalaczy). Zobacz komentarz

Łatwe, jeśli używasz phpMyAdmin.

Po prostu odznacz Enable foreign key checksopcję w SQLzakładce i uruchomTRUNCATE <TABLE_NAME>

Podczas gdy zadawano to pytanie, nie wiedziałem o tym, ale teraz, jeśli używasz phpMyAdmin, możesz po prostu otworzyć bazę danych i wybrać tabele, które chcesz obciąć.

• Na dole znajduje się lista rozwijana z wieloma opcjami. Otwórz i wybierz Emptyopcję pod nagłówkiem Delete data or table.
• Nastąpi automatyczne przejście do następnej strony, na której znajduje się opcja w polu wyboru o nazwie Enable foreign key checks. Po prostu usuń zaznaczenie i naciśnij Yesprzycisk, a wybrane tabele zostaną obcięte.

Może wewnętrznie uruchamia zapytanie sugerowane w odpowiedzi user447951 , ale jest bardzo wygodne w użyciu z interfejsu phpMyAdmin.

Testowane na bazie danych MYSQL

Rozwiązanie 1:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;

Rozwiązanie 2:

DELETE FROM table1;
ALTER TABLE table1 AUTO_INCREMENT = 1;
TRUNCATE table1;

To działa dla mnie. Mam nadzieję, że ci to pomoże. Dzięki, że zadałeś to pytanie.

Odpowiedź jest rzeczywiście udzielona przez Zerkms , jak stwierdzono w Opcji 1 :

Opcja 1 : która nie stwarza ryzyka uszkodzenia integralności danych:

1. Usuń ograniczenia
2. Wykonaj TRUNCATE
3. Usuń ręcznie wiersze, które mają teraz odniesienia do nikąd
4. Utwórz ograniczenia

Trudna część polega na usuwaniu ograniczeń , więc chcę powiedzieć, jak to zrobić, na wypadek, gdyby ktoś musiał wiedzieć, jak to zrobić:

1. Uruchom SHOW CREATE TABLE <Table Name>zapytanie, aby zobaczyć, jak nazywa się KLUCZ ZAGRANICZNY (czerwona ramka na obrazku poniżej):

   

2. Uruchom ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>. Spowoduje to usunięcie ograniczenia klucza obcego.

3. Upuść powiązany Indeks (przez stronę struktury tabeli) i gotowe.

aby ponownie utworzyć klucze obce:

ALTER TABLE <Table Name>
ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);

Wystarczy użyć CASCADE

TRUNCATE "products" RESTART IDENTITY CASCADE;

Ale bądź gotowy na usuwanie kaskadowe)

Jeśli silnik bazy danych dla tabel różni się, pojawi się ten błąd, więc zmień je na InnoDB

ALTER TABLE my_table ENGINE = InnoDB;

Uzyskiwanie starego stanu sprawdzania klucza obcego i trybu sql to najlepszy sposób na obcinanie / upuszczanie tabeli, tak jak robią to Mysql Workbench podczas synchronizacji modelu z bazą danych.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;`
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP TABLE TABLE_NAME;
TRUNCATE TABLE_NAME;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;