Описание

Конфликт инструкции update с ограничением foreign key

emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL);

CREATE TABLE department(dept_no CHAR(4) NOT NULL, dept_name CHAR(25) NOT NULL, location CHAR(30) NULL);

CREATE TABLE project (project_no CHAR(4) NOT NULL, project_name CHAR(15) NOT NULL, budget FLOAT NULL);

CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL);

Кроме типа данных и свойства содержать значения NULL. в спецификации столбца можно указать следующие параметры:

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

CURRENT_USER. SESSION_USER. SYSTEM_USER. CURRENT_TIMESTAMP и NULL.

Столбец идентификаторов, создаваемый указанием свойства IDENTITY. может иметь только целочисленные значения, которые системой присваиваются обычно неявно. Каждое следующее значение, вставляемое в такой столбец, вычисляется, увеличивая последнее, вставленное в этот столбец, значение. Поэтому определение столбца со свойством IDENTITY содержит (явно или неявно) начальное значение и шаг инкремента. Это свойство подробно рассматривается в главе 6 (см. пример 6.42).

В заключение этого раздела, в примере 5.5, показано создание таблицы, содержащей столбец типа SQL_VARIANT .

Пример 5.5. Создание таблицы, содержащей столбец типа SQL_VARIANT

CREATE TABLE Item_Attributes (

item_id INT NOT NULL,

attribute NVARCHAR(30) NOT NULL, value SQL_VARIANT NOT NULL, PRIMARY KEY (item_id, attribute) )

В примере 5.5 создается таблица, содержащая столбец value. который имеет тип SQL_VARIANT. Как рассматривалось в главе 4, тип данных SQL_VARIANT можно использовать для хранения значений разных типов одновременно, таких как числовые

Глава 5. Язык описания данных

значения, строки и даты. Обратите внимание на то, что в примере 5.5 столбцу присваивается тип данных SQL_VARIANT по той причине, что значения разных атрибутов могут быть разных типов данных. Например, для атрибута размера тип данных значения attribute будет целочисленным, а для атрибута имени — строковым.

Инструкция CREATE TABLE

и ограничения декларативной целостности

Одной из самых важных особенностей, которую должна предоставлять СУБД, является способ обеспечения целостности данных. Ограничения, которые используются для проверки данных при их модификации или вставке, называются ограничениями для обеспечения целостности (integrity constraints). Обеспечение целостности данных может осуществляться пользователем в прикладной программе или же системой управления базами данных. Наиболее важными преимуществами предоставления ограничений целостности системой управления базами данных являются следующие:

повышается надежность данных;

сокращается время на программирование;

упрощается техническое обслуживание.

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

Если ограничения для обеспечения целостности не предоставляются системой управления базами данных, то их необходимо определить в каждой программе приложения, которая использует данные, включенные в это ограничение. В противоположность этому, если ограничения для обеспечения целостности предоставляются системой управления базами данных, то их требуется определить только один раз. Кроме этого, код для ограничений, предоставляемых приложениями, обычно более сложный, чем в случае таких же ограничений, предоставляемых СУБД.

Если ограничения для обеспечения целостности предоставляются СУБД, то в случае изменений ограничений, соответствующие изменения в коде необходимо реализовать только один раз — в системе управления базами данных. А если ограничения предоставляются приложениями, то модификацию для отражения изменений в ограничениях необходимо выполнить в каждом из этих приложений.

Системами управления базами данных предоставляются два типа ограничений для обеспечения целостности:

декларативные ограничения для обеспечения целостности;

процедурные ограничения для обеспечения целостности, реализуемые посредством триггеров (информацию о триггерах см. в главе 13).

Часть II. Язык Transact-SQL

Декларативные ограничения определяются с помощью инструкций языка DDL CREATE TABLE и ALTER TABLE. Эти ограничения могут быть уровня столбцов или уровня таблицы. Ограничения уровня столбцов определяются наряду с типом данных и другими свойствами столбца в объявлении столбца, тогда как ограничения уровня таблицы всегда определяются в конце инструкции CREATE TABLE или ALTER TABLE после определения всех столбцов.

Между ограничениями уровня столбцов и ограничениями уровня таблицы есть лишь одно различие: ограничения уровня столбцов можно применять только к одному столбцу, в то время как ограничения уровня таблицы могут охватывать больше, чем один столбец таблицы.

Каждому декларативному ограничению присваивается имя. Это имя может быть присвоено явно посредством использования опции CONSTRAINT в инструкции CREATE TABLE или ALTER TABLE. Если опция CONSTRAINT не указывается, то имя ограничению присваивается неявно компонентом Database Engine.

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

Декларативные ограничения можно сгруппировать в следующие категории:

предложение PRIMARY KEY ;

