Primarni ključ i vanjski ključ SQLite s Primjerima

U donjim odjeljcima objašnjeno je SQLite detaljno opisuju ograničenja, počevši od PRIMARY KEY i FOREIGN KEY koji definiraju i povezuju tablice, pa sve do pravila NOT NULL, DEFAULT, UNIQUE i CHECK koja validiraju podatke u svakom stupcu.

SQLite ograničenja

Ograničenja stupaca nameću pravila na vrijednosti umetnute u stupac kako bi se provjerili podaci. Ta se ograničenja definiraju prilikom stvaranja tablice, unutar definicije stupca. Ona održavaju pohranjene podatke dosljednima i točnima odbacujući vrijednosti koje krše pravila koja ste postavili, kao što su duplikati, null vrijednosti ili vrijednosti koje ne postoje u povezanoj tablici.

SQLite Glavni ključ

Sve vrijednosti u stupcu primarnog ključa moraju biti jedinstvene i ne moraju biti null. Primarni ključ jedinstveno identificira svaki redak u tablici.

Primarni ključ može se primijeniti samo na jedan stupac ili na kombinaciju stupaca. U potonjem slučaju, kombinacija vrijednosti stupaca treba biti jedinstvena za sve retke tablice.

Sintaksa:

Postoji nekoliko različitih načina za definiranje primarnog ključa u tablici:

U samoj definiciji stupca:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Kao zasebna definicija:

PRIMARY KEY(ColumnName);

Da biste stvorili kombinaciju stupaca kao primarnog ključa:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Ograničenja NOT NULL, DEFAULT, UNIQUE i CHECK

Osim primarnog ključa, SQLite pruža nekoliko ograničenja stupaca koja provjeravaju vrijednosti unesene u tablicu. Ograničenja NOT NULL, DEFAULT, UNIQUE i CHECK definirana su u definiciji stupca i svako od njih nameće određeno pravilo na stupac tip podataka i vrijednosti.

NOT NULL ograničenje

The SQLite Ograničenje NOT NULL sprječava da stupac ima null vrijednost:

ColumnName INTEGER  NOT NULL;

DEFAULT ograničenje

Uz SQLite ZADANO ograničenje, ako ne umetnete nikakvu vrijednost u stupac, umjesto toga se umeće zadana vrijednost.

Na primjer:

ColumnName INTEGER DEFAULT 0;

Ako napišete naredbu za umetanje i ne navedete nikakvu vrijednost za taj stupac, stupac će imati vrijednost 0.

JEDINSTVENO ograničenje

The SQLite Ograničenje UNIQUE sprječava dupliciranje vrijednosti među svim vrijednostima u stupcu.

Na primjer:

EmployeeId INTEGER NOT NULL UNIQUE;

Ovim se nameće jedinstvenost vrijednosti "EmployeeId"; duplicirane vrijednosti nisu dopuštene. Imajte na umu da se ovo odnosi samo na vrijednosti stupca "EmployeeId".

PROVJERI ograničenje

The SQLite Ograničenje CHECK postavlja uvjet za provjeru umetnute vrijednosti. Ako vrijednost ne odgovara uvjetu, neće biti umetnuta.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

U stupac "Količina" ne možete umetnuti vrijednost manju od 10.

SQLite Strani kljuc

The SQLite Strani ključ je ograničenje koje provjerava postojanje vrijednosti prisutne u jednoj tablici u drugoj tablici koja ima relaciju s prvom tablicom u kojoj je definiran strani ključ.

Prilikom rada s više tablica, postoje slučajevi kada su dvije tablice međusobno povezane putem jednog zajedničkog stupca. Ako želite osigurati da vrijednost umetnuta u jednu od njih mora postojati i u stupcu druge tablice, tada biste trebali koristiti ograničenje stranog ključa na zajedničkom stupcu.

U ovom slučaju, kada pokušate umetnuti vrijednost u taj stupac, strani ključ će osigurati da umetnuta vrijednost postoji u stupcu referencirane tablice.

Imajte na umu da ograničenja stranog ključa nisu omogućena prema zadanim postavkama u SQLitePrvo ih morate omogućiti pokretanjem sljedeće naredbe:

PRAGMA foreign_keys = ON;

Ograničenja stranog ključa uvedena su u SQLite počevši od verzije 3.6.19.

Primjer SQLite Strani kljuc

Pretpostavimo da imamo dvije tablice: Studenti i Odjeli.

Tablica Studenti sadrži popis studenata, a tablica Odjeli sadrži popis odjela. Svaki student pripada odjelu, odnosno svaki student ima stupac ID odjela.

