Błąd MySQL 1452 - Nie można dodać lub zaktualizować wiersza podrzędnego: ograniczenie klucza obcego kończy się niepowodzeniem

Mam trochę dziwny problem. Próbuję dodać klucz obcy do jednej tabeli, która odwołuje się do innej, ale z jakiegoś powodu nie działa. Przy mojej ograniczonej wiedzy o MySQL jedyną rzeczą, którą można podejrzewać, jest istnienie klucza obcego w innej tabeli odwołującej się do tej, o którą próbuję się powołać.

Zrobiłem SHOW CREATE TABLEzapytanie na obu tabelach, sourcecodes_tagsczy tabela z kluczem obcym sourcecodesjest tabelą , do której istnieje odniesienie.

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

To jest kod, który generuje błąd:

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

Całkiem prawdopodobne, że twoja sourcecodes_tagstabela zawiera sourcecode_idwartości, których już nie ma sourcecodes. Najpierw musisz się ich pozbyć.

Oto zapytanie, które może znaleźć te identyfikatory:

SELECT DISTINCT sourcecode_id FROM 
   sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id 
WHERE sc.id IS NULL;

Miałem ten sam problem z bazą danych MySQL, ale w końcu dostałem rozwiązanie, które działało dla mnie.
Ponieważ w mojej tabeli wszystko było w porządku z punktu widzenia mysql (obie tabele powinny używać silnika InnoDB, a typ danych każdej kolumny powinien być tego samego typu, który bierze udział w ograniczeniu klucza obcego).
Jedyne, co zrobiłem, to wyłączenie sprawdzania klucza obcego, a później włączenie go po wykonaniu operacji na kluczu obcym.
Kroki, które podjąłem:

SET foreign_key_checks = 0;

alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query
OK, 8 rows affected (0.23 sec) Records: 8  Duplicates: 0  Warnings: 0

SET foreign_key_checks = 1;

Użyj, NOT INaby dowiedzieć się, gdzie ograniczenia są ograniczone :

SELECT column FROM table WHERE column NOT IN 
(SELECT intended_foreign_key FROM another_table)

a dokładniej:

SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN 
(SELECT id FROM sourcecodes)

EDYCJA: INa NOT INoperatory są znacznie szybsze niż JOINoperatory, a także o wiele łatwiejsze do zbudowania i powtórzenia.

Obetnij tabele, a następnie spróbuj dodać ograniczenie FK .

Wiem, że to rozwiązanie jest trochę niezręczne, ale działa w 100%. Zgadzam się jednak, że nie jest to idealne rozwiązanie problemu, ale mam nadzieję, że to pomoże.

Dla mnie ten problem był nieco inny i bardzo łatwy do sprawdzenia i rozwiązania.

Musisz upewnić się, że OBA twoich stołów to InnoDB. Jeśli jedna z tabel, a mianowicie tabela referencyjna, to MyISAM, ograniczenie się nie powiedzie.

    SHOW TABLE STATUS WHERE Name =  't1';

    ALTER TABLE t1 ENGINE=InnoDB;

Dzieje się tak również podczas ustawiania klucza obcego na parent.id na child.column, jeśli child.column ma już wartość 0 i brak wartości parent.id wynosi 0

Musisz upewnić się, że każda kolumna child.column ma wartość NULL lub ma wartość istniejącą w pliku parent.id

A teraz, kiedy przeczytałem oświadczenie, które napisałem, to właśnie to potwierdza.

Miałem dzisiaj ten sam problem. Testowałem na cztery rzeczy, niektóre z nich już tu wspomniano:

1. Czy w kolumnie podrzędnej są jakieś wartości, które nie istnieją w kolumnie nadrzędnej (oprócz NULL, jeśli kolumna podrzędna ma wartość zerową)

2. Czy kolumny podrzędne i nadrzędne mają ten sam typ danych?

3. Czy istnieje indeks w kolumnie nadrzędnej, do której się odwołujesz? MySQL wydaje się tego wymagać ze względu na wydajność ( http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html )

4. A ten rozwiązał dla mnie: Czy obie tabele mają identyczne zestawienie?

Miałem jeden stół w UTF-8, a drugi w iso-coś. To nie zadziałało. Po zmianie tabeli izo-tabeli na sortowanie UTF-8 ograniczenia można dodawać bez problemów. W moim przypadku phpMyAdmin nawet nie pokazał tabeli potomnej w izo-kodowaniu w menu rozwijanym do utworzenia ograniczenia klucza obcego.

Wygląda na to, że w linii 0 kolumny jest nieprawidłowa wartość, która nie jest prawidłowym kluczem obcym, więc MySQL nie może ustawić dla niej ograniczenia klucza obcego.

Możesz wykonać następujące kroki:

1. Upuść kolumnę, dla której próbujesz ustawić ograniczenie FK.

2. Dodaj go ponownie i ustaw wartość domyślną na NULL.

3. Spróbuj ponownie ustawić dla niego ograniczenie klucza obcego.

Miałem ten sam problem, sprawdziłem wiersze moich tabel i stwierdziłem, że istnieje pewna niezgodność z wartością pól, które chciałem zdefiniować klucz obcy. Poprawiłem tę wartość, spróbowałem ponownie i problem został rozwiązany.

W końcu usuwam wszystkie dane z mojej tabeli i ponownie uruchamiam alter. To działa. Nie jest to genialne, ale oszczędza dużo czasu, zwłaszcza że twoja aplikacja jest wciąż w fazie rozwoju bez żadnych danych klientów.

Spróbuj tego

SET foreign_key_checks = 0;

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

