FOREIGN KEY în MySQL

FOREIGN KEY (coloana) REFERENCES alt_tabel (id) ON DELETE CASCADE

Ce face FOREIGN KEY

O cheie străină spune că valorile dintr-o coloană (de exemplu comenzi.client_id) trebuie să existe în alt tabel (clienti.id). MySQL refuză o comandă pentru un client inexistent.

Cu ON DELETE alegi ce se întâmplă când ștergi rândul din tabelul principal: RESTRICT (implicit, ștergerea este refuzată), CASCADE (se șterg și rândurile legate) sau SET NULL.

Cheile străine funcționează doar cu tabele InnoDB, iar coloanele legate trebuie să aibă același tip.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE clienti (
  id INT PRIMARY KEY,
  nume VARCHAR(50)
);

CREATE TABLE comenzi (
  id INT PRIMARY KEY,
  client_id INT NOT NULL,
  total INT,
  FOREIGN KEY (client_id) REFERENCES clienti (id) ON DELETE CASCADE
);

INSERT INTO clienti VALUES (1, 'Ana Pop'), (2, 'Dan Stoica');
INSERT INTO comenzi VALUES (101, 1, 3580), (102, 1, 1200), (103, 2, 80);

Exemplul 1: ON DELETE CASCADE

Ștergem clientul Ana, iar comenzile ei sunt șterse automat.

DELETE FROM clienti WHERE id = 1;

SELECT * FROM comenzi;

Rezultat

+-----+-----------+-------+
| id  | client_id | total |
+-----+-----------+-------+
| 103 |         2 |    80 |
+-----+-----------+-------+

Exemplul 2: O comandă pentru un client care nu există

Exemplul presupune că tabelele sunt în baza de date magazin.

INSERT INTO comenzi VALUES (104, 99, 500);

Rezultat în MySQL

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`magazin`.`comenzi`, CONSTRAINT `comenzi_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clienti` (`id`) ON DELETE CASCADE)

Greșeli frecvente

• Coloanele au tipuri diferite (INT și INT UNSIGNED) și primești eroarea Foreign key constraint is incorrectly formed.
• Pui ON DELETE CASCADE fără să te gândești. Ștergerea unui singur client poate șterge mii de comenzi, facturi și plăți.

Comenzi înrudite

• CREATE TABLE: Creează un tabel nou, cu coloanele și tipurile lor de date.
• INNER JOIN: Combină rândurile din două tabele care au o valoare comună, de exemplu comenzile cu clienții lor.
• DELETE: Șterge rânduri dintr-un tabel.