Cheile externe FOREIGN KEY

Cheile externe permit stabilirea relațiilor între tabele. Cheia externă este stabilită pentru coloanele dintr-o tabelă dependentă sau subordonată și indică către una dintre coloanele din tabela principală. De obicei, cheia externă indică către cheia primară din tabela principală asociată.

Sintaxa generală pentru stabilirea unei chei externe la nivelul tabelei este:

[CONSTRAINT nume_restrictie]
FOREIGN KEY (coloana1, coloana2, ... coloanaN)
REFERENCES tabela_principala (coloana_tabela_principala1, coloana_tabela_principala2, ... coloana_tabela_principalaN)
[ON DELETE actiune]
[ON UPDATE actiune]

Pentru a crea o restricție de cheie externă, după cuvântul cheie FOREIGN KEY se specifică coloana tabelului care va reprezenta cheia externă. Apoi, după cuvântul cheie REFERENCES, se indică numele tabelului asociat, iar între paranteze numele coloanei asociate care va fi indicată de cheia externă.

După expresia REFERENCES urmează expresiile ON DELETE și ON UPDATE, care definesc acțiunile la ștergerea și actualizarea rândului din tabelul principal, respectiv.

CREATE TABLE Customers
(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   Age INT,
   FirstName VARCHAR(20) NOT NULL,
   LastName VARCHAR(20) NOT NULL,
   Phone VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Orders
(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   CustomerId INT,
   CreatedAt Date,
   FOREIGN KEY (CustomerId)  REFERENCES Customers (Id)
);

În acest caz, sunt definite tabelele "Customers" și "Orders". "Customers" este tabela principală și reprezintă un client. "Orders" este o tabelă dependentă și reprezintă o comandă plasată de către client. Tabela "Orders" este legată de tabela "Customers" prin intermediul coloanei "CustomerId", care este un cheie externă ce indică către coloana "Id" din tabela "Customers".

Cu ajutorul operatorului CONSTRAINT, poți atribui un nume restricției cheii externe.

CREATE TABLE Orders
(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   CustomerId INT,
   CreatedAt Date,
   CONSTRAINT orders_custonmers_fk
   FOREIGN KEY (CustomerId)  REFERENCES Customers (Id)
);

ON DELETE și ON UPDATE

Cu ajutorul expresiilor ON DELETE și ON UPDATE, poți stabili acțiuni care se desfășoară, respectiv, la ștergerea și actualizarea unui rând asociat din tabela principală. Ca acțiuni, pot fi folosite următoarele opțiuni:

• CASCADE: șterge sau actualizează automat rândurile din tabela dependentă la ștergerea sau actualizarea rândurilor asociate din tabela principală
• SET NULL: atunci când se șterge sau actualizează un rând asociat din tabela principală, setează valoarea NULL pentru coloana cheii externe. (În acest caz, coloana cheii externe trebuie să permită valori NULL.)
• RESTRICT: respinge ștergerea sau actualizarea rândurilor din tabela principală în prezența rândurilor asociate în tabela dependentă
• NO ACTION: echivalent cu RESTRICT
• SET DEFAULT: atunci când se șterge un rând asociat din tabela principală, setează valoarea implicită pentru coloana cheii externe, definită prin atributul DEFAULT. Deși această opțiune este disponibilă în principiu, motorul InnoDB nu susține această expresie

Ștergerea în cascădă

Ștergerea în cascădă permite ștergerea automată a tuturor rândurilor asociate din tabela dependentă atunci când se șterge un rând din tabela principală. Aceasta se realizează folosind opțiunea:

• CASCADE:

CREATE TABLE Orders
(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   CustomerId INT,
   CreatedAt Date,
   FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);

Similar, funcționează și expresia ON UPDATE CASCADE. La modificarea valorii cheii primare, valoarea cheii externe asociate se actualizează automat. Cu toate acestea, deoarece cheile primare sunt modificate foarte rar și, în principiu, nu se recomandă utilizarea coloanelor cu valori schimbătoare ca chei primare, în practică, expresia ON UPDATE este folosită rar.

Setarea la NULL

Atunci când se utilizează opțiunea SET NULL pentru cheia externă, este necesar ca coloana cheii externe să admită valoarea NULL:

CREATE TABLE Orders
(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   CustomerId INT,
   CreatedAt Date,
   FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL
);