MySQL kļūda 1452, Cannot add or update a child row: a foreign key constraint fails, nozīmē, ka InnoDB atsakās saglabāt rindu, kuras ārējās atslēgas vērtībai nav atbilstošas rindas vecāka tabulā. Nekas nav salūzis: ierobežojums dara savu darbu. Noderīgais jautājums ir, kāpēc jūsu lietojumprogramma, imports vai migrācija mēģina ierakstīt atsauci uz kaut ko, kā nav.
Vispirms izlasiet visu ziņojumu
mysql> INSERT INTO orders (id, customer_id, total) VALUES (1001, 777, 49.90);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`)
REFERENCES `customers` (`id`))
Teksts pēc vispārīgās daļas nosauc bērna tabulu, ierobežojumu, bērna kolonnu un vecāka tabulu un kolonnu. Tas ir viss, kas vajadzīgs, lai sāktu. Kļūda ir ER_NO_REFERENCED_ROW_2, SQLSTATE 23000 (MySQL kļūdu saraksts). Tās māsa, kļūda 1451 Cannot delete or update a parent row, ir tas pats ierobežojums no otras puses: jūs dzēšat vai maināt vecāka rindu, kurai vēl ir bērni.
Ja redzat īsāko kļūdu 1216 bez ierobežojuma detaļām, lietotājam, kas izpilda komandu, nav tabulas līmeņa tiesību uz vecāku tabulām, tāpēc MySQL slēpj informāciju par vecāku. Veiciet diagnostiku ar lietotāju, kas redz abas tabulas.
Biežākie cēloņi
- Vecāka rindas tiešām nav. Drukas kļūda ID, agrāk dzēsts klients, testa dati, kas nekad neizveidoja vecāku, vai ID no citas vides.
- Nepareiza ievietošanas secība. Lietojumprogramma ievieto pasūtījumu pirms klienta, vai ETL darbs ielādē bērnu tabulas pirms vecāku tabulām.
- Ierobežojuma pievienošana netīriem datiem.
ALTER TABLE ... ADD FOREIGN KEYar ieslēgtām pārbaudēm pārbauda esošās rindas, un mūsu pieredzē pietiek ar vienu bāreni jebkur tabulā, lai viss ALTER neizdotos ar 1452. Tabulā, kas gadiem dzīvojusi bez ierobežojumiem, bāreņi ir vienmēr. - Daļēja atjaunošana un datu kopijas. Vienas tabulas atjaunošana no vakardienas rezerves kopijas datubāzē, kur vecāka tabula jau ir mainījusies, vai tabulu apakškopas kopēšana uz staging.
Diagnostika trīs vaicājumos
-- 1. which constraint, which columns (or read the error text)
SHOW CREATE TABLE orders\G
-- every foreign key that points somewhere, per schema
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'shop' AND REFERENCED_TABLE_SCHEMA IS NOT NULL;
-- 2. does the parent row exist?
SELECT id FROM customers WHERE id = 777;
-- 3. details of the most recent foreign key error
SHOW ENGINE INNODB STATUS\G -- section LATEST FOREIGN KEY ERROR
InnoDB gadījumā SHOW ENGINE INNODB STATUS sadaļa LATEST FOREIGN KEY ERROR parāda detaļas par pēdējo ārējās atslēgas kļūdu (ārējo atslēgu dokumentācija). Arī ierobežojumu problēmām, veidojot tabulas (kļūda 150), tā ir pirmā vieta, kur skatīties.
Ja cēlonis ir esošie dati, izveidojiet bāreņu sarakstu:
-- child rows whose parent is missing (orphans)
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.id IS NULL
LIMIT 100;
Pareizs risinājums
- Lietojumprogrammas kļūda: vispirms ievietojiet vecāku, tajā pašā transakcijā, kur bērnu, un pārbaudiet, ka izmantojat to vecāka ID, ko ģenerēja datubāze.
- Ielādes un imports: ielādējiet vecāku tabulas pirms bērnu tabulām. Ja secību nevar kontrolēt, izslēdziet pārbaudes tikai šai sesijai un pirms ielādi uzskatāt par pabeigtu, pārbaudiet ar bāreņu vaicājumu.
mysqldumptieši šī iemesla dēļ savā izvadē automātiski izslēdz ārējo atslēgu pārbaudes. - Bāreņi esošajos datos: lemiet katrā gadījumā atsevišķi. Izveidojiet trūkstošo vecāku, iestatiet atsauci uz
NULL, ja kolonna to atļauj, vai dzēsiet bāreni. Tas ir biznesa lēmums, tāpēc neļaujiet skriptam to pieņemt klusi. - Turpmākās dzēšanas: ja bērniem jāseko vecākam, definējiet
ON DELETE CASCADEvaiON DELETE SET NULLskaidri. NoklusējumsNO ACTIONInnoDB uzvedas kāRESTRICT.SET DEFAULTtiek parsēts, bet InnoDB noraida tabulu definīcijas, kas to izmanto.
ALTER TABLE (MySQL rokasgrāmata). Lietojiet to tikai sesijā, ko kontrolējat, un vienmēr pēc tam pārbaudiet bāreņus.Ārējo atslēgu prasības, kas izraisa saistītas kļūdas
- Vecāka un bērna kolonnām vajag līdzīgus tipus: veselo skaitļu un
DECIMALizmēram un zīmei jāsakrīt (INT UNSIGNEDnevar atsaukties uzINT), un nebināriem teksta tipiem jāsakrīt rakstzīmju kopai un kolācijai. Teksta garums var atšķirties. - Abām pusēm vajag indeksu. Bērna pusē MySQL to izveido automātiski, ja tā nav; vecāka pusē atsauces kolonnām jābūt indeksa pirmajām kolonnām. Atsauce uz neunikālu vai daļēju atslēgu 8.4 versijā ir novecojusi (deprecated).
- Ārējās atslēgas šeit ir InnoDB funkcija: abām tabulām jāizmanto viens un tas pats glabāšanas dzinējs, un pagaidu tabulas nevar piedalīties.
- Kaskādes darbības neizsauc trigerus. Ja jūsu audita žurnāls balstās uz
DELETEtrigeri bērna tabulā, kaskādes to apies.
Lielās tabulās: kas mainās
Testa datubāzē ar desmit tūkstošiem rindu viss iepriekš minētais aizņem sekundes. Produkcijas tabulā ar simtiem miljonu rindu katram solim vajag plānu.
- Bāreņu pārbaude ir liels JOIN. Vecāka primārā atslēga padara katru uzmeklēšanu lētu, bet vaicājums joprojām nolasa visu bērna tabulu. Ejiet cauri pa primārās atslēgas diapazoniem un palaidiet to uz replikas, lai tā nekonkurētu ar produkcijas slodzi un buferu kopu.
- Ierobežojuma pievienošana ar ieslēgtām pārbaudēm nozīmē pilnu tabulas kopiju. Ar ieslēgtu
foreign_key_checksārējās atslēgas pievienošanai tiek atbalstīts tikaiALGORITHM=COPY, kas pārbūvē tabulu un visu tās laiku bloķē vienlaicīgus ierakstus. Ar izslēgtām pārbaudēm tiek atbalstītsINPLACE: bez pārbūves, ar atļautu vienlaicīgu DML, tikai metadati (online DDL dokumentācija). Cena ir tā, ka esošās rindas netiek pārbaudītas, tāpēc vispirms sakārtojiet datus, tad pievienojiet ierobežojumu in place. Vairāk par ALTER stratēģiju izvēli: MySQL ALTER TABLE bez dīkstāves.
-- on a large table: walk the child primary key in ranges, ideally on a replica
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.id >= 0 AND o.id < 1000000
AND o.customer_id IS NOT NULL
AND c.id IS NULL;
-- next range: 1000000 .. 2000000, and so on
-- once the data is clean: add the constraint without a table copy
SET SESSION foreign_key_checks = 0;
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (id),
ALGORITHM=INPLACE, LOCK=NONE;
SET SESSION foreign_key_checks = 1;
- Ārējo atslēgu pārbaudes bloķē vecāka rindas. Katrs INSERT, UPDATE vai DELETE, kam jāpārbauda ierobežojums, uzliek koplietojamus ierakstu bloķējumus vecāka rindām, kuras tas aplūko, arī tad, ja pārbaude neizdodas (InnoDB bloķēšana). Ļoti karstām vecāka rindām (viens konts, kas saņem tūkstošiem bērnu ierakstu sekundē, vai pakešdarbs, kas atjaunina vecākus, kamēr tiek ievietoti bērni) tas izpaužas kā bloķējumu gaidīšana un strupceļi. Kā to atpazīt, skatiet MySQL deadlock izvades lasīšana.
- ON DELETE CASCADE lielam vecākam ir viena milzīga transakcija. Vecāka dzēšana ar diviem miljoniem bērnu tos visus dzēš vienā komandā, ar visu no tā izrietošo undo žurnālu, replikācijas aizturi un bloķējumu apjomu. Vispirms dzēsiet bērnus pa daļām, tad vecāku.
- Masveida ielādes. Pārbaužu izslēgšana sesijas līmenī lielas, sakārtotas ielādes laikā ir pamatota un ietaupa uzmeklēšanu katrai rindai. Pēc tam pārbaudiet ar diapazonu bāreņu vaicājumu, nevis ar cerību.
Galvenās atziņas
- Kļūda 1452 nozīmē, ka bērna rinda atsaucas uz vecāka vērtību, kas neeksistē; ziņojums nosauc precīzu ierobežojumu un kolonnas.
- Diagnosticējiet ar
SHOW CREATE TABLE, vecāka vērtības uzmeklēšanu unSHOW ENGINE INNODB STATUSsadaļu LATEST FOREIGN KEY ERROR. - Pirms ierobežojuma pievienošanas atrodiet esošos bāreņus ar
LEFT JOIN ... IS NULLvaicājumu. foreign_key_checksatkārtota ieslēgšana nepārbauda datus, kas ierakstīti, kamēr tas bija izslēgts.- Lielās tabulās sakārtojiet datus, tad pievienojiet ārējo atslēgu ar izslēgtām pārbaudēm un
ALGORITHM=INPLACE, lai izvairītos no pilnas tabulas kopijas. - Sekojiet bloķējumu gaidīšanai uz karstām vecāka rindām un sadaliet lielas kaskādes dzēšanas.