Cheia primară și cheia străină SQLite cu Exemple

Secțiunile de mai jos explică SQLite constrângerile în detaliu, începând cu PRIMARY KEY și FOREIGN KEY care definesc și conectează tabelele și acoperind regulile NOT NULL, DEFAULT, UNIQUE și CHECK care validează datele din fiecare coloană.

SQLite Constrângerile

Restricțiile de coloană impun reguli asupra valorilor inserate într-o coloană pentru a valida datele. Aceste restricții sunt definite la crearea unui tabel, în interiorul definiției coloanei. Acestea mențin datele stocate consecvente și precise prin respingerea valorilor care încalcă regulile setate, cum ar fi duplicatele, valorile nule sau valorile care nu există într-un tabel corelat.

SQLite Cheia principala

Toate valorile dintr-o coloană de cheie primară trebuie să fie unice și nu nule. Cheia primară identifică în mod unic fiecare rând din tabel.

Cheia primară poate fi aplicată unei singure coloane sau unei combinații de coloane. În acest ultim caz, combinația valorilor coloanelor trebuie să fie unică pentru toate rândurile tabelului.

Sintaxă:

Există mai multe moduri diferite de a defini o cheie primară într-un tabel:

În definiția coloanei în sine:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Ca o definiție separată:

PRIMARY KEY(ColumnName);

Pentru a crea o combinație de coloane ca cheie primară:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Constrângerile NOT NULL, DEFAULT, UNIQUE și CHECK

Pe lângă cheia primară, SQLite oferă mai multe constrângeri de coloană care validează valorile introduse într-un tabel. Constrângerile NOT NULL, DEFAULT, UNIQUE și CHECK sunt definite fiecare în definiția coloanei și fiecare dintre ele impune o regulă specifică asupra valorilor introduse într-un tabel. tip de date și valori.

NOT NULL Constrângere

SQLite Constrângerea NOT NULL împiedică o coloană să aibă o valoare nulă:

ColumnName INTEGER  NOT NULL;

Constrângere DEFAULT

Cu SQLite Constrângere DEFAULT, dacă nu introduceți nicio valoare într-o coloană, se inserează în schimb valoarea implicită.

De exemplu:

ColumnName INTEGER DEFAULT 0;

Dacă scrieți o instrucțiune de inserare și nu specificați nicio valoare pentru acea coloană, coloana va avea valoarea 0.

Constrângere unică

SQLite Constrângerea UNIQUE previne valorile duplicate între toate valorile coloanei.

De exemplu:

EmployeeId INTEGER NOT NULL UNIQUE;

Aceasta impune ca valoarea „EmployeeId” să fie unică; nu sunt permise valori duplicate. Rețineți că acest lucru se aplică numai valorilor din coloana „EmployeeId”.

VERIFICAREA Constrângerii

SQLite Constrângerea CHECK stabilește o condiție pentru a verifica o valoare inserată. Dacă valoarea nu corespunde condiției, aceasta nu va fi inserată.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Nu puteți introduce o valoare mai mică de 10 în coloana „Cantitate”.

SQLite Cheie externă

SQLite O cheie externă este o constrângere care verifică existența unei valori prezente într-un tabel dintr-un alt tabel care are o relație cu primul tabel în care este definită cheia externă.

Când lucrați cu mai multe tabele, există cazuri în care două tabele se leagă între ele printr-o coloană comună. Dacă doriți să vă asigurați că valoarea introdusă într-unul dintre ele trebuie să existe în coloana celuilalt tabel, atunci ar trebui să utilizați o constrângere de cheie externă asupra coloanei comune.

În acest caz, când încercați să inserați o valoare în coloana respectivă, cheia externă va asigura că valoarea inserată există în coloana tabelului la care se face referire.

Rețineți că restricțiile de cheie externă nu sunt activate în mod implicit în SQLiteTrebuie să le activați mai întâi rulând următoarea comandă:

PRAGMA foreign_keys = ON;

Au fost introduse constrângeri cheie străine în SQLite începând cu versiunea 3.6.19.

Exemplu de SQLite Cheie externă

Să presupunem că avem două tabele: Studenți și Departamente.

Tabelul Studenți are o listă de studenți, iar tabelul Departamente are o listă a departamentelor. Fiecare student aparține unui departament; adică, fiecare student are o coloană departmentId.

Acum vom vedea cum constrângerea cheii externe poate fi utilă pentru a ne asigura că valoarea id-ului departamentului din tabelul Studenți trebuie să existe și în tabelul Departamente.

