Primærnøkkel og fremmednøkkel inn SQLite med eksempler

Avsnittene nedenfor forklarer SQLite begrensninger i detalj, startende med PRIMARY KEY og FOREIGN KEY som definerer og kobler sammen tabeller, og som dekker NOT NULL-, DEFAULT-, UNIQUE- og CHECK-reglene som validerer dataene i hver kolonne.

SQLite begrensninger

Kolonnebegrensninger håndhever regler for verdiene som settes inn i en kolonne for å validere dataene. Disse begrensningene defineres når du oppretter en tabell, inne i kolonnedefinisjonen. De holder de lagrede dataene konsistente og nøyaktige ved å avvise verdier som bryter reglene du angir, for eksempel duplikater, nullverdier eller verdier som ikke finnes i en relatert tabell.

SQLite Primærnøkkel

Alle verdiene i en primærnøkkelkolonne må være unike og ikke null. Primærnøkkelen identifiserer hver rad i tabellen unikt.

Primærnøkkelen kan brukes på bare én kolonne eller på en kombinasjon av kolonner. I sistnevnte tilfelle bør kombinasjonen av kolonneverdiene være unik for alle tabellens rader.

Syntaks:

Det finnes flere forskjellige måter å definere en primærnøkkel på en tabell:

I selve kolonnedefinisjonen:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Som en egen definisjon:

PRIMARY KEY(ColumnName);

Slik oppretter du en kombinasjon av kolonner som en primærnøkkel:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite IKKE NULL-, STANDARD-, UNIK- og SJEKK-begrensninger

Foruten primærnøkkelen, SQLite gir flere kolonnebegrensninger som validerer verdiene som er lagt inn i en tabell. Begrensningene NOT NULL, DEFAULT, UNIQUE og CHECK er hver definert i kolonnedefinisjonen, og hver av dem håndhever en spesifikk regel på kolonnens data-type og verdier.

IKKE NULL-begrensning

Ocuco SQLite NOT NULL-begrensningen hindrer en kolonne i å ha en nullverdi:

ColumnName INTEGER  NOT NULL;

STANDARD Begrensning

Med SQLite STANDARDbegrensning, hvis du ikke setter inn noen verdi i en kolonne, settes standardverdien inn i stedet.

For eksempel:

ColumnName INTEGER DEFAULT 0;

Hvis du skriver en insert-setning og ikke angir noen verdi for den kolonnen, vil kolonnen ha verdien 0.

UNIK begrensning

Ocuco SQLite UNIK begrensning forhindrer dupliserte verdier blant alle verdiene i kolonnen.

For eksempel:

EmployeeId INTEGER NOT NULL UNIQUE;

Dette tvinger frem at verdien «EmployeeId» skal være unik; dupliserte verdier er ikke tillatt. Merk at dette kun gjelder verdiene i kolonnen «EmployeeId».

SJEKK-begrensning

Ocuco SQLite CHECK-begrensningen setter en betingelse for å sjekke en innsatt verdi. Hvis verdien ikke samsvarer med betingelsen, vil den ikke bli satt inn.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Du kan ikke sette inn en verdi mindre enn 10 i «Antall»-kolonnen.

SQLite Foreign Key

Ocuco SQLite En fremmednøkkel er en begrensning som bekrefter eksistensen av en verdi som finnes i én tabell i en annen tabell, og som har en relasjon til den første tabellen der fremmednøkkelen er definert.

Når man arbeider med flere tabeller, finnes det tilfeller der to tabeller er relatert til hverandre gjennom én felles kolonne. Hvis du vil sikre at verdien som settes inn i en av dem må finnes i den andre tabellens kolonne, bør du bruke en fremmednøkkelbegrensning på den felles kolonnen.

I dette tilfellet, når du prøver å sette inn en verdi i den kolonnen, vil fremmednøkkelen sørge for at den innsatte verdien finnes i kolonnen til den refererte tabellen.

Merk at begrensninger for fremmednøkler ikke er aktivert som standard i SQLiteDu må først aktivere dem ved å kjøre følgende kommando:

PRAGMA foreign_keys = ON;

Utenlandske nøkkelbegrensninger ble introdusert i SQLite fra og med versjon 3.6.19.

Eksempel SQLite Foreign Key

Anta at vi har to tabeller: Studenter og Avdelinger.

Studenter-tabellen har en liste over studenter, og avdelinger-tabellen har en liste over avdelingene. Hver student tilhører en avdeling; det vil si at hver student har en avdelings-ID-kolonne.

