Chiave primaria e chiave esterna inserite SQLite con esempi

Le sezioni seguenti spiegano SQLite I vincoli vengono analizzati nel dettaglio, a partire dalla CHIAVE PRIMARIA e dalla CHIAVE ESTERNA che definiscono e collegano le tabelle, e vengono trattate le regole NOT NULL, DEFAULT, UNIQUE e CHECK che convalidano i dati in ciascuna colonna.

SQLite vincoli

I vincoli di colonna impongono delle regole sui valori inseriti in una colonna al fine di convalidare i dati. Questi vincoli vengono definiti al momento della creazione di una tabella, all'interno della definizione della colonna. Mantengono i dati memorizzati coerenti e accurati, rifiutando i valori che violano le regole impostate, come duplicati, valori nulli o valori inesistenti in una tabella correlata.

SQLite Chiave primaria

Tutti i valori in una colonna chiave primaria devono essere univoci e non nulli. La chiave primaria identifica in modo univoco ogni riga della tabella.

La chiave primaria può essere applicata a una sola colonna o a una combinazione di colonne. In quest'ultimo caso, la combinazione dei valori delle colonne deve essere univoca per tutte le righe della tabella.

Sintassi:

Esistono diversi modi per definire una chiave primaria in una tabella:

Nella definizione della colonna stessa:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Come definizione separata:

PRIMARY KEY(ColumnName);

Per creare una combinazione di colonne come chiave primaria:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Vincoli NOT NULL, DEFAULT, UNIQUE e CHECK

Oltre alla chiave primaria, SQLite fornisce diversi vincoli di colonna che convalidano i valori inseriti in una tabella. I vincoli NOT NULL, DEFAULT, UNIQUE e CHECK sono definiti nella definizione della colonna e ognuno di essi impone una regola specifica sulla colonna. tipo di dati e valori.

Vincolo NOT NULL

Migliori SQLite Il vincolo NOT NULL impedisce a una colonna di avere un valore nullo:

ColumnName INTEGER  NOT NULL;

Vincolo PREDEFINITO

Grazie alla SQLite Vincolo DEFAULT: se non si inserisce alcun valore in una colonna, viene inserito il valore predefinito.

Per esempio:

ColumnName INTEGER DEFAULT 0;

Se si scrive un'istruzione INSERT e non si specifica alcun valore per quella colonna, la colonna avrà il valore 0.

Vincolo UNICO

Migliori SQLite Il vincolo UNIQUE impedisce la presenza di valori duplicati tra tutti i valori della colonna.

Per esempio:

EmployeeId INTEGER NOT NULL UNIQUE;

Questo impone che il valore di "EmployeeId" sia univoco; non sono ammessi valori duplicati. Si noti che ciò si applica solo ai valori della colonna "EmployeeId".

VERIFICA Vincolo

Migliori SQLite Il vincolo CHECK imposta una condizione per verificare un valore inserito. Se il valore non corrisponde alla condizione, non verrà inserito.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Non è possibile inserire un valore inferiore a 10 nella colonna "Quantità".

SQLite chiave esterna

Migliori SQLite La chiave esterna è un vincolo che verifica l'esistenza di un valore presente in una tabella anche in un'altra tabella che ha una relazione con la prima tabella in cui è definita la chiave esterna.

Quando si lavora con più tabelle, a volte due tabelle sono correlate tra loro tramite una colonna in comune. Se si desidera garantire che il valore inserito in una tabella debba essere presente anche nella colonna dell'altra, è necessario utilizzare un vincolo di chiave esterna sulla colonna in comune.

In questo caso, quando si tenta di inserire un valore in quella colonna, la chiave esterna garantirà che il valore inserito esista nella colonna della tabella di riferimento.

Nota che i vincoli di chiave esterna non sono abilitati per impostazione predefinita in SQLiteÈ necessario abilitarli prima eseguendo il seguente comando:

PRAGMA foreign_keys = ON;

Sono stati introdotti vincoli di chiave esterna SQLite a partire dalla versione 3.6.19.

Esempio di SQLite chiave esterna

Supponiamo di avere due tabelle: Studenti e Dipartimenti.

La tabella Studenti contiene un elenco di studenti, mentre la tabella Dipartimenti contiene un elenco dei dipartimenti. Ogni studente appartiene a un dipartimento; ovvero, ogni studente ha una colonna departmentId.

Ora vedremo come il vincolo di chiave esterna può essere utile per garantire che il valore dell'ID del dipartimento nella tabella Studenti debba esistere anche nella tabella Dipartimenti.

