Incidentu reaģēšana

MySQL "Cannot add foreign key constraint": cēloņi un risinājumi

Kļūda 1215, errno 150, 3780, 1822, bet MySQL 8.4 arī jauna, 6125. Visas nozīmē vienu un to pašu: ārējās atslēgas abas puses nesakrīt. Lūk, kā noskaidrot, kas tieši nesakrīt, un kā pievienot atslēgu lielai tabulai bez ilgas dīkstāves.

· 14 min lasīšanai · AG Data komanda

"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):

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

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:

-- 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

MySQL Kļūdas InnoDB Shēma
Sadarbība ar mums

Shēmas izmaiņas iestrēgst uz ārējām atslēgām?

Mēs salabojam neizdevušās migrācijas, iztīrām bāreņu datus un pievienojam ierobežojumus lielām produkcijas MySQL tabulām bez ilgām rakstīšanas bloķēšanām.

Sazināties Vairāk no emuāra