BEGRÄNSNING: databasintegritet

Databasintegritetskontroll

En annan viktig sak att veta om databaser är CONSTRAINS. Med hjälp av begränsningar kan du kontrollera dataändringar i dina tabeller och behålla deras integritet och konsistens.

Vad är datakonsistens när vi talar om en databas?

Låt oss ta vår webbutik med anställd-, produkt- och uppgiftstabeller . Vi vet redan att det kan finnas uppgifter i uppgiftstabellen som inte är tilldelade någon: anställd_id för sådana rader är NULL.

Men vad händer om det finns en post i uppgiftstabellen med ett anställd_id lika med, säg, 115? Vi har trots allt ingen sådan anställd. Vi har ingen anställd med id = 115 i personaltabellen. Samtidigt finns en länk till en anställd med detta id i uppgiftstabellen. Detta är ett exempel på datainkonsekvens .

Så hur stämmer vi ihop dessa uppgifter? Helst skulle det vara så att SQL-servern, med alla dataförändringar, kontrollerade alla dessa nyanser. Och det finns en sådan möjlighet, den heter FOREIGN_KEY.

Om någon kolumn i din tabell inte bara innehåller siffror, utan id-rader från en annan tabell, kan detta anges explicit.

Lägga till en UTLÄNDSK NYCKEL

En sådan nyckel kan läggas till i tabellen både vid skapandet och efter det med ALTER TABLE. Formatet är inte fundamentalt annorlunda. Vi kommer att presentera båda alternativen.

Den allmänna formen för en sådan nyckel/regel är:

FOREIGN KEY (column)
  	REFERENCES table(column)

Låt oss lägga till den här nyckeln/regeln i uppgiftstabellen för att säkerställa att alla anställda_id från tabellen hänvisar till en befintlig post i anställningstabellen. Detta skript kommer att se ut så här:

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

Och om vi bestämde oss för att lägga till den här regeln när vi skapade uppgiftstabellen, skulle koden se ut så här:

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

Förresten, det finns situationer när strängen vi refererar till har en unik sammansatt nyckel: till exempel "Namn och födelseår" eller "productCatogoryId och productId". Då kan FOREIGN KEY skrivas så här:

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

UTLÄNDSK NYCKEL och ändring av data

Föreställ dig nu en situation där vi bestämde oss för att uppdatera vissa data i personaltabellen och vårt medarbetar-ID har ändrats. Vad kommer att hända med data i uppgiftstabellen? Det stämmer, de kommer att bli irrelevanta och vår databas integritet kommer att kränkas.

För att förhindra att detta händer kan du säga till SQL Server att ändra anställd_id för alla rader i alla tabeller som hänvisar till just detta ändrade id när id:t i tabellen för anställda ändras.

Sådana skript kallas OnUpdate och OnDelete . Vad ska man göra om post-id ändras och vad ska man göra om posten raderas?

Med borttagningen är inte allt så enkelt. Om du har beroende objekt representerade av strängar i databasen som hänvisar till varandra, är en mängd olika beteendescenarier möjliga när du tar bort ett objekt.

Låt oss säga att vi tar bort en webbplatsanvändare, vilket betyder att vi måste ta bort all hans personliga korrespondens. Men det är osannolikt att vi ska ta bort alla hans offentliga kommentarer.

Eller så slutar en anställd. Det vore konstigt om han slutade och samtidigt försvann alla uppgifter som han tilldelats från databasen. Men om de hade förblivit utsedda inte av honom, skulle det också ha gått illa. Det är mer korrekt att göra det så att medarbetaren kan sluta efter att ha överlåtit alla sina uppgifter till andra personer.

Så här kan vi beskriva dessa scenarier med hjälp av FOREIGN KEY. Den allmänna formen för en sådan nyckel/regel är:

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

Vad ska man göra om man raderar (ON DELETE) eller ändrar (ON UPDATE) poster? Totalt kan det finnas fem alternativ för SQL-servern att agera i var och en av dessa situationer:

Så här kan vi ändra vår uppgiftstabell:

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

Det som står här:

ON UPDATE CASCADE : Om id-nyckeln i anställdtabellen ändras, ändra då även anställd_id i uppgiftstabellen som refererar till den.

PÅ DELETE-BEGRÄNSNING : Om en rad tas bort från anställdstabellen och refereras från uppgiftstabellen, förhindra att raden tas bort från anställdstabellen.