BEGRÆNSNING: databaseintegritet

Databaseintegritetskontrol

En anden vigtig ting at vide om databaser er CONSTRAINS. Ved hjælp af begrænsninger kan du kontrollere dataændringer i dine tabeller og bevare deres integritet og konsistens.

Hvad er datakonsistens , når vi taler om en database?

Lad os tage vores netbutik med medarbejder-, produkt- og opgavetabeller . Vi ved allerede, at der kan være opgaver i opgavetabellen, som ikke er tildelt nogen: medarbejder-id'et for sådanne rækker er NULL.

Men hvad sker der, hvis der er en post i opgavetabellen med et medarbejder_id lig med f.eks. 115? Sådan en medarbejder har vi jo ikke. Vi har ikke en medarbejder med id = 115 i medarbejdertabellen. Samtidig er et link til en medarbejder med dette id i opgavetabellen. Dette er et eksempel på datainkonsistens .

Så hvordan forener vi disse data? Ideelt set ville det være sådan, at SQL-serveren, med enhver dataændring, kontrollerede alle disse nuancer. Og der er sådan en mulighed, den hedder FOREIGN_KEY.

Hvis en kolonne i din tabel ikke kun indeholder tal, men id-rækker fra en anden tabel, så kan dette specificeres eksplicit.

Tilføjelse af en UDENLANDSKE NØGLE

En sådan nøgle kan føjes til tabellen både på oprettelsesstadiet og efter ved hjælp af ALTER TABLE. Formatet er ikke grundlæggende anderledes. Vi vil præsentere begge muligheder.

Den generelle form for en sådan nøgle/regel er:

FOREIGN KEY (column)
  	REFERENCES table(column)

Lad os tilføje denne nøgle/regel til opgavetabellen for at sikre, at alle medarbejder-id'er fra tabellen refererer til en eksisterende post i medarbejdertabellen. Dette script vil se sådan ud:

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

Og hvis vi besluttede at tilføje denne regel på tidspunktet for oprettelse af opgavetabellen, så ville koden se sådan ud:

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

Der er i øvrigt situationer, hvor den streng, vi henviser til, har en unik sammensat nøgle: for eksempel "Navn og fødselsår" eller "productCatogoryId and productId". Så kan FOREIGN KEY skrives sådan her:

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

UDENLANDSKE NØGLE og skiftende data

Forestil dig nu en situation, hvor vi besluttede at opdatere nogle data i medarbejdertabellen, og vores medarbejder-id er ændret. Hvad vil der ske med dataene i opgavetabellen? Det er rigtigt, de bliver irrelevante, og integriteten af ​​vores database vil blive krænket.

For at forhindre dette i at ske, kan du bede SQL Server om at ændre medarbejder-id'et for alle rækker i alle tabeller, der refererer til dette bestemte ændrede id, når id'et i medarbejdertabellen ændres.

Sådanne scripts kaldes OnUpdate og OnDelete . Hvad skal man gøre, hvis post-id'et ændres, og hvad skal man gøre, hvis posten slettes?

Med fjernelsen er ikke alt så simpelt. Hvis du har afhængige objekter repræsenteret af strenge i databasen, der refererer til hinanden, så er en lang række adfærdsscenarier mulige, når du sletter et objekt.

Lad os sige, at vi sletter en webstedsbruger, hvilket betyder, at vi skal slette al hans personlige korrespondance. Men det er usandsynligt, at vi skal fjerne alle hans offentlige kommentarer.

Eller en medarbejder siger op. Det ville være mærkeligt, hvis han sagde op, og samtidig forsvandt alle de opgaver, han fik tildelt, fra databasen. Men hvis de ikke var blevet udpeget af ham, ville det også være gået dårligt. Det er mere korrekt at gøre det, så medarbejderen kan stoppe efter at have overført alle sine opgaver til andre personer.

Her er, hvordan vi kan beskrive disse scenarier ved hjælp af FOREIGN KEY. Den generelle form for en sådan nøgle/regel er:

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

Hvad skal man gøre i tilfælde af sletning (ON DELETE) eller ændring (ON UPDATE) poster? I alt kan der være 5 muligheder for SQL-serveren til at handle i hver af disse situationer:

Sådan kunne vi ændre vores opgavetabel:

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

Hvad er skrevet her:

PÅ OPDATERING CASCADE : Hvis id-nøglen i medarbejdertabellen ændres, skal du også ændre medarbejder-id'et i opgavetabellen, der refererer til den.

PÅ SLETTEBEGRÆNSNING : Hvis en række slettes fra medarbejdertabellen og der henvises til fra opgavetabellen, skal du forhindre rækken i at blive slettet fra medarbejdertabellen.