Primární klíč a cizí klíč SQLite s příklady

Níže uvedené části vysvětlují SQLite podrobně popíšeme omezení, počínaje PRIMARY KEY a FOREIGN KEY, které definují a propojují tabulky, a pokryjeme pravidla NOT NULL, DEFAULT, UNIQUE a CHECK, která ověřují data v každém sloupci.

SQLite Omezení

Omezení sloupců vynucují pravidla pro hodnoty vložené do sloupce za účelem ověření dat. Tato omezení jsou definována při vytváření tabulky, v definici sloupce. Udržují uložená data konzistentní a přesná tím, že odmítají hodnoty, které porušují nastavená pravidla, jako jsou duplikáty, hodnoty null nebo hodnoty, které neexistují v související tabulce.

SQLite Primární klíč

Všechny hodnoty ve sloupci primárního klíče musí být jedinečné a ne null. Primární klíč jednoznačně identifikuje každý řádek v tabulce.

Primární klíč lze použít pouze pro jeden sloupec nebo pro kombinaci sloupců. V druhém případě by kombinace hodnot sloupců měla být jedinečná pro všechny řádky tabulky.

Syntaxe:

Existuje několik různých způsobů, jak definovat primární klíč v tabulce:

V samotné definici sloupce:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Jako samostatná definice:

PRIMARY KEY(ColumnName);

Chcete-li vytvořit kombinaci sloupců jako primární klíč:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Omezení NOT NULL, DEFAULT, UNIQUE a CHECK

Kromě primárního klíče, SQLite poskytuje několik omezení sloupců, která ověřují hodnoty zadané do tabulky. Omezení NOT NULL, DEFAULT, UNIQUE a CHECK jsou definována v definici sloupce a každé z nich vynucuje specifické pravidlo pro daný sloupec. datový typ a hodnoty.

Omezení NOT NULL

Jedno SQLite Omezení NOT NULL zabraňuje tomu, aby sloupec měl hodnotu null:

ColumnName INTEGER  NOT NULL;

VÝCHOZÍ omezení

S hodinkami SQLite VÝCHOZÍ omezení: pokud do sloupce nevložíte žádnou hodnotu, vloží se místo toho výchozí hodnota.

Například:

ColumnName INTEGER DEFAULT 0;

Pokud napíšete příkaz INSERT a pro daný sloupec nezadáte žádnou hodnotu, bude mít sloupec hodnotu 0.

JEDINEČNÉ omezení

Jedno SQLite Omezení UNIQUE zabraňuje duplicitním hodnotám mezi všemi hodnotami ve sloupci.

Například:

EmployeeId INTEGER NOT NULL UNIQUE;

Toto vynucuje, aby hodnota „EmployeeId“ byla jedinečná; duplicitní hodnoty nejsou povoleny. Upozorňujeme, že se to týká pouze hodnot ve sloupci „EmployeeId“.

ZKONTROLUJTE omezení

Jedno SQLite Omezení CHECK nastavuje podmínku pro kontrolu vložené hodnoty. Pokud hodnota neodpovídá podmínce, nebude vložena.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Do sloupce „Množství“ nelze vložit hodnotu menší než 10.

SQLite Cizí klíč

Jedno SQLite Cizí klíč je omezení, které ověřuje existenci hodnoty přítomné v jedné tabulce v jiné tabulce, která má vztah s první tabulkou, kde je cizí klíč definován.

Při práci s více tabulkami existují případy, kdy dvě tabulky spolu souvisí prostřednictvím jednoho společného sloupce. Pokud chcete zajistit, aby hodnota vložená do jedné z nich musela existovat i ve sloupci druhé tabulky, měli byste na společný sloupec použít omezení cizího klíče.

V tomto případě, když se pokusíte vložit hodnotu do daného sloupce, cizí klíč zajistí, aby vložená hodnota existovala ve sloupci odkazované tabulky.

Všimněte si, že omezení cizího klíče nejsou ve výchozím nastavení povolena v SQLiteNejprve je musíte povolit spuštěním následujícího příkazu:

PRAGMA foreign_keys = ON;

Omezení cizího klíče byla zavedena v SQLite počínaje verzí 3.6.19.

Příklad SQLite Cizí klíč

Předpokládejme, že máme dvě tabulky: Studenti a Oddělení.

Tabulka Students obsahuje seznam studentů a tabulka Departments obsahuje seznam kateder. Každý student patří do určité katedry, tj. každý student má sloupec departmentId.

Nyní se podíváme, jak může být omezení cizího klíče užitečné k zajištění toho, aby hodnota ID oddělení v tabulce Students musela existovat v tabulce Departments.