SET foreign_key_checks = 1;

Miałem ten sam problem około trzy razy. W każdym przypadku było tak, ponieważ jeden (lub więcej) moich rekordów nie był zgodny z nowym kluczem obcym. Możesz spróbować zaktualizować swoje istniejące rekordy, aby przestrzegać ograniczeń składni klucza obcego przed próbą dodania samego klucza. Poniższy przykład powinien ogólnie izolować rekordy problemów:

SELECT * FROM (tablename)
    WHERE (candidate key) <> (proposed foreign key value) 
        AND (candidate key) <> (next proposed foreign key value)

powtórz AND (candidate key) <> (next proposed foreign key value)w zapytaniu dla każdej wartości w kluczu obcym.

Jeśli masz mnóstwo rekordów, może to być trudne, ale jeśli twój stół jest dość mały, nie powinno to zająć zbyt długo. Nie jestem super niesamowita w składni SQL, ale zawsze to dla mnie izolowało problem.

Opróżnij dane obu tabel i uruchom polecenie. To będzie działać.

Otrzymałem ten błąd, gdy korzystałem z Laravela i elokwentnego, próba utworzenia linku do klucza obcego spowodowałaby 1452. Problemem był brak danych w połączonej tabeli.

Zobacz tutaj przykład: http://mstd.eu/index.php/2016/12/02/laravel-eloquent-integrity-constraint-violation-1452-foreign-key-constraint/

Przygotowywałem te rozwiązania i ten przykład może pomóc.

Moja baza danych ma dwie tabele (e-mail i karta kredytowa) z kluczami podstawowymi dla ich identyfikatorów. Inna tabela (klient) określa te tabele jako klucze obce. Mam powód, aby mieć ten e-mail oprócz danych klienta.

Najpierw wstawiam dane wiersza dla tabel, do których istnieją odniesienia (e-mail, karta kredytowa), a następnie dostajesz identyfikator dla każdej, te identyfikatory są potrzebne w trzeciej tabeli (klient).

Jeśli nie wstawisz najpierw wierszy w tabelach, do których się odwołujesz, MySQL nie będzie w stanie wykonać korespondencji, gdy wstawisz nowy wiersz w trzeciej tabeli, która odwołuje się do kluczy obcych.

Jeśli najpierw wstawisz wiersze, do których istnieją odwołania, a następnie wiersz odnoszący się do kluczy obcych, nie wystąpi błąd.

Mam nadzieję że to pomoże.

Upewnij się, że wartość znajduje się w drugiej tabeli, w przeciwnym razie pojawi się ten błąd w przypisanej odpowiedniej kolumnie.

Jeśli więc jest przypisana, kolumna jest przypisana do identyfikatora wiersza innej tabeli, upewnij się, że w tabeli znajduje się wiersz, w przeciwnym razie pojawi się błąd.

możesz spróbować tego przykładu

 START TRANSACTION;
 SET foreign_key_checks = 0;
 ALTER TABLE `job_definers` ADD CONSTRAINT `job_cities_foreign` FOREIGN KEY 
 (`job_cities`) REFERENCES `drop_down_lists`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 SET foreign_key_checks = 1;
 COMMIT;

Uwaga: jeśli używasz phpmyadmin, odznacz opcję Włącz sprawdzanie klucza obcego

jako przykład

mam nadzieję, że to rozwiązanie naprawi problem :)

Musisz tylko odpowiedzieć na jedno pytanie:

Czy twoja tabela już przechowuje dane? (Zwłaszcza tabela zawiera klucz obcy.)

Jeśli odpowiedź brzmi „tak”, jedyne, co musisz zrobić, to usunąć wszystkie rekordy, a następnie możesz dodać dowolny klucz obcy do swojej tabeli.

Instrukcja usuwania: Z podrzędnego (które obejmuje tabelę kluczy obcych) do tabeli nadrzędnej.

Powód, dla którego nie można dodać klucza obcego po wprowadzeniu danych, wynika z niespójności tabeli. Jak sobie poradzisz z nowym kluczem obcym w poprzedniej tabeli wypełnionej danymi?

Jeśli odpowiedź brzmi „nie”, postępuj zgodnie z innymi instrukcjami.

UPDATE sourcecodes_tags
SET sourcecode_id = NULL
WHERE sourcecode_id NOT IN (
  SELECT id FROM sourcecodes);

powinno pomóc pozbyć się tych identyfikatorów. Lub jeśli nullnie jest dozwolone sourcecode_id, usuń te wiersze lub dodaj brakujące wartości do sourcecodestabeli.

Miałem ten sam problem i znalazłem rozwiązanie, umieszczając NULLzamiast NOT NULLna kolumnie klucza obcego. Oto zapytanie:

ALTER TABLE `db`.`table1`
ADD COLUMN `col_table2_fk` INT UNSIGNED NULL,
ADD INDEX `col_table2_fk_idx` (`col_table2_fk` ASC),
ADD CONSTRAINT `col_table2_fk1`
FOREIGN KEY (`col_table2_fk`)
REFERENCES `db`.`table2` (`table2_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

MySQL wykonał to zapytanie!

W moim przypadku utworzyłem nową tabelę o tej samej strukturze, utworzyłem relacje z innymi tabelami, następnie wyodrębniłem dane w CSV ze starej tabeli, która ma problem, a następnie zaimportowałem CSV do nowej tabeli i wyłączyłem sprawdzanie klucza obcego i wyłączono przerwanie importu, wszystkie moje dane są wstawiane do nowej tabeli, która nie ma problemu, a następnie usuwają starą tabelę.

To zadziałało dla mnie.