Pertanto, se creiamo un vincolo di chiave esterna sul campo DepartmentId nella tabella Students, ogni DepartmentId inserito deve essere presente nella tabella Departments.

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

Per verificare come i vincoli di chiave esterna possono impedire l'inserimento di un elemento o valore non definito in una tabella che ha una relazione con un'altra tabella, esamineremo il seguente esempio.

In questo esempio, la tabella Departments ha una relazione di chiave esterna con la tabella Students, quindi qualsiasi valore departmentId inserito nella tabella Students deve esistere anche nella tabella Departments. Se si tenta di inserire un valore departmentId che non esiste nella tabella Departments, il vincolo di chiave esterna lo impedirà.

Inseriamo due dipartimenti, "IT" e "Arts", nella tabella dei dipartimenti con le seguenti impostazioni: query INSERISCI:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

Le due istruzioni dovrebbero inserire due dipartimenti nella tabella Departments. Puoi verificare che i due valori siano stati inseriti eseguendo successivamente la query "SELECT * FROM Departments":

Prova quindi a inserire un nuovo studente con un departmentId che non esiste nella tabella dei dipartimenti:

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

La riga non verrà inserita e verrà visualizzato un errore con il seguente messaggio: Vincolo FOREIGN KEY non rispettato.

Differenza tra chiave primaria e chiave esterna in SQLite

Le chiavi primarie e le chiavi esterne contribuiscono entrambe a mantenere l'integrità dei dati, ma svolgono ruoli diversi. Una chiave primaria identifica le righe all'interno di una singola tabella, mentre una chiave esterna collega le righe tra due tabelle correlate. La tabella seguente riassume le principali differenze.

Nell'esempio Studenti e Dipartimenti, DepartmentId è la chiave primaria della tabella Dipartimenti e una chiave esterna nella tabella Studenti, che collega ogni studente a un dipartimento valido.

SQLite Chiave primaria composita

Una chiave primaria composta è una chiave primaria formata da due o più colonne. Viene utilizzata quando nessuna singola colonna è univoca di per sé, ma la combinazione delle colonne è univoca per ogni riga. SQLite considera i valori combinati come un'unica chiave.

Ad esempio, una tabella di iscrizione può consentire allo stesso studente di frequentare più corsi e allo stesso studente di frequentare lo stesso corso, ma ogni coppia studente-corso dovrebbe comparire una sola volta:

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

In questo caso, né StudentId né CourseId sono univoci singolarmente, ma la coppia (StudentId, CourseId) lo è; pertanto, lo stesso studente non può essere iscritto due volte allo stesso corso. Si prega di tenere presente i seguenti punti quando si utilizza una chiave composta:

• Utilizzare una chiave composta quando una singola colonna non è in grado di identificare in modo univoco una riga.
• Ogni colonna della chiave composta segue le regole della chiave primaria, quindi il valore combinato deve essere univoco e non nullo.
• Una chiave composta viene definita come una clausola PRIMARY KEY separata a livello di tabella, non all'interno della definizione di una singola colonna.

SQLite Azioni sui tasti esterni: ON DELETE e ON UPDATE

Una chiave esterna può anche controllare cosa accade alle righe figlie quando la riga padre a cui fanno riferimento viene eliminata o aggiornata. Queste azioni referenziali vengono aggiunte con le clausole ON DELETE e ON UPDATE quando si definisce la chiave esterna. SQLite supporta cinque azioni:

• NESSUNA AZIONE — l'azione predefinita, che genera un errore se le righe figlie fanno ancora riferimento alla riga padre.
• LIMITARE — impedisce l'eliminazione o l'aggiornamento immediato, prima che venga eseguita qualsiasi altra modifica.
• IMPOSTA NULLA — imposta la colonna della chiave esterna figlia su null.
• IMPOSTA DEFAULT — imposta la colonna della chiave esterna figlia al suo valore predefinito dichiarato.
• CASCADE — applica la stessa modifica alle righe figlie, quindi eliminando un elemento padre vengono eliminati anche i suoi figli.

L'esempio seguente ricrea la tabella Studenti in modo che l'eliminazione di un dipartimento comporti automaticamente l'eliminazione degli studenti ad esso associati e l'aggiornamento dell'ID di un dipartimento aggiorni gli studenti corrispondenti:

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

Ricorda che le azioni referenziali vengono eseguite solo quando è attivo il supporto per le chiavi esterne, quindi esegui PRAGMA foreign_keys = ON all'inizio di ogni connessione. Senza di esso, SQLite analizza le clausole ON DELETE e ON UPDATE ma non le applica.

DOMANDE FREQUENTI