Pokud tedy v tabulce Students vytvoříme omezení cizího klíče pro DepartmentId, musí být každé vložené departmentId přítomno v tabulce 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)
);

Abychom ověřili, jak omezení cizího klíče mohou zabránit vložení nedefinovaného prvku nebo hodnoty do tabulky, která má vztah k jiné tabulce, podíváme se na následující příklad.

V tomto příkladu má tabulka Departments vztah cizího klíče s tabulkou Students, takže jakákoli hodnota departmentId vložená do tabulky Students musí v tabulce Departments existovat. Pokud se pokusíte vložit hodnotu departmentId, která v tabulce Departments neexistuje, omezení cizího klíče vám v tom zabrání.

Vložme do tabulky Oddělení dvě oddělení, „IT“ a „Umění“, s následujícím VKLADÁNÍ dotazů:

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

Tyto dva příkazy by měly do tabulky Departments vložit dvě oddělení. Vložení obou hodnot můžete ověřit spuštěním dotazu „SELECT * FROM Departments“:

Pak zkuste vložit nového studenta s ID oddělení, které v tabulce Oddělení neexistuje:

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

Řádek se nevloží a zobrazí se chyba: Omezení FOREIGN KEY selhalo.

Rozdíl mezi primárním klíčem a cizím klíčem v SQLite

Primární i cizí klíče pomáhají udržovat integritu dat, ale hrají odlišné role. Primární klíč identifikuje řádky v jedné tabulce, zatímco cizí klíč propojuje řádky napříč dvěma souvisejícími tabulkami. Níže uvedená tabulka shrnuje hlavní rozdíly.

V příkladu Studenti a oddělení je DepartmentId primárním klíčem tabulky Oddělení a cizím klíčem v tabulce Studenti, který spojuje každého studenta s platným oddělením.

SQLite Složený primární klíč

Složený primární klíč je primární klíč složený ze dvou nebo více sloupců. Používá se, když žádný sloupec není sám o sobě jedinečný, ale kombinace sloupců je jedinečná pro každý řádek. SQLite zachází s kombinovanými hodnotami jako s jedním klíčem.

Například tabulka zápisů může povolit stejného studenta v mnoha kurzech a stejný kurz pro mnoho studentů, ale každý pár student-kurz by se měl objevit pouze jednou:

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

V tomto případě není ani StudentId, ani CourseId jedinečné samo o sobě, ale dvojice (StudentId, CourseId) je jedinečná, takže stejný student nemůže být zapsán do stejného kurzu dvakrát. Při použití složeného klíče mějte na paměti následující body:

• Složený klíč použijte, pokud jeden sloupec nemůže jednoznačně identifikovat řádek.
• Každý sloupec v kompozitním klíči se řídí pravidly primárního klíče, takže kombinovaná hodnota musí být jedinečná a ne null.
• Složený klíč se zapisuje jako samostatná klauzule PRIMARY KEY na úrovni tabulky, nikoli uvnitř definice jednoho sloupce.

SQLite Akce cizího klíče: ON DELETE a ON UPDATE

Cizí klíč může také řídit, co se stane s podřízenými řádky, když je nadřazený řádek, na který odkazují, odstraněn nebo aktualizován. Tyto referenční akce se přidávají pomocí klauzulí ON DELETE a ON UPDATE při definování cizího klíče. SQLite podporuje pět akcí:

• ŽÁDNÁ AKCE — výchozí akce, která vyvolá chybu, pokud podřízené řádky stále odkazují na rodičovský řádek.
• OMEZIT — zabrání okamžitému odstranění nebo aktualizaci, než se spustí jakákoli jiná změna.
• SET NULL — nastaví sloupec podřízeného cizího klíče na hodnotu null.
• NASTAVIT VÝCHOZÍ — nastaví sloupec podřízeného cizího klíče na deklarovanou výchozí hodnotu.
• CASCADE — aplikuje stejnou změnu na podřízené řádky, takže odstraněním rodičovského řádku se smažou i jeho podřízené řádky.

Následující příklad znovu vytvoří tabulku Studenti tak, že odstranění oddělení automaticky odstraní i jeho studenty a aktualizace ID oddělení aktualizuje odpovídající studenty:

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

Nezapomeňte, že referenční akce se spouštějí pouze tehdy, když je zapnutá podpora cizích klíčů, proto na začátku každého připojení spusťte příkaz PRAGMA foreign_keys = ON. Bez něj SQLite analyzuje klauzule ON DELETE a ON UPDATE, ale nevynucuje je.

Nejčastější dotazy