Ensisijainen avain ja vierasavain sisään SQLite esimerkkien kanssa

Alla olevat osiot selittävät SQLite rajoitukset yksityiskohtaisesti, alkaen ensisijaisesta avaimesta ja viiteavaimesta, jotka määrittelevät ja yhdistävät taulukot, ja kattaen NOT NULL-, DEFAULT-, UNIQUE- ja CHECK-säännöt, jotka validoivat kunkin sarakkeen tiedot.

SQLite rajoitteet

Sarakerajoitteet valvovat sarakkeeseen lisättyjen arvojen sääntöjä tietojen validoimiseksi. Nämä rajoitukset määritellään taulukkoa luotaessa sarakemäärityksen sisällä. Ne pitävät tallennetut tiedot yhdenmukaisina ja tarkkoina hylkäämällä arvot, jotka rikkovat asettamiasi sääntöjä, kuten kaksoiskappaleet, null-arvot tai arvot, joita ei ole liittyvässä taulukossa.

SQLite Pääavain

Kaikkien ensisijaisen avaimen sarakkeen arvojen on oltava yksilöllisiä eivätkä null-arvoja. Ensisijainen avain yksilöi yksilöllisesti jokaisen rivin taulukossa.

Ensisijaista avainta voidaan soveltaa vain yhteen sarakkeeseen tai sarakeyhdistelmään. Jälkimmäisessä tapauksessa sarakkeiden arvojen yhdistelmän tulee olla ainutlaatuinen kaikille taulukon riveille.

Syntaksi:

Taulukon ensisijaisen avaimen määrittämiseen on useita eri tapoja:

Itse sarakkeen määritelmässä:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Erillisenä määritelmänä:

PRIMARY KEY(ColumnName);

Sarakkeiden yhdistelmän luominen ensisijaiseksi avaimeksi:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite NOT NULL-, DEFAULT-, UNIQUE- ja CHECK-rajoitteet

Pääavaimen lisäksi SQLite tarjoaa useita sarakerajoitteita, jotka tarkistavat taulukkoon syötetyt arvot. NOT NULL-, DEFAULT-, UNIQUE- ja CHECK-rajoitteet on kukin määritelty sarakemääritelmässä, ja jokainen niistä valvoo tiettyä sääntöä sarakkeen arvoissa. tietotyyppi ja arvot.

NOT NULL -rajoite

SQLite NOT NULL -rajoite estää sarakkeen tyhjäarvon:

ColumnName INTEGER  NOT NULL;

OLETUSrajoitus

Kanssa SQLite OLETUSrajoite, jos et lisää sarakkeeseen arvoa, sen sijaan lisätään oletusarvo.

Esimerkiksi:

ColumnName INTEGER DEFAULT 0;

Jos kirjoitat lisäyslausekkeen etkä määritä sarakkeelle mitään arvoa, sarakkeen arvoksi tulee 0.

AINUTLAATUINEN rajoite

SQLite UNIQUE-rajoite estää kaksoisarvot sarakkeen kaikkien arvojen joukossa.

Esimerkiksi:

EmployeeId INTEGER NOT NULL UNIQUE;

Tämä pakottaa ”EmployeeId”-arvon olemaan yksilöllinen; päällekkäisiä arvoja ei sallita. Huomaa, että tämä koskee vain ”EmployeeId”-sarakkeen arvoja.

TARKISTA Rajoitus

SQLite TARKISTUS-rajoite asettaa ehdon lisätyn arvon tarkistamiseksi. Jos arvo ei vastaa ehtoa, sitä ei lisätä.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Et voi syöttää ”Määrä”-sarakkeeseen arvoa, joka on pienempi kuin 10.

SQLite viiteavain

SQLite Viiteavain on rajoite, joka varmistaa yhden taulukon arvon olemassaolon toisessa taulukossa, jolla on suhde ensimmäiseen taulukkoon, jossa viiteavain on määritelty.

Useiden taulukoiden kanssa työskenneltäessä on tapauksia, joissa kaksi taulukkoa liittyvät toisiinsa yhteisen sarakkeen kautta. Jos haluat varmistaa, että toiseen taulukkoon lisätty arvo esiintyy toisen taulukon sarakkeessa, sinun tulee käyttää viiteavainrajoitetta yhteiselle sarakkeelle.

Tässä tapauksessa, kun yrität lisätä arvon kyseiseen sarakkeeseen, viiteavain varmistaa, että lisätty arvo on olemassa viitatun taulukon sarakkeessa.

Huomaa, että viiteavaimen rajoitukset eivät ole oletusarvoisesti käytössä SQLiteSinun on ensin otettava ne käyttöön suorittamalla seuraava komento:

PRAGMA foreign_keys = ON;

Vieraiden avainten rajoitukset otettiin käyttöön SQLite versiosta 3.6.19 alkaen.

Esimerkki SQLite viiteavain

Oletetaan, että meillä on kaksi taulukkoa: Opiskelijat ja Osastot.

Opiskelijat-taulukossa on luettelo opiskelijoista ja Osastot-taulukossa on luettelo osastoista. Jokainen opiskelija kuuluu osastoon eli jokaisella opiskelijalla on osaston tunnussarake.

Nyt näemme, miten viiteavaimen rajoite voi olla hyödyllinen sen varmistamiseksi, että Opiskelijat-taulukon osastotunnuksen arvon on oltava olemassa Osastot-taulukossa.

