Primär nyckel och främmande nyckel in SQLite med exempel

Avsnitten nedan förklarar SQLite begränsningar i detalj, med början i PRIMARY KEY och FOREIGN KEY som definierar och kopplar samman tabeller, och som täcker reglerna NOT NULL, DEFAULT, UNIQUE och CHECK som validerar data i varje kolumn.

SQLite begränsningar

Kolumnbegränsningar tillämpar regler på värden som infogas i en kolumn för att validera data. Dessa begränsningar definieras när en tabell skapas, inuti kolumndefinitionen. De håller lagrade data konsekventa och korrekta genom att avvisa värden som bryter mot de regler du anger, till exempel dubbletter, nullvärden eller värden som inte finns i en relaterad tabell.

SQLite Primärnyckel

Alla värden i en primärnyckelkolumn måste vara unika och inte null. Primärnyckeln identifierar unikt varje rad i tabellen.

Primärnyckeln kan tillämpas på endast en kolumn eller på en kombination av kolumner. I det senare fallet ska kombinationen av kolumnernas värden vara unik för alla tabellens rader.

Syntax:

Det finns flera olika sätt att definiera en primärnyckel i en tabell:

I själva kolumndefinitionen:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Som en separat definition:

PRIMARY KEY(ColumnName);

Så här skapar du en kombination av kolumner som en primärnyckel:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite NOT NULL, DEFAULT, UNIQUE och CHECK-begränsningar

Förutom primärnyckeln, SQLite tillhandahåller flera kolumnbegränsningar som validerar värdena som anges i en tabell. Begränsningarna NOT NULL, DEFAULT, UNIQUE och CHECK definieras alla i kolumndefinitionen, och var och en av dem tillämpar en specifik regel på kolumnens data typ och värderingar.

NOT NULL Begränsning

Ocuco-landskapet SQLite NOT NULL-begränsningen förhindrar att en kolumn har ett nullvärde:

ColumnName INTEGER  NOT NULL;

STANDARD Begränsning

Med SQLite STANDARDbegränsning, om du inte infogar något värde i en kolumn infogas standardvärdet istället.

Till exempel:

ColumnName INTEGER DEFAULT 0;

Om du skriver en insert-sats och inte anger något värde för den kolumnen, kommer kolumnen att ha värdet 0.

Unik begränsning

Ocuco-landskapet SQLite UNIQUE-begränsningen förhindrar dubbletter av värden bland alla värden i kolumnen.

Till exempel:

EmployeeId INTEGER NOT NULL UNIQUE;

Detta tvingar fram att värdet ”EmployeeId” är unikt; inga duplicerade värden är tillåtna. Observera att detta endast gäller värdena i kolumnen ”EmployeeId”.

KONTROLLERA begränsning

Ocuco-landskapet SQLite CHECK-villkoret anger ett villkor för att kontrollera ett infogat värde. Om värdet inte matchar villkoret kommer det inte att infogas.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Du kan inte ange ett värde som är mindre än 10 i kolumnen ”Kvantitet”.

SQLite Främmande nyckel

Ocuco-landskapet SQLite En främmande nyckel är en begränsning som verifierar förekomsten av ett värde som finns i en tabell i en annan tabell som har en relation till den första tabellen där den främmande nyckeln är definierad.

När man arbetar med flera tabeller finns det fall där två tabeller relaterar till varandra genom en gemensam kolumn. Om du vill säkerställa att värdet som infogas i en av dem måste finnas i den andra tabellens kolumn, bör du använda en främmande nyckelbegränsning på den gemensamma kolumnen.

I det här fallet, när du försöker infoga ett värde i den kolumnen, kommer den främmande nyckeln att säkerställa att det infogade värdet finns i den refererade tabellens kolumn.

Observera att begränsningar för främmande nycklar inte är aktiverade som standard i SQLiteDu måste först aktivera dem genom att köra följande kommando:

PRAGMA foreign_keys = ON;

Utländska nyckelbegränsningar infördes i SQLite från och med version 3.6.19.

Exempel på SQLite Främmande nyckel

Anta att vi har två tabeller: Studenter och Avdelningar.

Tabellen Studenter innehåller en lista över studenter och tabellen Avdelningar innehåller en lista över avdelningarna. Varje student tillhör en avdelning; det vill säga, varje student har en kolumn för avdelnings-ID.

Nu ska vi se hur begränsningen av främmande nyckel kan vara till hjälp för att säkerställa att värdet för avdelnings-ID:t i tabellen Studenter måste finnas i tabellen Avdelningar.

