Esmane võti ja võõrvõti sees SQLite koos näidetega

Allolevad osad selgitavad SQLite piirangud üksikasjalikult, alustades primaarvõtmest ja võõrvõtmest, mis defineerivad ja ühendavad tabeleid, ning hõlmates reegleid NOT NULL, DEFAULT, UNIQUE ja CHECK, mis valideerivad iga veeru andmeid.

SQLite Piirangud

Veergude piirangud jõustavad veergu sisestatud väärtustele reegleid andmete valideerimiseks. Need piirangud määratletakse tabeli loomisel veeru definitsiooni sees. Need hoiavad salvestatud andmed järjepideva ja täpsena, lükates tagasi väärtused, mis rikuvad teie seatud reegleid, näiteks duplikaadid, tühiväärtused või väärtused, mida seotud tabelis ei eksisteeri.

SQLite Esmane võti

Kõik primaarvõtme veeru väärtused peavad olema unikaalsed ja mitte tühised. Primaarvõti identifitseerib unikaalselt iga tabeli rea.

Primaarvõtit saab rakendada ainult ühele veerule või veergude kombinatsioonile. Viimasel juhul peaks veergude väärtuste kombinatsioon olema kõigi tabeli ridade jaoks unikaalne.

süntaksit:

Tabeli primaarvõtme määratlemiseks on mitu erinevat viisi:

Veeru määratluses:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Eraldi määratlusena:

PRIMARY KEY(ColumnName);

Veergude kombinatsiooni loomiseks esmase võtmena toimige järgmiselt.

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Piirangud NOT NULL, DEFAULT, UNIQUE ja CHECK

Lisaks primaarvõtmele SQLite pakub mitmeid veerupiiranguid, mis valideerivad tabelisse sisestatud väärtusi. Piirangud NOT NULL, DEFAULT, UNIQUE ja CHECK on kõik määratletud veeru definitsioonis ja igaüks neist jõustab veeru väärtustele kindla reegli. andmetüüp ja väärtused.

NOT NULL piirang

. SQLite NOT NULL piirang takistab veerul nullväärtuse omamist:

ColumnName INTEGER  NOT NULL;

DEFAULT Piirang

Koos SQLite VAIKIMISI piirang, kui te veergu väärtust ei lisa, lisatakse selle asemel vaikeväärtus.

Näiteks:

ColumnName INTEGER DEFAULT 0;

Kui kirjutad sisestuslause ja ei määra sellele veerule väärtust, saab veeru väärtus 0.

AINULAADNE piirang

. SQLite UNIQUE piirang hoiab ära topeltväärtuste esinemise veeru kõigi väärtuste hulgas.

Näiteks:

EmployeeId INTEGER NOT NULL UNIQUE;

See jõustab väärtuse „EmployeeId” unikaalseks; dubleeritud väärtused pole lubatud. Pange tähele, et see kehtib ainult veeru „EmployeeId” väärtuste kohta.

KONTROLLI piirang

. SQLite Piirang CHECK seab tingimuse sisestatud väärtuse kontrollimiseks. Kui väärtus tingimusele ei vasta, siis seda ei sisestata.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Veergu „Kogus“ ei saa sisestada väärtust, mis on väiksem kui 10.

SQLite võõrvõti

. SQLite Võõrvõti on piirang, mis kontrollib ühes tabelis oleva väärtuse olemasolu teises tabelis, millel on seos esimese tabeliga, kus võõrvõti on määratletud.

Mitme tabeliga töötades esineb juhtumeid, kus kaks tabelit on omavahel seotud ühe ühise veeru kaudu. Kui soovite tagada, et ühte tabelisse sisestatud väärtus peab eksisteerima ka teise tabeli veerus, peaksite ühise veeru jaoks kasutama võõrvõtme piirangut.

Sellisel juhul, kui proovite sellesse veergu väärtust sisestada, tagab võõrvõti, et sisestatud väärtus on viidatud tabeli veerus olemas.

Pange tähele, et võõrvõtme piirangud pole vaikimisi lubatud SQLitePeate need kõigepealt lubama, käivitades järgmise käsu:

PRAGMA foreign_keys = ON;

Võõrvõtme piirangud võeti kasutusele aastal SQLite alates versioonist 3.6.19.

Näide SQLite võõrvõti

Oletame, et meil on kaks tabelit: Õpilased ja Osakonnad.

Õpilaste tabelis on õpilaste loend ja osakondade tabelis on osakondade loend. Iga õpilane kuulub osakonda; see tähendab, et igal õpilasel on osakonna ID veerg.

Nüüd näeme, kuidas võõrvõtme piirang saab olla abiks tagamaks, et õpilaste tabelis olev osakonna ID väärtus peab eksisteerima ka osakondade tabelis.