Sada ćemo vidjeti kako ograničenje stranog ključa može biti korisno kako bi se osiguralo da vrijednost ID-a odjela u tablici Studenti mora postojati u tablici Odjeli.

Dakle, ako stvorimo ograničenje stranog ključa na DepartmentId u tablici Studenti, svaki umetnuti departmentId mora biti prisutan u tablici Odjeli.

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

Kako bismo provjerili kako ograničenja stranog ključa mogu spriječiti umetanje nedefiniranog elementa ili vrijednosti u tablicu koja ima relaciju s drugom tablicom, pogledat ćemo sljedeći primjer.

U ovom primjeru, tablica Odjeli ima relaciju vanjskog ključa s tablicom Studenti, tako da svaka vrijednost departmentId umetnuta u tablicu Studenti mora postojati u tablici Odjeli. Ako pokušate umetnuti vrijednost departmentId koja ne postoji u tablici Odjeli, ograničenje vanjskog ključa spriječit će vas u tome.

U tablicu Odjela umetnimo dva odjela, „IT“ i „Umjetnost“ sa sljedećim UMETNI upite:

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

Dvije naredbe trebale bi umetnuti dva odjela u tablicu Odjeli. Možete potvrditi da su dvije vrijednosti umetnute pokretanjem upita "SELECT * FROM Odjeli" nakon toga:

Zatim pokušajte umetnuti novog studenta s departmentId-om koji ne postoji u tablici Departments:

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

Redak se neće umetnuti i dobit ćete grešku koja kaže: Ograničenje STRANOG KLJUČA nije uspjelo.

Razlika između primarnog ključa i stranog ključa u SQLite

Primarni i strani ključevi pomažu u održavanju integriteta podataka, ali imaju različite uloge. Primarni ključ identificira retke unutar jedne tablice, dok strani ključ povezuje retke između dvije povezane tablice. Tablica u nastavku sažima glavne razlike.

U primjeru Studenti i Odjeli, DepartmentId je primarni ključ tablice Odjeli i strani ključ u tablici Studenti, što svakog studenta povezuje s valjanim odjelom.

SQLite Kompozitni primarni ključ

Složeni primarni ključ je primarni ključ sastavljen od dva ili više stupaca. Koristi se kada nijedan stupac nije jedinstven sam za sebe, ali je kombinacija stupaca jedinstvena za svaki redak. SQLite tretira kombinirane vrijednosti kao jedan ključ.

Na primjer, tablica upisa može dopustiti istom studentu mnogo kolegija i isti kolegij za mnogo studenata, no svaki par student-kolegij trebao bi se pojaviti samo jednom:

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

Ovdje ni StudentId ni CourseId nisu jedinstveni sami po sebi, već je par (StudentId, CourseId), tako da isti student ne može biti dvaput upisan na isti kolegij. Imajte na umu sljedeće točke kada koristite složeni ključ:

• Koristite složeni ključ kada jedan stupac ne može jedinstveno identificirati redak.
• Svaki stupac u složenom ključu slijedi pravila primarnog ključa, tako da kombinirana vrijednost mora biti jedinstvena i ne smije biti null.
• Kompozitni ključ piše se kao zasebna klauzula PRIMARY KEY na razini tablice, a ne unutar definicije jednog stupca.

SQLite Akcije stranog ključa: PRI BRISANJU i PRI AŽURIRANJU

Strani ključ također može kontrolirati što se događa s podređenim redcima kada se roditeljski red na koji se referiraju izbriše ili ažurira. Ove referencijalne radnje dodaju se klauzulama ON DELETE i ON UPDATE kada definirate strani ključ. SQLite podržava pet akcija:

• NEMA AKCIJE — zadana radnja koja izaziva grešku ako podređeni retci i dalje referenciraju roditelja.
• OGRANIČITI — sprječava trenutno brisanje ili ažuriranje, prije nego što se izvrši bilo koja druga promjena.
• POSTAVITE NULL — postavlja stupac podređenog stranog ključa na null.
• POSTAVI ZADANO — postavlja stupac podređenog stranog ključa na njegovu deklariranu zadanu vrijednost.
• CASCADE — primjenjuje istu promjenu na podređene retke, pa brisanjem roditelja briše se i njegova podređena stanja.

U donjem primjeru ponovno se stvara tablica Studenti tako da brisanje odjela automatski briše i njegove studente, a ažuriranje ID-a odjela ažurira odgovarajuće studente:

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

Imajte na umu da se referencijalne akcije izvode samo kada je uključena podrška za strani ključ, stoga pokrenite PRAGMA foreign_keys = ON na početku svake veze. Bez nje, SQLite analizira klauzule ON DELETE i ON UPDATE, ali ih ne provodi.

Pitanja i odgovori