Nå skal vi se hvordan begrensningen med fremmednøkkel kan være nyttig for å sikre at verdien til avdelings-ID-en i Studenter-tabellen må finnes i Avdelinger-tabellen.

Så, hvis vi oppretter en fremmednøkkelbegrensning på DepartmentId i Students-tabellen, må hver innsatte departmentId være tilstede 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 å sjekke hvordan fremmednøkkelbegrensninger kan forhindre at et udefinert element eller en udefinert verdi settes inn i en tabell som har en relasjon til en annen tabell, skal vi se på følgende eksempel.

I dette eksemplet har tabellen Avdelinger en fremmednøkkelrelasjon til tabellen Studenter, så enhver departmentId-verdi som settes inn i tabellen Studenter må finnes i tabellen Avdelinger. Hvis du prøver å sette inn en departmentId-verdi som ikke finnes i tabellen Avdelinger, vil fremmednøkkelbegrensningen hindre deg i å gjøre det.

La oss sette inn to avdelinger, «IT» og «Kunst», i avdelingstabellen med følgende INSERT-spørringer:

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

De to setningene skal sette inn to avdelinger i avdelingstabellen. Du kan bekrefte at de to verdiene ble satt inn ved å kjøre spørringen «SELECT * FROM Departments» etterpå:

Prøv deretter å sette inn en ny student med en avdelings-ID som ikke finnes i avdelingstabellen:

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

Raden blir ikke satt inn, og du vil få en feilmelding som sier: FOREIGN KEY-begrensning mislyktes.

Forskjellen mellom primærnøkkel og fremmednøkkel i SQLite

Primærnøkler og fremmednøkler bidrar begge til å opprettholde dataintegritet, men de spiller forskjellige roller. En primærnøkkel identifiserer rader i en enkelt tabell, mens en fremmednøkkel kobler rader på tvers av to relaterte tabeller. Tabellen nedenfor oppsummerer de viktigste forskjellene.

I eksemplet med studenter og avdelinger er avdelings-ID primærnøkkelen i avdelingstabellen og en fremmednøkkel i studentertabellen, som er det som knytter hver student til en gyldig avdeling.

SQLite Sammensatt primærnøkkel

En sammensatt primærnøkkel er en primærnøkkel laget av to eller flere kolonner. Den brukes når ingen enkelt kolonne er unik i seg selv, men kombinasjonen av kolonner er unik for hver rad. SQLite behandler de kombinerte verdiene som én nøkkel.

For eksempel kan en påmeldingstabell tillate den samme studenten i mange kurs og det samme kurset for mange studenter, men hvert student-kurs-par skal bare vises én gang:

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

Her er verken StudentId eller CourseId unikt alene, men paret (StudentId, CourseId) er unikt, så den samme studenten kan ikke være registrert i samme kurs to ganger. Merk følgende punkter når du bruker en sammensatt nøkkel:

• Bruk en sammensatt nøkkel når en enkelt kolonne ikke kan identifisere en rad entydig.
• Hver kolonne i den sammensatte nøkkelen følger primærnøkkelreglene, så den kombinerte verdien må være unik og ikke null.
• En sammensatt nøkkel skrives som en separat PRIMARY KEY-klausul på tabellnivå, ikke inne i en enkelt kolonnedefinisjon.

SQLite Fremmednøkkelhandlinger: VED SLETTING og VED OPPDATERING

En fremmednøkkel kan også kontrollere hva som skjer med underordnede rader når den overordnede raden de refererer til slettes eller oppdateres. Disse referansehandlingene legges til med ON DELETE- og ON UPDATE-klausulene når du definerer fremmednøkkelen. SQLite støtter fem handlinger:

• INGEN HANDLING — standardhandlingen, som genererer en feil hvis underrader fortsatt refererer til den overordnede.
• BEGRENSE — forhindrer sletting eller oppdatering umiddelbart, før noen annen endring kjøres.
• SET NULL — setter den underordnede fremmednøkkelkolonnen til null.
• SETT STANDARD — setter den underordnede fremmednøkkelkolonnen til den deklarerte standardverdien.
• CASCADE — bruker den samme endringen på underradene, slik at ved å slette en overordnet rad slettes også underradene.

Eksemplet nedenfor gjenskaper Student-tabellen slik at sletting av en avdeling automatisk sletter studentene, og oppdatering av en avdelings-ID oppdaterer de samsvarende studentene:

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 referansehandlinger bare kjører når støtte for fremmednøkler er aktivert, så kjør PRAGMA foreign_keys = ON i starten av hver tilkobling. Uten den, SQLite analyserer ON DELETE- og ON UPDATE-klausulene, men håndhever dem ikke.

Spørsmål og svar