Создание и использование умолчаний, ограничений и правил

Ограничение FOREIGN KEY

Ограничение FOREIGN KEY определяет внешний ключ который задает связь между двумя таблицами. Колонка или колонки внешнего ключа одной таблицы ссылаются на потенциальный ключ (одна или несколько колонок) в другой таблице. При вставке строки в таблицу с ограничением FOREIGN KEY значения, которые должны быть внесены в колонку или колонки, определенные как внешний ключ, сравниваются со значениями в потенциальном ключе ссылочной таблицы. Если ни одна из строк ссылочной таблицы не соответствует значениям во внешнем ключе, то вставка новой строки не выполняется. Но если значения внешнего ключа, которые нужно внести в таблицу, все же имеются в потенциальном ключе другой таблицы, то вставка новой строки будет выполнена. Если значение, которое должно быть занесено в таблицу с ограничением FOREIGN KEY, равно NULL, то это тоже допустимо.

Проверка ограничений FOREIGN KEY происходит также в тех случаях, когда вы хотите обновить какую-либо строку в ссылочной таблице или в таблице с внешним ключом. Вы не сможете обновить какое-либо значение потенциального ключа или внешнего ключа, если это приведет к нарушению ограничения. Существует одно исключение из этого правила, когда вы обновляете ссылочную таблицу с помощью опции ON UPDATE CASCADE оператора T-SQL CREATE TABLE. (См. раздел "Создание и модифицирование ограничений с помощью Enterprise Manager" далее.) Кроме того, ограничения FOREIGN KEY проверяются, если вы хотите удалить какую-либо строку из ссылочной таблицы. Вы не сможете удалить строку из ссылочной таблицы, если строка какой-либо таблицы с внешним ключом (таблицы, содержащей ограничение FOREIGN KEY ) содержит ссылку на значение в колонке внешнего ключа.

Иными словами, для каждой строки в таблице с внешним ключом должна существовать соответствующая строка в ссылочной таблице, и эту строку нельзя удалить, пока на нее имеется ссылка. Существует также исключение из этого правила: вы можете удалить строку из ссылочной таблицы с помощью опции ON DELETE CASCADE оператора T-SQL CREATE TABLE.(См. раздел "Создание и модифицирование ограничений с помощью Enterprise Manager" далее.)

Внешний ключ может ссылаться только на те колонки, которые содержат в ссылочной таблице ограничение PRIMARY KEY или UNIQUE. Если вы попытаетесь создать внешний ключ, который ссылается на колонку, не являющуюся частью одного из этих ограничений, то SQL Server возвратит сообщение об ошибке. Кроме того, тип данных и размер колонки или колонок внешнего ключа должны совпадать со ссылочной колонкой или колонками.

Чтобы получить более ясное представление о внешних ключах, рассмотрим некоторые примеры. Сначала мы создадим таблицу с именем items (товары), которая содержит ограничение PRIMARY KEY по колонке item_id (идентификатор товара), как в следующем операторе:

CREATE TABLE 	items
( 
item_name  		char(15)     	NOT NULL, 
item_id     	smallint     	NOT NULL IDENTITY(1,1), 
price       	smallmoney   	NULL, 
item_desc   	varchar(30)  	NOT NULL DEFAULT 'none', 
CONSTRAINT  	PK_item_id   	PRIMARY KEY (item_id) 
) 
GO

Затем мы создадим таблицу с именем inventory, содержащую ограничение FOREIGN KEY с именем FK_item_id, которое ссылается на колонку item_id в таблице items, как в следующем операторе:

CREATE TABLE 	inventory
( 
store_id        	tinyint    	NOT NULL, 
item_id         	smallint   	NOT NULL,  
item_quantity   	tinyint    	NOT NULL, 
CONSTRAINT      	FK_item_id 	FOREIGN KEY (item_id)  
REFERENCES      	items(item_id) 
) 
GO