Jos siis luomme viiteavaimen rajoitteen Opiskelijat-taulukon Osastotunnukselle, jokaisen lisätyn osastotunnuksen on oltava läsnä Osastot-taulukossa.

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

Seuraavassa esimerkissä tarkastellaan, miten viiteavaimen rajoitteet voivat estää määrittelemättömän elementin tai arvon lisäämisen taulukkoon, jolla on relaatio toiseen taulukkoon.

Tässä esimerkissä Osastot-taulukolla on viiteavainsuhde Opiskelijat-taulukkoon, joten kaikki Opiskelijat-taulukkoon lisätyt departmentId-arvot on oltava olemassa Osastot-taulukossa. Jos yrität lisätä departmentId-arvon, jota ei ole Osastot-taulukossa, viiteavainrajoite estää sinua tekemästä sitä.

Lisätään kaksi osastoa, ”IT” ja ”Taiteet”, Osastot-taulukkoon seuraavasti INSERT-kyselyt:

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

Näiden kahden lausekkeen pitäisi lisätä kaksi osastoa Osastot-taulukkoon. Voit varmistaa, että kaksi arvoa on lisätty, suorittamalla jälkeenpäin kyselyn ”SELECT * FROM Osastot”:

Yritä sitten lisätä uusi opiskelija, jonka osastotunnusta ei ole Osastot-taulukossa:

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

Riviä ei lisätä, ja saat virheilmoituksen: FOREIGN AVAIN -rajoite epäonnistui.

Ero ensisijaisen avaimen ja viiteavaimen välillä SQLite

Sekä ensisijaiset että viiteavaimet auttavat ylläpitämään tietojen eheyttä, mutta niillä on eri roolit. Ensisijainen avain tunnistaa rivit yhden taulukon sisällä, kun taas viiteavain yhdistää rivit kahden toisiinsa liittyvän taulukon välillä. Alla oleva taulukko esittää yhteenvedon tärkeimmistä eroista.

Opiskelijat- ja Osastot-esimerkissä OsastonId on Osastot-taulukon ensisijainen avain ja Opiskelijat-taulukon viiteavain, joka sitoo jokaisen opiskelijan kelvolliseen osastoon.

SQLite Yhdistetty ensisijainen avain

Yhdistetty ensisijainen avain on kahdesta tai useammasta sarakkeesta koostuva ensisijainen avain. Sitä käytetään, kun mikään yksittäinen sarake ei ole ainutlaatuinen, mutta sarakeyhdistelmä on ainutlaatuinen jokaiselle riville. SQLite käsittelee yhdistettyjä arvoja yhtenä avaimena.

Esimerkiksi ilmoittautumistaulukko voi sallia saman opiskelijan useilla kursseilla ja saman kurssin useille opiskelijoille, mutta jokainen opiskelija-kurssipari tulisi esiintyä vain kerran:

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

Tässä kumpikaan, StudentId tai CourseId, ei ole yksilöllinen yksinään, mutta pari (StudentId, CourseId) on yksilöllinen, joten samaa opiskelijaa ei voi ilmoittautua samalle kurssille kahdesti. Huomioi seuraavat seikat käyttäessäsi yhdistelmäavainta:

• Käytä yhdistelmäavainta, kun yksi sarake ei voi yksilöidä riviä.
• Jokainen yhdistelmäavaimen sarake noudattaa ensisijaisen avaimen sääntöjä, joten yhdistetyn arvon on oltava yksilöllinen eikä tyhjä.
• Yhdistelmäavain kirjoitetaan erillisenä taulukkotason PRIMARY KEY -lauseena, ei yksittäisen sarakemääritelmän sisällä.

SQLite Viiteavaimen toiminnot: POISTON YHTEYDESSÄ ja PÄIVITYKSEN YHTEYDESSÄ

Viiteavain voi myös hallita sitä, mitä alisäriveille tapahtuu, kun niiden viittaama päärivi poistetaan tai päivitetään. Nämä viittaavat toiminnot lisätään ON DELETE- ja ON UPDATE -lausekkeilla, kun määrität viiteavaimen. SQLite tukee viittä toimenpidettä:

• EI TOIMENPITEITÄ — oletusarvoinen toiminto, joka aiheuttaa virheen, jos lapsirivit viittaavat edelleen pääriviin.
• RAJOITTAA — estää poiston tai päivityksen välittömästi ennen muiden muutosten suorittamista.
• SET NULL — asettaa lapsen viiteavainsarakkeen arvoksi null.
• ASETA OLETUS — asettaa lapsen viiteavainsarakkeen ilmoitettuun oletusarvoonsa.
• RYÖPYTÄ — soveltaa samaa muutosta alisiireihin, joten pääobjektin poistaminen poistaa myös sen alisiiret.

Alla olevassa esimerkissä Opiskelijat-taulukko luodaan uudelleen siten, että osaston poistaminen poistaa automaattisesti sen opiskelijat, ja osastotunnuksen päivittäminen päivittää vastaavat opiskelijat:

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

Muista, että viittaustoiminnot suoritetaan vain, kun viiteavaimen tuki on käytössä, joten suorita PRAGMA foreign_keys = ON jokaisen yhteyden alussa. Ilman sitä, SQLite jäsentää ON DELETE- ja ON UPDATE -lausekkeet, mutta ei valvo niitä.

UKK