Primaire sleutel en buitenlandse sleutel invoeren SQLite met voorbeelden

De onderstaande secties leggen uit SQLite De beperkingen worden in detail besproken, te beginnen met de PRIMARY KEY en FOREIGN KEY die tabellen definiëren en verbinden, en vervolgens de NOT NULL-, DEFAULT-, UNIQUE- en CHECK-regels die de gegevens in elke kolom valideren.

SQLite beperkingen

Kolombeperkingen leggen regels op aan de waarden die in een kolom worden ingevoerd om de gegevens te valideren. Deze beperkingen worden gedefinieerd bij het aanmaken van een tabel, binnen de kolomdefinitie. Ze zorgen ervoor dat de opgeslagen gegevens consistent en nauwkeurig blijven door waarden te weigeren die de ingestelde regels overtreden, zoals duplicaten, null-waarden of waarden die niet in een gerelateerde tabel voorkomen.

SQLite Hoofdsleutel

Alle waarden in een primaire sleutelkolom moeten uniek zijn en mogen niet null zijn. De primaire sleutel identificeert elke rij in de tabel op unieke wijze.

De primaire sleutel kan worden toegepast op slechts één kolom of op een combinatie van kolommen. In het laatste geval moet de combinatie van de kolomwaarden uniek zijn voor alle rijen van de tabel.

Syntax:

Er zijn verschillende manieren om een ​​primaire sleutel in een tabel te definiëren:

In de kolomdefinitie zelf:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Als aparte definitie:

PRIMARY KEY(ColumnName);

Om een ​​combinatie van kolommen als primaire sleutel te maken:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite NOT NULL-, DEFAULT-, UNIQUE- en CHECK-beperkingen

Naast de primaire sleutel, SQLite Biedt verschillende kolombeperkingen die de in een tabel ingevoerde waarden valideren. De beperkingen NOT NULL, DEFAULT, UNIQUE en CHECK worden elk gedefinieerd in de kolomdefinitie en elk ervan legt een specifieke regel op aan de kolom. data type en waarden.

NIET NULL-beperking

De SQLite De NOT NULL-beperking voorkomt dat een kolom een ​​null-waarde kan bevatten:

ColumnName INTEGER  NOT NULL;

STANDAARD Beperking

Met de SQLite De DEFAULT-beperking zorgt ervoor dat als u geen waarde in een kolom invoert, de standaardwaarde wordt ingevoegd.

Bijvoorbeeld:

ColumnName INTEGER DEFAULT 0;

Als je een INSERT-instructie schrijft en geen waarde voor die kolom opgeeft, krijgt de kolom de waarde 0.

UNIEKE beperking

De SQLite De UNIQUE-beperking voorkomt dat er dubbele waarden voorkomen in de kolom.

Bijvoorbeeld:

EmployeeId INTEGER NOT NULL UNIQUE;

Dit zorgt ervoor dat de waarde van "EmployeeId" uniek is; dubbele waarden zijn niet toegestaan. Houd er rekening mee dat dit alleen van toepassing is op de waarden in de kolom "EmployeeId".

CONTROLEER Beperking

De SQLite De CHECK-beperking stelt een voorwaarde in om een ​​in te voeren waarde te controleren. Als de waarde niet aan de voorwaarde voldoet, wordt deze niet ingevoegd.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Je kunt in de kolom 'Hoeveelheid' geen waarde kleiner dan 10 invoeren.

SQLite Vreemde sleutel

De SQLite Een foreign key is een beperking die controleert of een waarde die in de ene tabel voorkomt, ook bestaat in een andere tabel die een relatie heeft met de eerste tabel waarin de foreign key is gedefinieerd.

Bij het werken met meerdere tabellen zijn er gevallen waarin twee tabellen via één gemeenschappelijke kolom aan elkaar zijn gekoppeld. Als u wilt garanderen dat de waarde die in de ene tabel wordt ingevoerd ook in de kolom van de andere tabel voorkomt, dan moet u een foreign key-beperking op die gemeenschappelijke kolom toepassen.

In dit geval zorgt de externe sleutel ervoor dat, wanneer u een waarde in die kolom probeert in te voegen, de ingevoegde waarde ook daadwerkelijk in de kolom van de betreffende tabel bestaat.

Houd er rekening mee dat beperkingen met betrekking tot externe sleutels niet standaard zijn ingeschakeld in SQLiteJe moet ze eerst inschakelen door de volgende opdracht uit te voeren:

PRAGMA foreign_keys = ON;

Er zijn externe sleutelbeperkingen geïntroduceerd SQLite vanaf versie 3.6.19.

Voorbeeld SQLite Vreemde sleutel

Stel dat we twee tabellen hebben: Studenten en Afdelingen.

De tabel 'Studenten' bevat een lijst met studenten en de tabel 'Afdelingen' bevat een lijst met afdelingen. Elke student behoort tot een afdeling; dat wil zeggen dat elke student een kolom 'afdelings-ID' heeft.

Nu zullen we zien hoe de foreign key-beperking kan helpen om ervoor te zorgen dat de waarde van de afdelings-ID in de tabel Studenten ook in de tabel Afdelingen voorkomt.

