OGRANICZENIE: integralność bazy danych

Kontrola integralności bazy danych

Kolejną ważną rzeczą, którą należy wiedzieć o bazach danych, są OGRANICZENIA. Za pomocą ograniczeń możesz kontrolować zmiany danych w tabelach i zachować ich integralność i spójność.

Czym jest spójność danych , gdy mówimy o bazie danych?

Weźmy nasz sklep internetowy z tabelami pracowników, produktów i zadań . Wiemy już, że w tabeli zadań mogą znajdować się zadania, które nie są nikomu przypisane: id_pracownika takich wierszy wynosi NULL.

Ale co się stanie, jeśli w tabeli zadań znajdzie się wpis z id_pracownika równym, powiedzmy, 115? Przecież takiego pracownika nie mamy. Nie mamy pracownika o identyfikatorze = 115 w tabeli pracowników. Jednocześnie link do pracownika o tym identyfikatorze znajduje się w tabeli zadań. To jest przykład niespójności danych .

Jak więc pogodzić te dane? Idealnie byłoby, gdyby serwer SQL przy każdej zmianie danych kontrolował wszystkie te niuanse. I jest taka możliwość, nazywa się FOREIGN_KEY.

Jeśli jakaś kolumna w twojej tabeli zawiera nie tylko liczby, ale id wierszy z innej tabeli, można to określić jawnie.

Dodanie KLUCZA OBCYEGO

Taki klucz można dodać do tabeli zarówno na etapie jej tworzenia, jak i po, używając ALTER TABLE. Format zasadniczo się nie różni. Przedstawimy obie opcje.

Ogólna postać takiego klucza/reguły to:

FOREIGN KEY (column)
  	REFERENCES table(column)

Dodajmy ten klucz/regułę do tabeli zadań , aby upewnić się, że wszystkie identyfikatory_pracowników z tabeli odnoszą się do istniejącego wpisu w tabeli pracowników. Ten skrypt będzie wyglądał następująco:

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

A gdybyśmy zdecydowali się dodać tę regułę w momencie tworzenia tabeli zadań, to kod wyglądałby tak:

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

Nawiasem mówiąc, zdarzają się sytuacje, w których ciąg, do którego się odwołujemy, ma unikalny klucz złożony: na przykład „Nazwa i rok urodzenia” lub „productCatogoryId i productId”. Następnie KLUCZ OBCY można zapisać w następujący sposób:

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

KLUCZ OBCY i zmieniające się dane

Teraz wyobraźmy sobie sytuację, w której zdecydowaliśmy się zaktualizować niektóre dane w tabeli pracowników i zmienił się nasz identyfikator pracownika. Co stanie się z danymi w tabeli zadań? Zgadza się, staną się one nieistotne, a integralność naszej bazy danych zostanie naruszona.

Aby temu zapobiec, możesz powiedzieć SQL Serverowi, aby zmieniał identyfikator_pracownika we wszystkich wierszach we wszystkich tabelach, które odnoszą się do tego zmienionego identyfikatora, gdy zmienia się identyfikator w tabeli pracowników.

Takie skrypty są nazywane OnUpdate i OnDelete . Co zrobić, jeśli zmieni się identyfikator rekordu i co zrobić, jeśli rekord zostanie usunięty?

Po usunięciu nie wszystko jest takie proste. Jeśli w bazie danych istnieją obiekty zależne reprezentowane przez ciągi znaków, które odwołują się do siebie nawzajem, podczas usuwania jednego obiektu możliwe są różne scenariusze zachowania.

Powiedzmy, że usuwamy użytkownika witryny, co oznacza, że ​​musimy usunąć całą jego osobistą korespondencję. Ale jest mało prawdopodobne, abyśmy usunęli wszystkie jego publiczne komentarze.

Albo pracownik odchodzi. Byłoby dziwne, gdyby odszedł, a jednocześnie wszystkie przypisane mu zadania zniknęły z bazy danych. Ale gdyby pozostali wyznaczeni nie przez niego, również źle by się to skończyło. Bardziej poprawne jest to, aby pracownik mógł odejść po przeniesieniu wszystkich swoich zadań na inne osoby.

Oto jak możemy opisać te scenariusze za pomocą KLUCZA OBCYEGO. Ogólna postać takiego klucza/reguły to:

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

Co zrobić w przypadku kasowania (ON DELETE) lub zmiany (ON UPDATE) rekordów? W sumie może istnieć 5 opcji działania serwera SQL w każdej z następujących sytuacji:

Oto jak możemy zmodyfikować naszą tabelę zadań:

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

Co tu jest napisane:

PRZY KASKADZIE AKTUALIZACJI : Jeśli zmieni się klucz id w tabeli pracowników, zmień także identyfikator pracownika w tabeli zadań, która się do niego odwołuje.

ON DELETE RESTRICT : Jeśli wiersz jest usuwany z tabeli pracowników i odwołuje się do niego z tabeli zadań, zapobiegnij usunięciu wiersza z tabeli pracowników.