Seega, kui loome õpilaste tabelis osakonna ID-le võõrvõtme piirangu, peab iga sisestatud osakonna ID olema osakondade tabelis.

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

Et kontrollida, kuidas võõrvõtme piirangud saavad takistada määratlemata elemendi või väärtuse lisamist tabelisse, millel on seos teise tabeliga, uurime järgmist näidet.

Selles näites on osakondade tabelil võõrvõtme seos õpilaste tabeliga, seega peab iga õpilaste tabelisse sisestatud osakonnaId väärtus eksisteerima ka osakondade tabelis. Kui proovite sisestada osakonnaId väärtust, mida osakondade tabelis ei eksisteeri, takistab võõrvõtme piirang teil seda teha.

Sisestame osakondade tabelisse kaks osakonda, „IT“ ja „Kunstid“, järgmisega INSERT-päringud:

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

Need kaks lauset peaksid lisama osakondade tabelisse kaks osakonda. Saate kinnitada, et kaks väärtust lisati, käivitades seejärel päringu „SELECT * FROM Departments”:

Seejärel proovige sisestada uus üliõpilane, kelle osakonna ID-d (departmentId) tabelis Departments (osakonnad) ei eksisteeri:

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

Rida ei lisata ja kuvatakse veateade: VÄISKÕTME piirangu täitmine ebaõnnestus.

Erinevus primaarvõtme ja võõrvõtme vahel SQLite

Primaarvõtmed ja võõrvõtmed aitavad mõlemad säilitada andmete terviklikkust, kuid neil on erinevad rollid. Primaarvõti tuvastab ühe tabeli read, võõrvõti aga seob ridu kahes seotud tabelis. Allolev tabel võtab kokku peamised erinevused.

Õpilaste ja osakondade näites on OsakonnaId Osakondade tabeli primaarvõti ja Õpilaste tabeli võõrvõti, mis seob iga õpilase kehtiva osakonnaga.

SQLite Liitprimaarvõti

Liitprimaarvõti on primaarvõti, mis koosneb kahest või enamast veerust. Seda kasutatakse juhul, kui ükski veerg pole iseenesest unikaalne, kuid veergude kombinatsioon on iga rea ​​jaoks unikaalne. SQLite käsitleb kombineeritud väärtusi ühe võtmena.

Näiteks võib registreerimistabel lubada sama üliõpilast paljudes kursustes ja sama kursust paljude üliõpilaste jaoks, kuid iga üliõpilase-kursuse paar peaks esinema ainult üks kord:

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

Siin ei ole ei StudentId ega CourseId eraldi unikaalsed, kuid paar (StudentId, CourseId) on unikaalne, seega ei saa sama üliõpilane olla samale kursusele kaks korda registreeritud. Liitvõtme kasutamisel pange tähele järgmist:

• Kasutage liitvõtit, kui üks veerg ei saa rida unikaalselt tuvastada.
• Iga liitvõtme veerg järgib primaarvõtme reegleid, seega peab kombineeritud väärtus olema unikaalne ja mitte tühi.
• Liitvõti kirjutatakse eraldi tabeli tasemel PRIMARY KEY klauslina, mitte ühe veeru definitsiooni sees.

SQLite Välisvõtme toimingud: KUSTUTAMISEL ja UUENDAMISEL

Võõrvõti saab kontrollida ka seda, mis juhtub tütarridadega, kui nende viidatud ülemrida kustutatakse või värskendatakse. Need viitamistoimingud lisatakse ON DELETE ja ON UPDATE klauslitega võõrvõtme defineerimisel. SQLite toetab viit tegevust:

• MITTE TEGEVUST — vaiketoiming, mis tekitab vea, kui lapse read viitavad endiselt ülemreale.
• PIIRATA — takistab kustutamist või värskendamist kohe enne mis tahes muude muudatuste käivitamist.
• SET NULL — määrab lapse võõrvõtme veeru väärtuseks null.
• MÄÄRA VAIKIMISI — määrab lapse võõrvõtme veeru selle deklareeritud vaikeväärtusele.
• KASKAD — rakendab sama muudatuse alamridade jaoks, seega vanema kustutamine kustutab ka selle alamridad.

Allolev näide loob õpilaste tabeli uuesti nii, et osakonna kustutamisel kustutatakse automaatselt ka selle õpilased ning osakonna ID värskendamisel värskendatakse ka vastavaid õpilasi:

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

Pea meeles, et viitamistoimingud käivituvad ainult siis, kui võõrvõtme tugi on sisse lülitatud, seega käivita iga ühenduse alguses PRAGMA foreign_keys = ON. Ilma selleta SQLite parsib klausleid ON DELETE ja ON UPDATE, aga ei jõusta neid.

KKK