Primær nøgle og fremmed nøgle ind SQLite med eksempler

Afsnittene nedenfor forklarer SQLite begrænsninger i detaljer, startende med PRIMARY KEY og FOREIGN KEY, der definerer og forbinder tabeller, og dækker NOT NULL, DEFAULT, UNIQUE og CHECK reglerne, der validerer dataene i hver kolonne.

SQLite Begrænsninger

Kolonnebegrænsninger håndhæver regler for de værdier, der indsættes i en kolonne, for at validere dataene. Disse begrænsninger defineres, når en tabel oprettes, inde i kolonnedefinitionen. De holder de gemte data konsistente og nøjagtige ved at afvise værdier, der bryder de regler, du angiver, f.eks. dubletter, nullværdier eller værdier, der ikke findes i en relateret tabel.

SQLite Primærnøgle

Alle værdier i en primærnøglekolonne skal være unikke og ikke null. Primærnøglen identificerer entydigt hver række i tabellen.

Primærnøglen kan anvendes på kun én kolonne eller på en kombination af kolonner. I sidstnævnte tilfælde skal kombinationen af ​​kolonnernes værdier være unik for alle tabellens rækker.

Syntaks:

Der er flere forskellige måder at definere en primærnøgle i en tabel:

I selve kolonnedefinitionen:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Som en separat definition:

PRIMARY KEY(ColumnName);

Sådan opretter du en kombination af kolonner som en primær nøgle:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite IKKE NULL, STANDARD, UNIK og CHECK-begrænsninger

Udover den primære nøgle, SQLite indeholder adskillige kolonnebegrænsninger, der validerer de værdier, der indtastes i en tabel. Begrænsningerne NOT NULL, DEFAULT, UNIQUE og CHECK er hver især defineret i kolonnedefinitionen, og hver af dem håndhæver en specifik regel for kolonnens datatype og værdier.

IKKE NULL-begrænsning

SQLite NOT NULL-begrænsningen forhindrer en kolonne i at have en null-værdi:

ColumnName INTEGER  NOT NULL;

STANDARD Begrænsning

Med SQLite STANDARDbegrænsning, hvis du ikke indsætter nogen værdi i en kolonne, indsættes standardværdien i stedet.

For eksempel:

ColumnName INTEGER DEFAULT 0;

Hvis du skriver en insert-sætning og ikke angiver nogen værdi for den kolonne, vil kolonnen have værdien 0.

UNIK Begrænsning

SQLite UNIQUE-begrænsningen forhindrer dubletter blandt alle værdierne i kolonnen.

For eksempel:

EmployeeId INTEGER NOT NULL UNIQUE;

Dette gør det muligt at gøre værdien "EmployeeId" unik; duplikerede værdier er ikke tilladt. Bemærk, at dette kun gælder for værdierne i kolonnen "EmployeeId".

CHECK-begrænsning

SQLite CHECK-betingelsen sætter en betingelse for at kontrollere en indsat værdi. Hvis værdien ikke matcher betingelsen, vil den ikke blive indsat.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Du kan ikke indsætte en værdi mindre end 10 i kolonnen "Mængde".

SQLite Fremmed nøgle

SQLite En fremmednøgle er en begrænsning, der verificerer eksistensen af ​​en værdi, der findes i én tabel i en anden tabel, og som har en relation til den første tabel, hvor fremmednøglen er defineret.

Når man arbejder med flere tabeller, er der tilfælde, hvor to tabeller relaterer til hinanden via én fælles kolonne. Hvis du vil sikre, at den værdi, der indsættes i en af ​​dem, skal findes i den anden tabels kolonne, skal du bruge en fremmednøglebegrænsning på den fælles kolonne.

I dette tilfælde, når du forsøger at indsætte en værdi i den kolonne, vil den fremmede nøgle sikre, at den indsatte værdi findes i den refererede tabels kolonne.

Bemærk, at begrænsninger for fremmednøgler ikke er aktiveret som standard i SQLiteDu skal først aktivere dem ved at køre følgende kommando:

PRAGMA foreign_keys = ON;

Udenlandske nøglebegrænsninger blev indført i SQLite fra version 3.6.19.

Eksempel SQLite Fremmed nøgle

Antag, at vi har to tabeller: Studerende og Afdelinger.

Tabellen Studerende indeholder en liste over studerende, og tabellen Afdelinger indeholder en liste over afdelingerne. Hver studerende tilhører en afdeling; det vil sige, at hver studerende har en afdelings-ID-kolonne.

Nu skal vi se, hvordan fremmednøglebegrænsningen kan være nyttig til at sikre, at værdien af ​​afdelings-id'et i tabellen Studenter skal findes i tabellen Afdelinger.

