Salary money NOT NULL,

PriorSalaary money NOT NULL,

LastRaise AS Salary - PriorSalary,

HireDate smalldatetime NOT NULL,

TerminationDate smalldatetime NULL,

ManagerEmpID int NOT NULL

REFERENCES Employees(EmployeelD),

Department varchar (25) NOT NULL

Следует отметить, что при попытке уничтожить таблицу Employees в данный момент (чтобы выполнить оператор, рассматриваемый во втором примере) было бы получено сообщение об ошибке. Рассмотрим, с чем это связано. Дело в том, что после определения в таблице Orders ссылки на таблицу Employees эти две таблицы становятся, как принято выражаться, связанными со схемой . Это означает, что в базе данных теперь содержится информация о наличии так называемой зависимости другой таблицы от таблицы Employees. С учетом такой информации СУБД SQL Server не позволяет уничтожать таблрщу, на которую ссылается другая таблица. Чтобы получить возможность выполнить с помощью СУБД SQL Server удаление таблицы Employees (или по той же причине таблицы Customers), необходимо вначале уничтожить внешний ключ в таблице Orders.

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

Каскадное осуществление действий

Одним из важных различий между внешними ключами и ключами др)тих типов является то, что внешние ключи двунаправлены. Иными словами, действие внешних ключей выражается не только в том, что они обусловливают ввод в дочернюю таблицу только таких значений, которые представлены в родительской таблице, но и обеспечивают проверку строк дочерней таблицы при внесении изменений в родительскую таблицу. (Это позволяет предотвратить появление в дочерней таблице так называемых висячих строк, т.е. строк, потерявших связь со строками родительской таблицы.) По умолчанию СУБД SQL Server защищает от удаления такие строки родительской таблицы, которым соответствуют строки, существующие в дочерней таблице. Но иногда предпочтительный способ организации работы приложения состоит в том, чтобы удаление всех зависимых строк, препятствующих удалению строки, на которую они ссылаются, происходило автоматически. Аналогичный принцип применяется к обновлению строк, поскольку требуется, чтобы в зависимых строках автоматически восстанавливалась ссылка на вновь обновленные строки. Немного реже встречается ситуация, в которой требуется перевести ссылаюшуюся строку в какое-то другое заведомо известное состояние. В последнем случае имеется возможность либо задать в зависимой строке NULL-значение, либо применить для соответствующего столбца то значение, которое предусмотрено по умолчанию.

Процесс автоматического осуществления подобных операций удаления и обновления известен под названием каскадного выполнения действий. Фактически этот процесс, особенно применительно к операциям удаления, может проходить через несколько уровней зависимостей (когда одна строка зависит от другой, вторая зависит от третьей и т.д.). Каскадное выполнение действий предусмотрено и в СУБД SQL Server. Для использования этой возможности достаточно прибегнуть к дополнительным опциям синтаксических конструкций при объявлении внешнего ключа - ввести те конструкции ON, которые были пропущены в начале этого раздела.

Рассмотрим возможность применения каскадных действий на примере еще одной таблицы, создаваемой в базе данных Accounting. Назовем эту таблицу OrderDetails и будем использовать ее для хранения отдельных элементов с обозначением товаров в заказе:

CREATE TABLE OrderDetails (

OrderlD int NOT NULL,

PartNo varchar(10) NOT NULL,

Description varchar(25) NOT NULL, UnitPrice money NOT NULL,

Qty int NOT NULL,

CONSTRAINT PKOrderDetails

PRIMARY KEY (OrderlD, PartNo), CONSTRAINT FKOrderContainsDetails FOREIGN KEY (OrderlD)

REFERENCES Orders(OrderlD)

ON UPDATE NO ACTION

ON DELETE CASCADE

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

Прежде чем приступить к подробному описанию объявления внешнего ключа в этом операторе, остановимся на том, как объявлен в нем первичный ключ. Автор решил не задавать объявление ограничения непосредственно после объявления самого ключа, а выделить его в отдельную конструкцию constraint. Такая структура объявления, во-первых, упрощает работу при создании первичного ключа, состоящего из нескольких столбцов (ведь первичньгй ключ с несколькими столбцами не может быть объявлен как ограничение столбца), и, во-вторых, позволяет проще понять общее устройство оператора CREATE TABLE. Аналогичным образом, можно было бы задать объявление внешнего ключа непосредственно вслед за объявлением столбца. Но автор предпочел другой возможный вариант и также применил отдельную конструкцию constraint. Дополнительная информация по этой теме приведена ниже в данной главе.

Прежде всего необходимо учесть, что применяемый внешний ключ является также частью первичного ключа. Такая организация дочерних таблиц встречается далеко не так уж редко, а в случае ассоциативных таблиц (которые рассматриваются более подробно в главе 7) применяется почти всегда. Но следует помнить, что каждое ограничение всегда действует автономно, поэтому добавление, модификация или удаление каждого ограничения осуществляется независимо от других ограничений.

Теперь рассмотрим само объявление внешнего ключа:

FOREIGN KEY (OrderlD) REFERENCES Orders(OrderlD)

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

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

Теперь приступим к изучению конструкций ON, чтобы лучше разобраться в проблеме каскадного осуществления действий:

ON UPDATE NO ACTION ON DELETE CASCADE

Таким образом, определены два различных действия по обеспечению ссылочной целостности. Вполне очевидно, что действием по обеспечению ссылочной целостности является такое действие, которое должно осуществляться при каждом вызове правила обеспечения ссылочной целостности. В тех ситуациях, когда происходит обновление строки родительской таблицы (строки таблицы Orders), указано, что такие обновления не должны распространяться каскадно на дочернюю таблицу (таблицу OrderDetails). Но в иллюстративных целях автор выбрал для операций удаления действие CASCADE.

Следует отметить, что опция N0 ACTION применяется по умолчанию, поэтому мы не были обязаны задавать ее в приведенном выше коде. Но данное ключевое сяово не поддерживалось в версиях, предшествующих SQL Server 2000, и это стало причиной массовой тенденции не задавать в коде ключевое сяово N0 ACTION. Но есяи поддержка обратной совместимости не требуется, тю автор рекомендует явно задавать ключевое слово N0 ACTION, чтобы явно выразить в коде свои намерения.

Предпримем попытку выполнить вставку данных в таблицу OrderDetails следующим образом:

INSERT INTO OrderDetails VALUES

(1, 4X4525, This is a part, 25.00, 2)

Ho если до сих пор не была выполнена вставка каких-либо данных в эту таблицу, осуществление такой попытки приведет к возникновению следующей ошибки: Msg 547, Level 16, State О, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint FKOrderContainsDetails . The conflict occurred in database Accounting , table Orders , column OrderlD. The statement has been terminated.

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