Så, om vi skapar en främmande nyckelbegränsning på DepartmentId i Students-tabellen, måste varje infogat departmentId finnas i Departments-tabellen.

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

För att kontrollera hur begränsningar av främmande nyckelringar kan förhindra att ett odefinierat element eller värde infogas i en tabell som har en relation till en annan tabell, ska vi titta på följande exempel.

I det här exemplet har tabellen Departments en främmande nyckelrelation till tabellen Students, så alla departmentId-värden som infogas i tabellen Students måste finnas i tabellen Departments. Om du försöker infoga ett departmentId-värde som inte finns i tabellen Departments, kommer främmande nyckelbegränsningen att hindra dig från att göra det.

Låt oss infoga två avdelningar, "IT" och "Konst", i tabellen Avdelningar med följande INSERT-frågor:

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

De två satserna ska infoga två avdelningar i tabellen Avdelningar. Du kan bekräfta att de två värdena infogades genom att köra frågan "SELECT * FROM Avdelningar" efteråt:

Försök sedan att infoga en ny student med ett avdelnings-ID som inte finns i tabellen Avdelningar:

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

Raden kommer inte att infogas, och du får ett felmeddelande som säger: FOREIGN KEY-begränsningen misslyckades.

Skillnaden mellan primärnyckel och utländsk nyckel i SQLite

Primärnycklar och främmande nycklar hjälper båda till att upprätthålla dataintegritet, men de spelar olika roller. En primärnyckel identifierar rader inom en enda tabell, medan en främmande nyckel länkar rader mellan två relaterade tabeller. Tabellen nedan sammanfattar de viktigaste skillnaderna.

I exemplet Studenter och Avdelningar är DepartmentId primärnyckeln i tabellen Avdelningar och en främmande nyckel i tabellen Studenter, vilket är det som kopplar varje student till en giltig avdelning.

SQLite Sammansatt primärnyckel

En sammansatt primärnyckel är en primärnyckel som består av två eller flera kolumner. Den används när ingen enskild kolumn är unik i sig, men kombinationen av kolumner är unik för varje rad. SQLite behandlar de kombinerade värdena som en nyckel.

Till exempel kan en inskrivningstabell tillåta samma student i många kurser och samma kurs för många studenter, men varje student-kurs-par bör bara förekomma en gång:

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

Här är varken StudentId eller CourseId unika ensamma, utan paret (StudentId, CourseId) är unikt, så samma student kan inte vara inskriven i samma kurs två gånger. Observera följande punkter när du använder en sammansatt nyckel:

• Använd en sammansatt nyckel när en enskild kolumn inte kan identifiera en rad unikt.
• Varje kolumn i den sammansatta nyckeln följer primärnyckelreglerna, så det kombinerade värdet måste vara unikt och inte null.
• En sammansatt nyckel skrivs som en separat PRIMARY KEY-klausul på tabellnivå, inte inuti en enda kolumndefinition.

SQLite Främmande nyckelåtgärder: VID RADERA och VID UPPDATERING

En främmande nyckel kan också styra vad som händer med underordnade rader när den överordnade raden de refererar till tas bort eller uppdateras. Dessa referensåtgärder läggs till med ON DELETE- och ON UPDATE-klausulerna när du definierar den främmande nyckeln. SQLite stöder fem åtgärder:

• INGEN ÅTGÄRD — standardåtgärden, som genererar ett fel om underordnade rader fortfarande refererar till den överordnade.
• BEGRÄNSA — förhindrar borttagning eller uppdatering omedelbart, innan någon annan ändring körs.
• SÄTT NULL — ställer in den underordnade främmande nyckelkolumnen till null.
• SÄTTA SOM NORMALT — ställer in den underordnade främmande nyckelkolumnen till dess deklarerade standardvärde.
• KASKAD — tillämpar samma ändring på underraderna, så att om man tar bort en förälder raderar man även dess underrad.

Exemplet nedan återskapar tabellen Studenter så att om man tar bort en institution automatiskt raderas dess studenter, och om man uppdaterar ett institutions-ID uppdateras matchande studenter:

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

Kom ihåg att referensåtgärder bara körs när stöd för främmande nycklar är aktiverat, så kör PRAGMA foreign_keys = ON i början av varje anslutning. Utan det, SQLite analyserar ON DELETE- och ON UPDATE-klausulerna men framtvingar dem inte.

Vanliga frågor