Så hvis vi opretter en fremmednøglebegrænsning på DepartmentId i Students-tabellen, skal hvert indsatte departmentId være til stede 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)
);

For at undersøge, hvordan fremmednøglebegrænsninger kan forhindre et udefineret element eller en udefineret værdi i at blive indsat i en tabel, der har en relation til en anden tabel, vil vi se på følgende eksempel.

I dette eksempel har tabellen Afdelinger en fremmednøgle-relation til tabellen Studerende, så enhver departmentId-værdi, der indsættes i tabellen Studerende, skal findes i tabellen Afdelinger. Hvis du forsøger at indsætte en departmentId-værdi, der ikke findes i tabellen Afdelinger, vil fremmednøglebegrænsningen forhindre dig i at gøre det.

Lad os indsætte to afdelinger, "IT" og "Kunst", i afdelingstabellen med følgende INSERT-forespørgsler:

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

De to udsagn bør indsætte to afdelinger i tabellen Afdelinger. Du kan bekræfte, at de to værdier blev indsat, ved at køre forespørgslen "SELECT * FROM Afdelinger" bagefter:

Prøv derefter at indsætte en ny studerende med et afdelings-ID, der ikke findes i afdelingstabellen:

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

Rækken vil ikke blive indsat, og du vil få en fejlmeddelelse, der siger: FOREIGN KEY-begrænsning mislykkedes.

Forskellen mellem primærnøgle og fremmednøgle i SQLite

Primære nøgler og fremmednøgler hjælper begge med at opretholde dataintegritet, men de spiller forskellige roller. En primærnøgle identificerer rækker i en enkelt tabel, mens en fremmednøgle forbinder rækker på tværs af to relaterede tabeller. Tabellen nedenfor opsummerer de vigtigste forskelle.

I eksemplet med studerende og afdelinger er DepartmentId den primære nøgle i tabellen Afdelinger og en fremmednøgle i tabellen Studerende, hvilket er det, der forbinder hver studerende med en gyldig afdeling.

SQLite Sammensat primærnøgle

En sammensat primærnøgle er en primærnøgle bestående af to eller flere kolonner. Den bruges, når ingen enkelt kolonne er unik i sig selv, men kombinationen af ​​kolonner er unik for hver række. SQLite behandler de kombinerede værdier som én nøgle.

For eksempel kan en tilmeldingstabel tillade den samme studerende i mange kurser og det samme kursus for mange studerende, men hvert studerende-kursus-par bør kun forekomme én gang:

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

Her er hverken StudentId eller CourseId unikt alene, men parret (StudentId, CourseId) er unikt, så den samme studerende kan ikke være tilmeldt det samme kursus to gange. Bemærk følgende punkter, når du bruger en sammensat nøgle:

• Brug en sammensat nøgle, når en enkelt kolonne ikke entydigt kan identificere en række.
• Hver kolonne i den sammensatte nøgle følger reglerne for primære nøgler, så den kombinerede værdi skal være unik og ikke nul.
• En sammensat nøgle skrives som en separat PRIMARY KEY-klausul på tabelniveau, ikke inde i en enkelt kolonnedefinition.

SQLite Fremmednøglehandlinger: VED SLETNING og VED OPDATERING

En fremmednøgle kan også styre, hvad der sker med underordnede rækker, når den overordnede række, de refererer til, slettes eller opdateres. Disse referentielle handlinger tilføjes med ON DELETE- og ON UPDATE-klausulerne, når du definerer fremmednøglen. SQLite understøtter fem handlinger:

• INGEN HANDLING — standardhandlingen, som udløser en fejl, hvis underordnede rækker stadig refererer til den overordnede.
• BEGRÆNSE — forhindrer sletning eller opdatering med det samme, før andre ændringer udføres.
• SÆT NULL — sætter den underordnede fremmednøglekolonne til null.
• SÆT STANDARD — indstiller den underordnede fremmednøglekolonne til dens deklarerede standardværdi.
• CASCADE — anvender den samme ændring på de underordnede rækker, så sletning af en overordnet række sletter også dens underrækker.

Eksemplet nedenfor genskaber tabellen Studerende, så sletning af en afdeling automatisk sletter dens studerende, og opdatering af et afdelings-id opdaterer de matchende studerende:

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

Husk at referentielle handlinger kun kører, når understøttelse af fremmednøgler er aktiveret, så kør PRAGMA foreign_keys = ON i starten af ​​hver forbindelse. Uden den, SQLite analyserer ON DELETE- og ON UPDATE-klausulerne, men håndhæver dem ikke.

Ofte Stillede Spørgsmål