ссылочная целостность и предложение FOREIGN KEY .

Использование предложения DEFAULT для определения ограничения по умолчанию было показано в этой главе ранее (см. также пример 5.6). Все другие ограничения рассматриваются в последующих разделах.

Иногда несколько столбцов или группа столбцов таблицы имеет уникальные значения, что позволяет использовать их в качестве первичного ключа. Столбцы или группы столбцов, которые можно использовать в качестве первичного ключа, называются потенциальными ключами (candidate key). Каждый потенциальный ключ определяется, используя предложение UNIQUE в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения UNIQUE следующий:

UNIQUE [CLUSTERED NONCLUSTERED1 (. )

Глава 5. Язык описания данных

Опция CONSTRAINT в предложении UNIQUE присваивает явное имя потенциальному ключу. Опция CLUSTERED или NONCLUSTERED связана с тем обстоятельством, что компонент Database Engine создает индекс для каждого потенциального ключа таблицы. Этот индекс может быть кластеризованным, когда физический порядок строк определяется посредством индексированного порядка значений столбца. Если порядок строк не указывается, индекс является некластеризованным (см. главу 10). По умолчанию применяется опция NONCLUSTERED. Параметр col_name1 обозначает имя столбца, который создает потенциальный ключ. (Потенциальный ключ может иметь до 16 столбцов.)

Применение предложения UNIQUE показано в примере 5.6. (Прежде чем выполнять этот пример, в базе данных sample нужно удалить таблицу projects. используя для этого инструкцию DROP TABLE projects .)

Пример 5.6. Применение предложения UNIQUE

CREATE TABLE projects (project_no CHAR(4) DEFAULT 'p1', project_name CHAR(15) NOT NULL, budget FLOAT NULL

CONSTRAINT unique_no UNIQUE (project_no));

Каждое значение столбца project_no таблицы projects является уникальным, включая значение NULL. (Точно так же, как и для любого другого значения с ограничением UNIQUE. если значения NULL разрешены для соответствующего столбца, этот столбец может содержать не более одной строки со значением NULL .) Попытка вставить в столбец project_no уже имеющееся в нем значение будет неуспешной, т. к. система не примет его. Явное имя ограничения, определяемого в приме-

ре 5.6, — unique_no .

Предложение PRIMARY KEY

Первичным ключом таблицы является столбец или группа столбцов, значения которого разные в каждой строке. Каждый первичный ключ определяется, используя предложение PRIMARY KEY в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения PRIMARY KEY следующий:

PRIMARY KEY [CLUSTERED | NONCLUSTERED] (. )

Все параметры предложения PRIMARY KEY имеют такие же значения, как и соответствующие одноименные параметры предложения UNIQUE. Но в отличие от столбца UNIQUE. столбец PRIMARY KEY не разрешает значений NULL и имеет значение по умол-

В примере 5.7 показано объявление первичного ключа для таблицы employee базы данных sample .

122 Часть II. Язык Transact-SQL

Прежде чем выполнять этот пример, в базе данных sample нужно удалить таблицу employee, используя для этого инструкцию DROP TABLE employee.

Пример 5.7. Определение первичного ключа

CREATE TABLE employee (emp_no INTEGER NOT NULL,

emp_fname CHAR(20) NOT NULL,

emp_lname CHAR(20) NOT NULL,

dept_no CHAR(4) NULL,

CONSTRAINT prim_empl PRIMARY KEY (emp_no));

В результате выполнения кода в примере 5.7 снова создается таблица employee. в которой определен первичный ключ. Первичный ключ таблицы определяется посредством декларативного ограничения для обеспечения целостности с именем prim_empl. Это ограничение для обеспечения целостности является ограничением уровня таблицы, поскольку оно указывается после определения всех столбцов таб-

Пример 5.8 эквивалентный примеру 5.7, за исключением того, что первичный ключ таблицы employee определяется как ограничение уровня столбца.

Опять же, прежде чем выполнять этот пример, в базе данных sample нужно удалить таблицу employee, используя для этого инструкцию DROP TABLE employee.

Пример 5.8. Определение ограничения уровня столбца

CREATE TABLE employee

(emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY,

emp_fname CHAR(20) NOT NULL,

emp_lname CHAR(20) NOT NULL,

dept_no CHAR(4) NULL);

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

Проверочное ограничение (check constraint) определяет условия для вставляемых в столбец данных. Каждая вставляемая в таблицу строка или каждое значение, ко-

Глава 5. Язык описания данных

торым обновляется значение столбца, должно отвечать этим условиям. Проверочные ограничения устанавливаются посредством предложения CHECK. определяемого в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения CHECK следующий:

CHECK [NOT FOR REPLICATION] expression

Параметр expression должен иметь логическое значение ( true или false ) и может ссылаться на любые столбцы в текущей таблице (или только на текущий столбец, если определен как ограничение уровня столбца), но не на другие таблицы. Предложение CHECK не применяется принудительно при репликации данных, если присутствует параметр NOT FOR REPLICATION. (При репликации база данных, или ее часть, хранится в нескольких местах. С помощью репликации можно повысить уровень доступности данных. Репликация данных рассматривается в главе 19.)

В примере 5.9 показано применение предложения CHECK .

Пример 5.9. Применение предложения CHECK

CREATE TABLE customer

(cust_no INTEGER NOT NULL,

cust_group CHAR(3) NULL,

CHECK (cust_group IN ('c1', ' c2 ', 'c10')));

Создаваемая в примере 5.9 таблица customer включает столбец cust_group. содержащий соответствующее проверочное ограничение. При вставке нового значения, отличающегося от значений в наборе ('cl', 'c2', 'c10). или при попытке изменения существующего значения на значение, отличающегося от этих значений, система управления базой данных возвращает сообщение об ошибке.

Предложение FOREIGN KEY

Внешним ключом (foreign key) называется столбец (или группа столбцов таблицы), содержащий значения, совпадающие со значениями первичного ключа в этой же или другой таблице. Внешний ключ определяется с помощью предложения FOREIGN KEY в комбинации с предложением REFERENCES. Синтаксис предложения FOREIGN KEY следующий:

[ON DELETE ( NO ACTION ] CASCADE | SET NULL | SET DEFAULT>] [ON UPDATE ( NO ACTION | CASCADE | SET NULL | SET DEFAULT>]

Предложение FOREIGN KEY явно определяет все столбцы, входящие во внешний ключ. В предложении REFERENCES указывается имя таблицы, содержащей столбцы, создающие соответствующий первичный ключ. Количество столбцов и их тип дан-

ных в предложении FOREIGN KEY должны совпадать с количеством соответствующих столбцов и их типом данных в предложении REFERENCES (и, конечно же, они должны совпадать с количеством столбцов и типами данных в первичном ключе таблицы, на которую они ссылаются).

Таблица, содержащая внешний ключ, называется ссылающейся (или дочерней) таблицей (referencing table), а таблица, содержащая соответствующий первичный ключ, называется ссылочной (referenced table) или родительской (parent table) таблицей. В примере 5.10 показано объявление внешнего ключа для таблицы works_on базы данных sample .

Прежде чем выполнять этот пример, в базе данных sample нужно удалить таблицу works_on, используя для этого инструкцию DROP TABLE works_on.

Пример 5.10. Объявление внешнего ключа

CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL,

job CHAR (15) NULL, enter_date DATE NULL,

CONSTRAINT prim_works PRIMARY KEY(emp_no, project_no),

CONSTRAINT foreign_works FOREIGN KEY(emp_no)

REFERENCES employee (emp_no));

Таблица works_on в примере 5.10 задается с двумя декларативными ограничениями для обеспечения целостности: prim_works и foreign_works. Оба ограничения являются уровня таблицы, где первое указывает первичный ключ, а второе — внешний ключ таблицы works_on. Кроме этого, ограничение foreign_works определяет таблицу employee. как ссылочную таблицу, а ее столбец emp_no. как соответствующий первичный ключ столбца с таким же именем в таблице works_on .

Предложение FOREIGN KEY можно пропустить, если внешний ключ определяется, как ограничение уровня таблицы, поскольку столбец, к которому применяется ограничение, является неявным "списком" столбцов внешнего ключа, и ключевого слова REFERENCES достаточно для указания того, какого типа является это ограничение. Таблица может содержать самое большее 63 ограничения FOREIGN KEY .

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

Ссылочная целостность (referential integrity) обеспечивает выполнение правил для вставок и обновлений таблиц, содержащих внешний ключ и соответствующее ог-

Глава 5. Язык описания данных

раничение первичного ключа. В примерах 5.7 и 5.10 задаются два таких ограничения: prim_empl и foreign_works. Предложение REFERENCES в примере 5.10 определяет таблицу employee в качестве ссылочной (родительской) таблицы.

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

Возможные проблемы со ссылочной целостностью

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

Вставка новой строки в таблицу works_on с номером сотрудника 11111 .

Соответствующая инструкция Transact-SQL выглядит таким образом:

INSERT INTO works_on (emp_no. )

При вставке новой строки в дочернюю таблицу works_on используется новый номер сотрудника emp_no. для которого нет совпадающего сотрудника (и номера) в родительской таблице employee. Если для обеих таблиц определена ссылочная целостность, как это сделано в примерах 5.7 и 5.10, то компонент Database Engine не допустит вставки новой строки с таким номером emp_no .

Изменение номера сотрудника 10102 во всех строка таблицы works_on на номер

Соответствующая инструкция Transact-SQL выглядит таким образом:

SET emp_no = 11111 WHERE emp_no = 10102;

В данном случае существующее значение внешнего ключа в ссылающейся таблице works_on заменяется новым значением, для которого нет совпадающего значения в родительской таблице employee. Если для обеих таблиц определена ссылочная целостность, как это сделано в примерах 5.7 и 5.10, то система управления базой данных не допустит модификацию строки с таким номером emp_no в таблице works_on .

Замена значения 10102 номера сотрудника emp_no на значение 22222 в таблице employee. Соответствующая инструкция Transact-SQL будет выглядеть таким образом:

SET emp_no = 22222 WHERE emp_no = 10102;

В данном случае предпринимается попытка заменить существующее значение 10102 номера сотрудника emp_no значением 22222 только в родительской таблице employee. не меняя соответствующие значения emp_no в ссылающейся таблице works_on. Система не разрешает выполнения этой операции. Ссылочная целостность не допускает существования в ссылающейся таблице (таблице, для которой предложением FOREIGN KEY определен внешний ключ) таких значений, для которых в родительской таблице (таблице, для которой предложением PRIMARY KEY определен первичный ключ) не существует соответствующего значения. В противном случае такие строки в ссылающейся таблице были бы "сиротами". Если бы описанная выше модификация таблицы employee была разрешена, тогда строки в таблице works_on со значением emp_no равным 10102 были бы сиротами. Поэтому система и не разрешает выполнения такой модификации.

Удаление строки в таблице employee со значением emp_no равным 10102 .

Этот случай похожий на случай 3. В случае выполнения этой операции, из таблицы employee была бы удалена строка со значением emp_no. для которого существуют совпадающие значения в ссылающейся (дочерней) таблице works_on. В примере 5.11 приводится код для определения всех ограничений первичного и внешнего ключей для таблиц базы данных sample. Для выполнения этого кода,

если таблицы employee. department. project и works_on уже существуют, то их

нужно удалить с помощью инструкции DROP TABLE table_name (имя_таблицы).

Пример 5.11. Определение всех ограничений первичного и внешнего ключей для таблиц базы данных sample

CREATE TABLE department(dept_no CHAR(4) NOT NULL, dept_name CHAR(25) NOT NULL, location CHAR(30) NULL,

CONSTRAINT prim_dept PRIMARY KEY (dept_no));

CREATE TABLE employee (emp_no INTEGER NOT NULL,

emp_fname CHAR(20) NOT NULL, emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL,

CONSTRAINT prim_emp PRIMARY KEY (emp_no),

CONSTRAINT foreign_emp FOREIGN KEY(dept_no) REFERENCES department(dept_no));

CREATE TABLE project (project_no CHAR(4) NOT NULL, project_name CHAR(15) NOT NULL, budget FLOAT NULL,

CONSTRAINT prim_proj PRIMARY KEY (project_no));

Глава 5. Язык описания данных

CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL,

CONSTRAINT prim_works PRIMARY KEY(emp_no, project_no), CONSTRAINT foreign1_works FOREIGN KEY(emp_no) REFERENCES

employee(emp_no), CONSTRAINT foreign2_works FOREIGN KEY(project_no)

Опции ON DELETE и ON UPDATE

Компонент Database Engine на попытку удаления и модифицирования первичного ключа может реагировать по-разному. Если попытаться обновить значения внешнего ключа, то все эти обновления будут несогласованы с соответствующим первичным ключом (см. случай 1 и 2 в предыдущем разделе), база данных откажется выполнять эти обновления и выведет сообщение об ошибке, наподобие следующего:

Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FKemployee'. The conflict occurred in database 'sample', table 'employee', column 'dept_no'. The statement has been terminated.

(Сообщение 547, уровень 16, состояние 0, строка 1. Конфликт инструкции UPDATE с ограничением FOREIGN KEY "foreign1_works". Конфликт произошел в базе данных "sample", таблица "dbo.employee", столбец 'emp_no'. Выполнение данной инструкции было прервано.)

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

NO ACTION. Модифицируются (обновляются или удаляются) только те значения

в родительской таблице, для которых нет соответствующих значений во внешнем ключе дочерней (ссылающейся) таблицы.

CASCADE. Разрешается модификация (обновление или удаление) любых значений

в родительской таблице. При обновлении значения первичного ключа в родительской таблице или при удалении всей строки, содержащей данное значение,

в дочерней (ссылающейся) таблице обновляются (т. е. удаляются) все строки

с соответствующими значениями внешнего ключа.

SET NULL. Разрешается модификация (обновление или удаление) любых значений

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