Als we dus een foreign key-beperking op de DepartmentId in de Students-tabel instellen, moet elke ingevoegde departmentId ook in de Departments-tabel aanwezig zijn.

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

Om te controleren hoe foreign key-beperkingen kunnen voorkomen dat een ongedefinieerd element of een ongedefinieerde waarde wordt ingevoegd in een tabel die een relatie heeft met een andere tabel, zullen we het volgende voorbeeld bekijken.

In dit voorbeeld heeft de tabel 'Afdelingen' een externe sleutelrelatie met de tabel 'Studenten'. Dit betekent dat elke waarde voor 'afdelings-ID' die in de tabel 'Studenten' wordt ingevoerd, ook in de tabel 'Afdelingen' moet voorkomen. Als u probeert een waarde voor 'afdelings-ID' in te voeren die niet in de tabel 'Afdelingen' voorkomt, zal de externe sleutelbeperking dit verhinderen.

Laten we twee afdelingen, "IT" en "Kunst", toevoegen aan de tabel Afdelingen met de volgende gegevens: INSERT-query's:

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

De twee instructies zouden twee afdelingen in de tabel Afdelingen moeten invoegen. U kunt controleren of de twee waarden zijn ingevoegd door daarna de query "SELECT * FROM Afdelingen" uit te voeren:

Probeer vervolgens een nieuwe student in te voegen met een departmentId die niet in de tabel Departments voorkomt:

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

De rij wordt niet ingevoegd en u krijgt een foutmelding met de tekst: FOREIGN KEY-beperking mislukt.

Verschil tussen primaire sleutel en externe sleutel in SQLite

Primaire sleutels en externe sleutels dragen beide bij aan de integriteit van gegevens, maar ze spelen verschillende rollen. Een primaire sleutel identificeert rijen binnen één tabel, terwijl een externe sleutel rijen in twee gerelateerde tabellen met elkaar verbindt. De onderstaande tabel vat de belangrijkste verschillen samen.

In het voorbeeld met studenten en afdelingen is DepartmentId de primaire sleutel van de tabel Afdelingen en een externe sleutel in de tabel Studenten, waarmee elke student aan een geldige afdeling wordt gekoppeld.

SQLite Samengestelde primaire sleutel

Een samengestelde primaire sleutel is een primaire sleutel die bestaat uit twee of meer kolommen. Deze wordt gebruikt wanneer geen enkele kolom op zichzelf uniek is, maar de combinatie van kolommen wel uniek is voor elke rij. SQLite behandelt de gecombineerde waarden als één sleutel.

Een inschrijvingstabel kan bijvoorbeeld toestaan ​​dat dezelfde student zich voor meerdere cursussen inschrijft en dat meerdere studenten dezelfde cursus volgen, maar elk student-cursuspaar mag slechts één keer voorkomen:

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

Hier is noch StudentId noch CourseId op zichzelf uniek, maar het paar (StudentId, CourseId) is wel uniek, waardoor dezelfde student niet twee keer voor dezelfde cursus kan worden ingeschreven. Houd rekening met de volgende punten wanneer u een samengestelde sleutel gebruikt:

• Gebruik een samengestelde sleutel wanneer een enkele kolom een ​​rij niet uniek kan identificeren.
• Elke kolom in de samengestelde sleutel voldoet aan de regels van de primaire sleutel, dus de gecombineerde waarde moet uniek zijn en mag niet null zijn.
• Een samengestelde sleutel wordt geschreven als een aparte PRIMARY KEY-clausule op tabelniveau, niet binnen één enkele kolomdefinitie.

SQLite Acties van externe sleutels: ON DELETE en ON UPDATE

Een externe sleutel kan ook bepalen wat er met onderliggende rijen gebeurt wanneer de bovenliggende rij waarnaar ze verwijzen, wordt verwijderd of bijgewerkt. Deze referentiële acties worden toegevoegd met de clausules ON DELETE en ON UPDATE wanneer u de externe sleutel definieert. SQLite ondersteunt vijf acties:

• GEEN ACTIE — de standaardactie, die een foutmelding geeft als onderliggende rijen nog steeds naar de bovenliggende rij verwijzen.
• BEPERKEN — voorkomt dat de verwijdering of update direct plaatsvindt, voordat andere wijzigingen worden uitgevoerd.
• NUL INSTELLEN — stelt de onderliggende foreign key-kolom in op null.
• STANDAARD INSTELLEN — stelt de onderliggende foreign key-kolom in op de gedeclareerde standaardwaarde.
• CASCADE — Deze wijziging wordt ook toegepast op de onderliggende rijen, dus het verwijderen van een ouderrij verwijdert ook de onderliggende rijen.

Het onderstaande voorbeeld maakt de tabel 'Studenten' opnieuw aan, zodat het verwijderen van een afdeling automatisch de bijbehorende studenten verwijdert en het bijwerken van een afdelings-ID de corresponderende studenten bijwerkt:

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

Houd er rekening mee dat referentiële acties alleen worden uitgevoerd wanneer ondersteuning voor foreign keys is ingeschakeld. Voer daarom PRAGMA foreign_keys = ON uit aan het begin van elke verbinding. Zonder deze instelling werkt het niet. SQLite Het programma analyseert de ON DELETE- en ON UPDATE-clausules, maar voert ze niet uit.

Veelgestelde vragen