Язык запросов SQL

         

Ссылочная целостность



Ссылочная целостность

Даже если в базе данных для каждой таблицы установлены целостность объекта и доменная целостность, то этой базе все равно грозят неприятности, происходящие из-за того, что связь одной таблицы с другой не согласована. В большинстве хорошо спроектированных баз данных в каждой таблице имеется как минимум один столбец, который ссылается на столбец из другой таблицы той же базы. Такие ссылки играют важную роль при поддержании общей целостности базы данных. Впрочем, те же ссылки делают возможными аномалии обновления.

Аномалии обновления — это неприятности, которые могут происходить после обновления значений в строке базы данных.

В целом отношения между таблицами не являются равноправными. Обычно одна таблица зависит от другой. Скажем, у вас, например, имеется база данных с таблицами CLIENT (фирма-клиент) и ORDERS (заказы). Вы можете намеренно ввести в таблицу CLIENT данные фирмы-клиента еще до того, как ею будут сделаны какие-либо заказы. Однако в таблицу ORDERS нельзя будет ввести ни одного заказа, если в первой, CLIENT, не будет записи для клиента, делающего этот заказ. Получается, что таблица ORDERS зависит от таблицы CLIENT. Такой порядок часто называют родительско-дочерним отношением таблиц, при котором CLIENT — это родительская, a ORDERS — дочерняя таблица. Дочерний элемент базы данных зависит от родительского. Обычно первичный ключ родительской таблицы — это столбец (или группа столбцов), который имеется и в дочерней таблице. И там он уже является внешним ключом. Во внешнем ключе могут находиться неопределенные значения, и ему не нужно быть уникальным.

Аномалии обновления возникают несколькими способами. Например, фирма-клиент не делает у вас заказов, и вы хотите удалить ее данные из базы. И если она уже сделала у вас некоторые заказы, данные о которых записаны в таблице ORDERS, то удаление ее данных из таблицы CLIENT может вызвать трудности. Дело в том, что тогда в дочерней таблице ORDERS остались бы записи, для которых не было бы соответствующих записей в главной таблице CLIENT. Аналогичные трудности могут возникнуть и тогда, когда запись в дочернюю таблицу добавляется, а соответствующее добавление в родительскую еще не сделано. Все изменения первичного ключа, происходящие в любой строке родительской таблицы, должны отражаться в соответствующих внешних ключах всех дочерних таблиц. Если этого не произойдет, образуются аномалии обновления.

Большинство трудностей, связанных со ссылочной целостностью, можно свести к минимуму, если тщательно управлять процессом обновления. В некоторых случаях необходимо каскадное удаление из родительской таблицы и ее дочерних таблиц. Чтобы при удалении строки из родительской таблицы произошли каскадные удаления, необходимо из всех дочерних таблиц удалить строки, значение внешнего ключа которых равно значению первичного ключа строки, удаляемой из главной таблицы. Рассмотрим следующий пример:



CREATE TABLE CLIENT (
ClientName CHARACTER (30), PRIMARY KEY,
Address1 CHARACTER (30),
Address2 CHARACTER (30),  
City CHARACTER (25), NOT NULL,
State CHARACTER (2),  
PostalCode CHARACTER (10),  
Phone CHARACTER (13),  
Fax CHARACTER (13),  
ContactPerson CHARACTER (30) ) ;  
CREATE TABLE TESTS (
TestName CHARACTER (30) PRIMARY KEY,
StandardCharge CHARACTER (30) ) ;  
CREATE TABLE EMPLOYEE (
EmployeeName CHARACTER (30) PRIMARY KEY,
Address1 CHARACTER (30),  
Address2 CHARACTER (30),  
City CHARACTER (25),  
State CHARACTER (2),  
PostalCode CHARACTER (10),  
HomePhone CHARACTER (13),  
OfficeExtension CHARACTER (4),  
HireDate DATE,  
JobClassification CHARACTER (10),  
HourSalComm CHARACTER (1) ) ;  
CREATE TABLE ORDERS (
OrderNumber INTEGER PRIMARY KEY,
ClientName CHARACTER (30),  
TestOrdered CHARACTER (30),  
Salesperson CHARACTER (30),  
OrderDate DATE ) ;  
CONSTRAINT NameFK FOREIGN KEY (ClientName)
REFERENCES CLIENT (ClientName)
ON DELETE CASCADE,
CONSTRAINT TestFK FOREIGN KEY (TestOrdered)
REFERENCES TESTS (TestName)
ON DELETE CASCADE,
CONSTRAINT SalesFK FOREIGN KEY (Salesperson)
REFERENCES EMPLOYEE (EmployeeName)
ON DELETE CASCADE ) ;

Ограничение NameFK делает поле ClientName внешним ключом, который указывает на столбец ClientName таблицы CLIENT. Когда в таблице CLIENT удаляется строка, то в таблице ORDERS автоматически удаляются все строки, у которых в столбце ClientName имеется то же значение, что и в столбце ClientName удаляемой строки таблицы CLIENT. Происходит каскадное удаление— вначале в таблице CLIENT, а затем в таблице ORDERS. To же самое верно для внешних ключей таблицы ORDERS, которые являются первичными ключами таблиц TESTS и EMPLOYEE.

Возможно, вы не хотите производить каскадное удаление, а вместо этого хотите заменить внешний ключ подчиненной таблицы значением NULL. Проанализируйте следующий вариант предыдущего примера:

CREATE TABLE ORDERS (
OrderNumber INTEGER PRIMARY KEY,
ClientName CHARACTER (30),
TestOrdered CHARACTER (30),
Salesperson CHARACTER (30),
OrderDate DATE ) ;
CONSTRAINT NameFK FOREIGN KEY (ClientName)
REFERENCES CLIENT (ClientName),
CONSTRAINT TestFK FOREIGN KEY (TestOrdered)
REFERENCES TESTS (TestName),
CONSTRAINT SalesFK FOREIGN KEY (Salesperson)
REFERENCES EMPLOYEE (EmployeeName),) ;
ON DELETE SET NULL ) ;

Ограничение SalesFK определяет поле Salesperson внешним ключом, который указывает на столбец EmployeeName таблицы EMPLOYEE. Если сотрудница, работавшая вашим представителем при оформлении заказов, уходит из компании, вы удаляете ее строку из таблицы EMPLOYEE. Co временем ее место займет другой работник. А сейчас удаление строки с ее данными из таблицы EMPLOYEE приводит к заменам значений. Эти замены состоят в том, что в таблице ORDERS во всех строках с заказами, оформленными этим представителем, столбцу Salesperson присваивается неопределенное значение.

Есть и другой способ предохранить базу от несогласованных данных. Он состоит в том, чтобы отказаться от разрешения добавлять строки в дочернюю таблицу, пока в родительской таблице не появится соответствующая им строка. Если вы откажетесь разрешать добавление строк в дочерней таблице, пока не будет нужной строки в родительской таблице, то таким образом предотвратите появление "строк-сирот" в дочерней таблице. Это позволит легко поддерживать согласованность таблиц. Еще одна возможность состоит в том, чтобы запретить изменять первичный ключ таблицы. В этом случае можно не беспокоиться об обновлении внешних ключей в других таблицах, которые зависят от этого первичного ключа.



Содержание раздела