VINCOLI: integrità del database

Controllo dell'integrità del database

Un'altra cosa importante da sapere sui database è CONSTRAINS. Con l'aiuto dei vincoli, puoi controllare le modifiche ai dati nelle tue tabelle e mantenerne l'integrità e la coerenza.

Cos'è la coerenza dei dati quando parliamo di un database?

Prendiamo il nostro negozio online con le tabelle dei dipendenti, dei prodotti e delle attività . Sappiamo già che ci possono essere attività nella tabella delle attività che non sono assegnate a nessuno: l'impiegato_id di tali righe è NULL.

Ma cosa succede se c'è una voce nella tabella delle attività con un employee_id uguale, diciamo, a 115? Dopotutto, non abbiamo un tale dipendente. Non abbiamo un dipendente con id = 115 nella tabella dei dipendenti. Allo stesso tempo, nella tabella delle attività è presente un collegamento a un dipendente con questo ID. Questo è un esempio di incoerenza dei dati .

Allora come riconciliare questi dati? Idealmente, sarebbe così che il server SQL, con qualsiasi modifica dei dati, controllasse tutte queste sfumature. E c'è una tale opportunità, si chiama FOREIGN_KEY.

Se qualche colonna nella tua tabella contiene non solo numeri, ma id righe da un'altra tabella, allora questo può essere specificato in modo esplicito.

Aggiunta di una FOREIGN KEY

Tale chiave può essere aggiunta alla tabella sia nella fase della sua creazione, sia successivamente, utilizzando ALTER TABLE. Il formato non è fondamentalmente diverso. Presenteremo entrambe le opzioni.

La forma generale di tale chiave/regola è:

FOREIGN KEY (column)
  	REFERENCES table(column)

Aggiungiamo questa chiave/regola alla tabella delle attività per assicurarci che tutti gli ID_impiegato della tabella facciano riferimento a una voce esistente nella tabella dei dipendenti. Questo script avrà questo aspetto:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

E se decidessimo di aggiungere questa regola al momento della creazione della tabella delle attività, il codice sarebbe simile a questo:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

A proposito, ci sono situazioni in cui la stringa a cui ci riferiamo ha una chiave composta univoca: ad esempio, "Nome e anno di nascita" o "productCategoryId e productId". Quindi FOREIGN KEY può essere scritto così:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

FOREIGN KEY e modifica dei dati

Ora immagina una situazione in cui abbiamo deciso di aggiornare alcuni dati nella tabella dei dipendenti e il nostro ID dipendente è cambiato. Cosa accadrà ai dati nella tabella delle attività? Esatto, diventeranno irrilevanti e l'integrità del nostro database verrà violata.

Per evitare che ciò accada, puoi dire a SQL Server di modificare l'impiegato_id di tutte le righe in tutte le tabelle che fanno riferimento a questo particolare id modificato quando l'id nella tabella dei dipendenti cambia.

Tali script sono chiamati OnUpdate e OnDelete . Cosa fare se l'ID del record cambia e cosa fare se il record viene eliminato?

Con la rimozione, non tutto è così semplice. Se nel database sono presenti oggetti dipendenti rappresentati da stringhe che fanno riferimento l'uno all'altro, è possibile un'ampia varietà di scenari di comportamento durante l'eliminazione di un oggetto.

Supponiamo di eliminare un utente del sito, il che significa che dobbiamo eliminare tutta la sua corrispondenza personale. Ma è improbabile che dovremmo rimuovere tutti i suoi commenti pubblici.

Oppure un dipendente si licenzia. Sarebbe strano se si licenziasse e allo stesso tempo tutti i compiti a lui assegnati scomparissero dal database. Ma se fossero rimasti nominati non da lui, sarebbe anche andata male. È più corretto fare in modo che il dipendente possa dimettersi dopo aver riassegnato tutti i suoi compiti ad altre persone.

Ecco come possiamo descrivere questi scenari usando FOREIGN KEY. La forma generale di tale chiave/regola è:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Cosa fare in caso di cancellazione (ON DELETE) o modifica (ON UPDATE) dei record? In totale, ci possono essere 5 opzioni affinché il server SQL agisca in ognuna di queste situazioni:

Ecco come potremmo modificare la nostra tabella delle attività:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Cosa c'è scritto qui:

ON UPDATE CASCADE : Se la chiave id nella tabella dei dipendenti cambia, cambia anche l'impiegato_id nella tabella delle attività che vi fa riferimento.

ON DELETE RESTRICT : Se una riga viene eliminata dalla tabella dei dipendenti e vi viene fatto riferimento dalla tabella delle attività, impedire che la riga venga eliminata dalla tabella dei dipendenti.