"Cannot add foreign key constraint" nozīmē, ka MySQL nevar savienot abas tabulas tā, kā ārējā atslēga ir uzrakstīta. Klasiskā forma ir ERROR 1215 (HY000) vai errno: 150 kļūdā 1005, veidojot tabulu. Jaunākas versijas ir konkrētākas un nosauc problēmu ar jaunāku kļūdas kodu. Cēlonis gandrīz vienmēr ir nesakritība starp bērna kolonnu un vecāka atslēgu.
Vispirms uzziniet īsto iemeslu
Vecie ziņojumi neko noderīgu nepasaka. InnoDB glabā pēdējās kļūmes detaļas savā statusa izvadē (FOREIGN KEY ierobežojumi):
SHOW ENGINE INNODB STATUS\G
-- look for the section:
-- ------------------------
-- LATEST FOREIGN KEY ERROR
-- ------------------------
MySQL 8.0 un 8.4 bieži uzreiz dod kādu no šīm konkrētākajām kļūdām (servera kļūdu saraksts):
3780: atsaucošā un atsauktā kolonna nav saderīgas;1822: atsauktajā tabulā trūkst indeksa ierobežojumam;6125: atsauktajā tabulā trūkst unikālas atslēgas (MySQL 8.4, skatīt tālāk);1824: neizdevās atvērt atsaukto tabulu, parasti tāpēc, ka tās nav vai nosaukums uzrakstīts citādi;3734: atsauktajā tabulā trūkst kolonnas;1830: kolonna nevar būt NOT NULL, jo ierobežojums izmantoSET NULL;1826: dublēts ārējās atslēgas ierobežojuma nosaukums.
1. Kolonnu tipi nesakrīt
-- parent
CREATE TABLE customers (id INT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=InnoDB;
-- child: INT (signed) vs INT UNSIGNED
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB;
-- ERROR 3780 (HY000): Referencing column 'customer_id' and referenced
-- column 'id' in foreign key constraint 'orders_ibfk_1' are incompatible.
Dokumentācija šeit ir stingra: veselo skaitļu un DECIMAL tipu izmēram un zīmei jābūt vienādiem (FOREIGN KEY ierobežojumi). INT un INT UNSIGNED nesakrīt. Tāpat INT un BIGINT. Tas iekož pēc migrācijas, kas vecāka primāro atslēgu paplašina līdz BIGINT, bet bērna kolonnas atstāj kā INT, vai kad ORM ID pēc noklusējuma veido bez zīmes, bet ar roku rakstīta tabula ne.
Salīdziniet abas puses tieši, nevis uzticieties migrāciju failiem:
SELECT table_name, column_name, column_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND (table_name, column_name) IN (('orders', 'customer_id'), ('customers', 'id'));
SELECT table_name, engine, create_options
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name IN ('orders', 'customers');
2. Atšķiras rakstzīmju kopa vai collation
Virkņu kolonnām garums var atšķirties, bet rakstzīmju kopai un collation jābūt vienādām. Tas ir biežākais cēlonis datubāzēs, kas gadu gaitā atjauninātas. MySQL 8.0 noklusējuma collation ir utf8mb4_0900_ai_ci, bet tabulas, kas izveidotas 5.7, bieži izmanto utf8mb4_general_ci vai veco utf8. Jauna tabula, kas atsaucas uz vecu, neizdodas, lai gan abas kolonnas "izskatās" kā VARCHAR(36). Risinājums ir pārveidot vienu pusi, lai abas sakristu. Lielai tabulai šī pārveide pati par sevi ir tabulas pārbūve.
3. Vecāka atslēga nav unikāla (MySQL 8.4)
-- MySQL 8.4: the referenced columns must be a PRIMARY KEY or UNIQUE key
ALTER TABLE orders ADD FOREIGN KEY (region_code) REFERENCES regions (code);
-- rejected: regions.code has only a non-unique index
-- (8.4 reports a missing unique key or a missing index for the constraint)
ALTER TABLE regions ADD UNIQUE KEY uq_regions_code (code); -- the real fix
InnoDB agrāk ļāva ārējai atslēgai atsaukties uz jebkuru indeksētu kolonnu, pat neunikālu vai daļēju indeksu. Tas bija nestandarta paplašinājums. Sākot ar MySQL 8.4.0 tas pēc noklusējuma tiek noraidīts: restrict_fk_on_non_standard_key ir ON (Kas jauns MySQL 8.4). To var iestatīt uz OFF sesijai, bet mainīgais ir novecojis, un šādu atslēgu atbalstu paredzēts noņemt (restrict_fk_on_non_standard_key).
Esošās šāda veida ārējās atslēgas pārdzīvo atjaunināšanu no 8.0. Serveris izraksta brīdinājumus ar to nosaukumiem. Problēmas sākas, kad šādu tabulu 8.4 versijā izveidojat no jauna vai maināt, piemēram, migrācijā vai atjaunojot dump. Labojiet pareizi: pievienojiet vecāka kolonnām UNIQUE atslēgu vai vērsiet ārējo atslēgu uz primāro atslēgu. Ja vecāka vērtības nav unikālas, "ārējā atslēga" nekad īsti tāda nav bijusi, un tā ir datu modeļa problēma, par kuru vērts zināt.
4. Trūkst indeksa vai tas nav izmantojams
MySQL vajag indeksu abās pusēs, kurā ārējās atslēgas kolonnas ir pirmās un tādā pašā secībā. Bērna pusē tas vajadzības gadījumā tiek izveidots automātiski. Vecāka pusē ne, un jūs saņemat 1822. Prefiksu indeksi neskaitās, tāpēc TEXT un BLOB kolonnas vispār nevar būt ārējā atslēgā.
5. Nepareizs dzinējs, partīcijas vai ģenerēta kolonna
- Abām tabulām jāizmanto viens glabāšanas dzinējs, un neviena nedrīkst būt pagaidu tabula. Bieži cēlonis ir veca MyISAM vecāktabula.
- InnoDB neatbalsta ārējās atslēgas tabulām ar lietotāja definētām partīcijām, ne vecākam, ne bērnam.
- Ārējā atslēga nevar atsaukties uz virtuālu ģenerētu kolonnu.
Visi trīs ir dokumentētajos ierobežojumos (FOREIGN KEY ierobežojumi). Pārbaudiet engine un create_options ar iepriekšējo vaicājumu.
6. SET NULL uz NOT NULL kolonnas un dublēti nosaukumi
ON DELETE SET NULL uz kolonnas, kas deklarēta kā NOT NULL, nevar darboties, un MySQL to pasaka ar kļūdu 1830. Vai nu atļaujiet NULL, vai izvēlieties citu darbību.
Ierobežojumu nosaukumiem jābūt unikāliem visā datubāzē, ne tikai tabulā. Dublikāts var parādīties arī kā errno 121 kļūdā 1005 (FOREIGN KEY ierobežojumi). Tas notiek, kad migrācija nokopē tabulas definīciju, ieskaitot CONSTRAINT fk_customer, otrā tabulā. Izmantojiet nosaukumus, kuros ir tabula, piemēram, fk_orders_customer.
foreign_key_checks atslēgšana nav risinājums. Tā ļauj ielādēt dump jebkurā tabulu secībā, un tam tā ir domāta. Kolonnu tipu nesakritība tiek noraidīta arī ar atslēgtām pārbaudēm (FOREIGN KEY ierobežojumi). Rindas, kas ierakstītas, kamēr pārbaudes ir atslēgtas, vēlāk nekad netiek pārbaudītas: pārbaužu ieslēgšana neskenē esošos datus (foreign_key_checks).
Kad definīcija ir pareiza, bet dati nav
Ja definīcija ir pareiza, atslēgas pievienošana tabulai ar esošām rindām joprojām var neizdoties bāreņu datu dēļ ar kļūdu 1452. Vispirms atrodiet bāreņus:
-- child rows that point to nothing: fix these before adding the FK
SELECT o.customer_id, COUNT(*)
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
GROUP BY o.customer_id
LIMIT 20;
Tad izlemiet katrai rindai: izveidot trūkstošo vecāku, iestatīt atsauci uz NULL vai dzēst bērnu. Mūsu rakstā par MySQL kļūdu 1452 tīrīšana aprakstīta sīkāk.
Lielās tabulās un noslogotos serveros
Testa datubāzē ārējās atslēgas pievienošana aizņem sekundi. Tabulā ar simtiem miljonu rindu metode ir svarīgāka par sintaksi:
- COPY pret INPLACE. Ar ieslēgtu
foreign_key_checksārējās atslēgas pievienošana atbalsta tikaiALGORITHM=COPY, kas pārbūvē tabulu. Ar atslēgtām pārbaudēm tiek atbalstītsINPLACE(InnoDB online DDL). Tāpēc ātrais ceļš paļaujas uz to, ka bāreņus esat pārbaudījuši paši. Mūsu pieredzē tas esošās rindas jūsu vietā nepārbauda. - Tipu labojumi ir pārbūves. Kolonnas datu tipa maiņa, piemēram,
INTuzBIGINT, lai sakristu ar vecāku, darbojas tikai arALGORITHM=COPY. Plānojiet šo izmaiņu atsevišķi no ierobežojuma, ar tiešsaistes shēmas maiņas rīku. - Rīku atbalsts. gh-ost neatbalsta ārējās atslēgas (gh-ost ierobežojumi). pt-online-schema-change ar tām tiek galā, bet ar bērnu tabulām jāuzmanās. Pirms izvēles izlasiet mūsu ceļvedi MySQL ALTER TABLE bez dīkstāves.
- Replikācija. Garš ALTER izpildās vēlreiz katrā replikā, un tā var atpalikt par visu ALTER ilgumu. 8.4 noteikumam par nestandarta atslēgām ir arī replikācijas nianse: ārējā atslēga, kas primārajā serverī izveidota ar atslēgtu ierobežojumu, replikā izdodas neatkarīgi no replikas iestatījuma (restrict_fk_on_non_standard_key).
- Rakstīšanas izmaksas. Katram ierakstam bērnā un katrai dzēšanai vai atslēgas maiņai vecākā tagad jāpārbauda otra tabula. Mūsu pieredzē tas ir manāms ļoti rakstīšanas noslogotās tabulās un kaskādes dzēšanās, kas skar miljoniem rindu. Mēriet uz produkcijai līdzīgiem datiem.
- Atjaunināšana uz 8.4. Pirms atjaunināšanas sameklējiet ārējās atslēgas, kas norāda uz neunikālām atslēgām. Citādi pirmā migrācija, kas pēc atjaunināšanas šādu tabulu izveido no jauna, neizdosies produkcijā.
-- after the orphan check came back empty:
SET SESSION foreign_key_checks = 0;
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
ALGORITHM=INPLACE; -- fails instead of silently copying
SET SESSION foreign_key_checks = 1;
Galvenās atziņas
- Palaidiet SHOW ENGINE INNODB STATUS vai izlasiet jaunāko kļūdas kodu: 3780, 1822, 6125, 1824, 1830, 1826.
- Veselo skaitļu izmēram un zīmei jāsakrīt precīzi; virkņu rakstzīmju kopai un collation jāsakrīt.
- MySQL 8.4 pēc noklusējuma prasa PRIMARY KEY vai UNIQUE atslēgu uz atsauktajām kolonnām.
- Viens dzinējs abās pusēs, bez lietotāja partīcijām, bez prefiksu indeksiem, bez SET NULL uz NOT NULL.
- Pirms atslēgas pievienošanas pārbaudiet bāreņu rindas, īpaši, ja izmantojat INPLACE ceļu ar atslēgtām pārbaudēm.
- Lielās tabulās plānojiet tipu maiņas un ierobežojumus atsevišķi; gh-ost neatbalsta ārējās atslēgas.