BEGRENSNING: databaseintegritet

Databaseintegritetskontroll

En annen viktig ting å vite om databaser er CONSTRAINS. Ved hjelp av begrensninger kan du kontrollere dataendringer i tabellene dine og opprettholde deres integritet og konsistens.

Hva er datakonsistens når vi snakker om en database?

La oss ta vår nettbutikk med ansatt-, produkt- og oppgavetabeller . Vi vet allerede at det kan være oppgaver i oppgavetabellen som ikke er tilordnet noen: ansatt_ID for slike rader er NULL.

Men hva skjer hvis det er en oppføring i oppgavetabellen med en ansatt_id lik for eksempel 115? Vi har tross alt ikke en slik ansatt. Vi har ikke en ansatt med id = 115 i ansatttabellen. Samtidig ligger en lenke til en ansatt med denne IDen i oppgavetabellen. Dette er et eksempel på datainkonsekvens .

Så hvordan forener vi disse dataene? Ideelt sett ville det vært slik at SQL-serveren, med enhver dataendring, kontrollerte alle disse nyansene. Og det er en slik mulighet, den heter FOREIGN_KEY.

Hvis en kolonne i tabellen ikke bare inneholder tall, men id-rader fra en annen tabell, kan dette spesifiseres eksplisitt.

Legge til en UTENLANDSK NØKKEL

En slik nøkkel kan legges til tabellen både på opprettelsesstadiet og etter, ved å bruke ALTER TABLE. Formatet er ikke fundamentalt forskjellig. Vi vil presentere begge alternativene.

Den generelle formen for en slik nøkkel/regel er:

FOREIGN KEY (column)
  	REFERENCES table(column)

La oss legge til denne nøkkelen/regelen i oppgavetabellen for å sikre at alle ansatte_ID fra tabellen refererer til en eksisterende oppføring i ansatttabellen. Dette skriptet vil se slik ut:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Og hvis vi bestemte oss for å legge til denne regelen da vi opprettet oppgavetabellen, ville koden se slik ut:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Forresten, det er situasjoner når strengen vi referer til har en unik sammensatt nøkkel: for eksempel "Navn og fødselsår" eller "productCatogoryId and productId". Da kan UTENLANDS NØKKEL skrives slik:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

UTENLANDSK NØKKEL og endrede data

Se for deg en situasjon der vi bestemte oss for å oppdatere noen data i ansatttabellen og vår medarbeider-ID er endret. Hva vil skje med dataene i oppgavetabellen? Det er riktig, de vil bli irrelevante, og integriteten til databasen vår vil bli krenket.

For å forhindre at dette skjer, kan du be SQL Server om å endre ansatt_id for alle rader i alle tabeller som refererer til denne endrede id når id i ansatttabell endres.

Slike skript kalles OnUpdate og OnDelete . Hva gjør jeg hvis post-IDen endres, og hva gjør jeg hvis posten slettes?

Med fjerningen er ikke alt så enkelt. Hvis du har avhengige objekter representert av strenger i databasen som refererer til hverandre, er en lang rekke oppførselsscenarier mulig når du sletter ett objekt.

La oss si at vi sletter en sidebruker, noe som betyr at vi må slette all hans personlige korrespondanse. Men det er usannsynlig at vi skal fjerne alle hans offentlige kommentarer.

Eller en ansatt slutter. Det ville være rart om han sluttet og samtidig forsvant alle oppgavene som ble tildelt ham fra databasen. Men hvis de hadde blitt oppnevnt ikke av ham, ville det også blitt dårlig. Det er riktigere å legge til rette for at den ansatte kan slutte etter å ha overført alle oppgavene sine til andre personer.

Her er hvordan vi kan beskrive disse scenariene ved å bruke FOREIGN KEY. Den generelle formen for en slik nøkkel/regel er:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Hva skal jeg gjøre hvis du sletter (PÅ SLETT) eller endrer (PÅ OPPDATERING) poster? Totalt kan det være 5 alternativer for SQL-serveren til å handle i hver av disse situasjonene:

Slik kan vi endre oppgavetabellen vår:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Hva er skrevet her:

ON UPDATE CASCADE : Hvis id-nøkkelen i ansatttabellen endres, endrer du også ansatt_id i oppgavetabellen som refererer til den.

PÅ SLETTEBEGRENSNING : Hvis en rad slettes fra ansatttabellen og refereres fra oppgavetabellen, så forhindre at raden slettes fra ansatttabellen.