Чтобы увидеть, каким образом связаны эти таблицы, мы создадим схему (диаграмму) базы данных (рис. 16.10). (Инструкции по созданию схемы базы данных см. в "Управление таблицами с помощью T-SQL и Enterprise Manager" .) В данном примере items – это ссылочная таблица с потенциальным ключом item_id. Это единственно возможный потенциальный ключ, поскольку он является первичным ключом в данной таблице и эта таблица не содержит никаких ограничений UNIQUE. Напомним, что только колонки первичного ключа и колонки с ограничениями UNIQUE является допустимыми потенциальными ключами. Таблица inventory содержит ограничение FOREIGN KEY, определенное по ее колонке item_id. С помощью этого ограничения создается связь по внешнему ключу между этими двумя таблицами. Обе связанные колонки имеют тип данных smallint. Ограничение FOREIGN KEY в таблице inventory по колонке item_id гарантирует, что в колонку item_id нельзя ввести никакое значение, если этого значения нет в колонке item_id таблицы items. Иными словами, если товар отсутствует в таблице items, то он не может присутствовать в таблице inventory. Кроме того, из таблицы items нельзя удалить строку, если на нее имеется ссылка из какой-либо строки в таблице inventory. Иными словами, если какой-либо товар присутствует в таблице items и таблице inventory, этот товар нельзя удалить из таблицы items, пока он присутствует в таблице inventory. Теперь вы, вероятно, поняли, что внешние ключи используются для поддержки согласованности базы данных. Например, в данном случае вам не нужна информация о допустимости какого-либо товара в таблицах, если не существует записи об этом товаре в таблице items, предназначенной для хранения записей по каждому имеющемуся товару.

увеличить изображение
Рис. 16.10. Схема базы данных, где показана связь по внешнему ключу между таблицами items и inventory

Чтобы модифицировать ограничение FOREIGN KEY с помощью операторов T-SQL, вы должны сначала удалить старое ограничение и затем создать новое с помощью оператора ALTER TABLE. Этот метод действует аналогично модифицированию ограничения PRIMARY KEY. Ниже приводятся операторы для удаления исходного ограничения по таблице inventory и последующего добавления нового ограничения:

ALTER TABLE inventory
DROP CONSTRAINT FK_item_id 
GO 

ALTER TABLE inventory 
ADD CONSTRAINT FK_item_id FOREIGN KEY (item_id)  
REFERENCES items(item_id) 
GO

Если вы добавляете ограничение FOREIGN KEY к существующей колонке таблицы, SQL Server проверяет существующие строки таблицы, чтобы убедиться в том, что для значений этой колонки (за исключением null -значений) имеются соответствующие значения в колонке с ограничением PRIMARY KEY или UNIQUE ссылочной таблицы. Чтобы создать ограничение FOREIGN KEY без проверки системой SQL Server совпадения с существующими значениями, вы должны использовать опцию WITH NOCHECK оператора ALTER TABLE, как это показано ниже:

ALTER TABLE inventory
WITH NOCHECK ADD CONSTRAINT FK_item_id  
FOREIGN KEY (item_id) 
REFERENCES items(item_id) 
GO

Опция WITH NOCHECK препятствует тому, чтобы SQL Server проверял существующие строки таблицы, и это позволяет добавить соответствующее ограничение к таблице независимо от существующих значений. После добавления этого ограничения SQL Server обеспечивает целостность по внешнему ключу для новых строк.

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

Вы можете также активизировать или отключать использование ограничения FOREIGN KEY. Если вы хотите ввести строку, которая не согласуется с существующим ограничением, то можете временно отключить это ограничение, ввести строку и затем снова активизировать ограничение. Ключевое слово NOCHECK указывает, что данное ограничение следует игнорировать (отключить), а ключевое слово CHECK указывает, что ограничение следует активизировать. Следующие операторы выполняют отключение и повторную активизацию ограничения FOREIGN KEY с помощью ключевых слов NOCHECK и CHECK:

ALTER TABLE inventory
NOCHECK CONSTRAINT FK_item_id    	--Отключает ограничение
GO

—Здесь должен быть оператор INSERT
GO

ALTER TABLE inventory          
CHECK CONSTRAINT FK_item_id      	--Повторно активизирует ограничение
GO

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