ОГРАНИЧЕНИЕ: цялост на базата данни

Контрол на целостта на базата данни

Друго важно нещо, което трябва да знаете за базите данни, са ОГРАНИЧЕНИЯТА. С помощта на ограничения можете да контролирате промените в данните във вашите таблици и да поддържате тяхната цялост и последователност.

Какво е съгласуваност на данните , когато говорим за база данни?

Да вземем нашия онлайн магазин с таблици за служители, продукти и задачи . Вече знаем, че в tableта със задачи може да има задачи, които не са присвоени на никого: employee_id на такива редове е NULL.

Но Howво се случва, ако в tableта на задачите има запис с employee_id, equals на, да речем, 115? Все пак нямаме такъв служител. Нямаме служител с id = 115 в tableта на служителите. В същото време в tableта на задачите има връзка към служител с този идентификатор. Това е пример за несъответствие в данните .

И така, How да съгласуваме тези данни? В идеалния случай би било така, че SQL сървърът, с всяка промяна на данните, да контролира всички тези нюанси. И има такава възможност, тя се нарича FOREIGN_KEY.

Ако някоя колона във вашата table съдържа не само числа, но id редове от друга table, тогава това може да бъде определено изрично.

Добавяне на ВЪНШЕН КЛЮЧ

Такъв ключ може да се добави към tableта Howто на етапа на нейното създаване, така и след това, като се използва ALTER TABLE. Форматът не е коренно различен. Ще представим и двата варианта.

Общата форма на такъв ключ/правило е:

FOREIGN KEY (column)
  	REFERENCES table(column)

Нека добавим този ключ/правило към tableта на задачите , за да гарантираме, че всички Emploee_id от tableта се отнасят за съществуващ запис в tableта на служителите. Този скрипт ще изглежда така:

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

И ако решим да добавим това правило по време на създаването на tableта със задачи, тогава codeът ще изглежда така:

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

Между другото, има ситуации, когато низът, към който се отнасяме, има уникален съставен ключ: например „Име и година на раждане“ or „productCatogoryId и productId“. Тогава FOREIGN KEY може да се напише така:

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

ВЪНШЕН КЛЮЧ и промяна на данните

Сега си представете ситуация, в която решихме да актуализираме някои данни в tableта на служителите и идентификационният номер на нашия служител се промени. Какво ще се случи с данните в tableта на задачите? Точно така, те ще станат неуместни и целостта на нашата база данни ще бъде нарушена.

За да предотвратите това да се случи, можете да кажете на SQL Server да промени employee_id на всички редове във всички таблици, които се отнасят до този конкретен променен идентификатор, когато идентификаторът в tableта на служителите се промени.

Такива скриптове се наричат ​​OnUpdate и OnDelete . Какво да направите, ако идентификаторът на записа се промени и Howво да направите, ако записът бъде изтрит?

С премахването не всичко е толкова просто. Ако имате зависими обекти, представени от низове в базата данни, които се отнасят един към друг, тогава са възможни голямо разнообразие от сценарии на поведение при изтриване на един обект.

Да речем, че изтриваме потребител на сайта, което означава, че трябва да изтрием цялата му лична кореспонденция. Но е малко вероятно да премахнем всички негови публични коментари.

Или служител напуска. Би било странно, ако се откаже и в същото време всички възложени му задачи изчезнат от базата данни. Но ако бяха останали назначени не от него, също щеше да излезе зле. По-правилно е да го направите така, че служителят да може да напусне, след като преназначи всичките си задачи на други хора.

Ето How можем да опишем тези сценарии с помощта на FOREIGN KEY. Общата форма на такъв ключ/правило е:

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

Какво да направите в случай на изтриване (ON DELETE) or промяна (ON UPDATE) на записи? Общо може да има 5 опции за действие на SQL сървъра във всяка от тези ситуации:

Ето How можем да модифицираме нашата table със задачи:

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

Какво пише тук:

ПРИ АКТУАЛИЗИРАНЕ НА КАСКАДА : Ако ключът за id в tableта на служителите се промени, тогава променете също Employee_id в tableта на задачите, която го препраща.

ПРИ ОГРАНИЧЕНИЕ ЗА ИЗТРИВАНЕ : Ако даден ред се изтрива от tableта на служителите и е препратен от tableта със задачи, тогава предотвратете изтриването на реда от tableта на служителите.