Deci, dacă creăm o constrângere de cheie externă asupra ID-ului de departament din tabelul Studenți, fiecare ID de departament introdus trebuie să fie prezent în tabelul Departamente.

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

Pentru a verifica cum constrângerile de cheie externă pot împiedica inserarea unui element sau a unei valori nedefinite într-un tabel care are o relație cu un alt tabel, vom analiza următorul exemplu.

În acest exemplu, tabelul Departamente are o relație de cheie externă cu tabelul Elevi, deci orice valoare departmentId introdusă în tabelul Elevi trebuie să existe în tabelul Departamente. Dacă încercați să inserați o valoare departmentId care nu există în tabelul Departamente, constrângerea de cheie externă vă va împiedica să faceți acest lucru.

Să introducem două departamente, „IT” și „Arte”, în tabelul Departamente cu următoarele Interogări INSERT:

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

Cele două instrucțiuni ar trebui să insereze două departamente în tabelul Departments. Puteți confirma că cele două valori au fost inserate executând ulterior interogarea „SELECT * FROM Departments”:

Apoi încercați să introduceți un student nou cu un departmentId care nu există în tabelul Departments:

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

Rândul nu va fi inserat și veți primi o eroare care spune: Constrângerea FOREIGN KEY a eșuat.

Diferența dintre cheia primară și cheia externă în SQLite

Cheile primare și cheile externe ajută ambele la menținerea integrității datelor, dar joacă roluri diferite. O cheie primară identifică rândurile dintr-un singur tabel, în timp ce o cheie externă leagă rândurile din două tabele corelate. Tabelul de mai jos rezumă principalele diferențe.

În exemplul cu Studenți și Departamente, DepartmentId este cheia primară a tabelului Departamente și o cheie externă în tabelul Studenți, care leagă fiecare student de un departament valid.

SQLite Cheie primară compusă

O cheie primară compozită este o cheie primară formată din două sau mai multe coloane. Se utilizează atunci când nicio coloană nu este unică în sine, dar combinația de coloane este unică pentru fiecare rând. SQLite tratează valorile combinate ca o singură cheie.

De exemplu, un tabel de înscrieri poate permite același student la mai multe cursuri și același curs pentru mai mulți studenți, însă fiecare pereche student-curs ar trebui să apară o singură dată:

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

Aici, nici StudentId, nici CourseId nu sunt unice individual, ci perechea (StudentId, CourseId) este unică, deci același student nu poate fi înscris de două ori în același curs. Rețineți următoarele aspecte atunci când utilizați o cheie compusă:

• Folosește o cheie compusă atunci când o singură coloană nu poate identifica în mod unic un rând.
• Fiecare coloană din cheia compozită respectă regulile cheii primare, deci valoarea combinată trebuie să fie unică și nu nulă.
• O cheie compozită este scrisă ca o clauză PRIMARY KEY separată la nivel de tabel, nu în interiorul unei singure definiții de coloană.

SQLite Acțiuni cheie externă: LA ȘTERGERE și LA ACTUALIZARE

O cheie externă poate controla, de asemenea, ce se întâmplă cu rândurile copil atunci când rândul părinte la care fac referire este șters sau actualizat. Aceste acțiuni referențiale sunt adăugate cu clauzele ON DELETE și ON UPDATE atunci când definiți cheia externă. SQLite susține cinci acțiuni:

• FARA ACTIUNE — acțiunea implicită, care generează o eroare dacă rândurile copil fac încă referire la părinte.
• RESTRICȚI — previne ștergerea sau actualizarea imediat, înainte de executarea oricărei alte modificări.
• SET NULL — setează coloana cheii externe copil la nulă.
• SETAȚI DEFAULT — setează coloana cheii externe copil la valoarea implicită declarată.
• CASCADĂ — aplică aceeași modificare rândurilor copil, astfel încât ștergerea unui părinte îi șterge și pe copiii acestuia.

Exemplul de mai jos recreează tabelul Studenți astfel încât ștergerea unui departament șterge automat studenții acestuia, iar actualizarea ID-ului unui departament actualizează studenții corespunzători:

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

Rețineți că acțiunile referențiale rulează doar atunci când este activat suportul pentru chei externe, așadar rulați PRAGMA foreign_keys = ON la începutul fiecărei conexiuni. Fără aceasta, SQLite analizează clauzele ON DELETE și ON UPDATE, dar nu le aplică.

Întrebări frecvente