4.1. Создание таблиц базы данных
Создание объектов базы данных осуществляется с помощью опера- торов языка определения данных (DDL).
Таблицы базы данных создаются с помощью оператора CREATE TABLE. Эта команда создает пустую таблицу, т.е. таблицу, не имеющую строк. Значения в эту таблицу вводятся с помощью оператора INSERT. Оператор CREATE TABLE определяет имя таблицы и множество по- именованных столбцов в указанном порядке. Для каждого столбца должны быть определены тип и размер. Каждая создаваемая таблица должна иметь по крайней мере один столбец. Упрощенный синтаксис оператора CREATE TABLE имеет следующий вид:
CREATE TABLE <имя таблицы>
(<имя столбца><тип данных> [(<размер>)],...);
Следующий пример показывает запрос, создающий таблицу
STUDENT1:
CREATE TABLE STUDENT1 (STUDENT_ID INTEGER,
SURNAME VARCHAR (60),
NAME VARCHAR (60),
STIPEND DOUBLE,
KURS INTEGER,
CITY VARCHAR (60),
BIRTHDAY DATE,
UNIV_ID INTEGER);
4.2. Использование индексации для быстрого доступа к данным
Операции поиска-выборки (SELECT) данных из таблиц по значени- ям их полей могут быть существенно ускорены путем использования индексации данных. Индекс содержит упорядоченный (в алфавитном или числовом порядке) список содержимого столбца или группы столб- цов в индексируемой таблице с идентификаторами соответствующих строк (ROWID). Для пользователей индексирование таблицы по тем или иным столбцам представляет собой способ логического упорядо- чения значений индексированных столбцов, позволяющего, в отличие от последовательного перебора строк, существенно повысить скорость доступа к конкретным строкам таблицы при выборках, использующих значения этих столбцов. Индексация позволяет находить содержащий индексированную строку блок данных, выполняя небольшое число обращений к внешнему устройству хранения данных.
При использовании индексации следует, однако, иметь в виду, что управление индексом существенно увеличивает время выполнения операций, связанных с обновлением данных (таких, как INSERT и DELETE), так как эти операции требуют перестройки индексов.
Индексы можно создавать как по одному, так и по множеству полей. Если указано более одного поля для создания единственного индекса, то данные упорядочиваются по значениям первого поля, по которому осуществляется индексирование. Внутри получившейся груп- пы осуществляется упорядочивание по значениям второго поля, для получившихся в результате групп осуществляется упорядочивание по значениям третьего поля и т.д.
Синтаксис оператора создания индекса имеет следующий вид:
CREATE INDEX <имя индекса> ON <имя таблицы>
(<имя столбца> [, <имя столбца> ]...);
При этом таблица должна уже быть созданной и содержать столб- цы, имена которых указаны в команде создания индекса. Имя индекса, определенное в команде, должно быть уникальным в базе данных. Будучи однажды созданным, индекс является невидимым для пользо- вателя, все операции с ним осуществляет СУБД.
Пример.
Если таблица EXAM_MARKS часто используется для поиска оценки конкретного студента по значению поля STUDENT_ID, то следует со- здать индекс по этому полю:
CREATE INDEX STUDENT_ID_1
ON EXAM_MARKS (STUDENT_ID);
Для удаления индекса (при этом обязательно требуется знать его имя) используется команда DROP INDEX, имеющая следующий син- таксис:
DROP INDEX <имя индекса>;
Удаление индекса не изменяет содержимого поля или полей, индекс которых удаляется.
4.3. Изменение существующей таблицы
Для модификации структуры и параметров существующей таблицы используется оператор ALTER TABLE. Упрощенный синтаксис опера- тора ALTER TABLE для добавления столбцов в таблицу имеет вид
ALTER TABLE <имя таблицы>
ADD (<имя столбца><тип данных><размер>);
При выполнении этого оператора для существующих в таблице строк добавляется новый столбец, в который заносится NULL -значение. Этот столбец становится последним в таблице. Можно добавлять несколько столбцов, в этом случае их определения в команде ALTER TABLE разделяются запятой.
Возможно изменение описания столбцов. Часто это связано с изме- нением размеров столбцов, добавлением или удалением ограничений, накладываемых на их значения. Синтаксис оператора в этом случае имеет вид
ALTER TABLE <имя таблицы>
MODIFY <имя столбца><тип данных><размер/точность>;
Следует иметь в виду, что модификация характеристик столбца может осуществляться не в любом случае, а с учетом следующих ограничений:
| • |
| • |
| • |
• разрешается изменять значения, установленные по умолчанию.
4.4. Удаление таблицы
Синтаксис оператора, осуществляющего удаление таблицы, имеет следующий вид:
DROP TABLE <имя таблицы>;
УПРАЖНЕНИЯ
1. Напишите команду CREATE TABLE для создания таблицы
LECTURER1.
2. Напишите команду CREATE TABLE для создания таблицы
SUBJECT1.
3. Напишите команду CREATE TABLE для создания таблицы
UNIVERSITY1.
4. Напишите команду CREATE TABLE для создания таблицы
EXAM_MARKS1.
5. Напишите команду CREATE TABLE для создания таблицы
SUBJ_LECT1.
6. Напишите команду, которая позволит быстро выбрать данные о студентах по курсам, на которых они учатся.
7. Создайте индекс, который позволит для каждого студента быстро осуществить поиск оценок, сгруппированных по датам.
4.5. Ограничения на множество допустимых значений данных
До сих пор рассматривалось только следующее ограничение: зна- чения, вводимые в таблицу, должны иметь типы данных и разме- ры, совместимые с типами и размерами данных столбцов, в которые эти значения вводятся (как определено в команде CREATE TABLE или ALTER TABLE). Описание таблицы может быть дополнено бо- лее сложными ограничениями, накладываемыми на значения, которые могут быть вставлены в столбец или группу столбцов. Ограничения (CONSTRAINTS) являются частью определения таблицы.
При создании (изменении) таблицы могут быть определены ограни- чения на вводимые значения. В этом случае SQL будет отвергать любое из вводимых значений, не соответствующее заданному ограничению. Ограничения могут быть статическими, ограничивающими значения или диапазон значений, вставляемых в столбец (CHECK, NOT NULL). Они могут иметь связь со всеми значениями столбца, ограничивая новые строки значениями, которые не содержатся в столбцах или их наборах (уникальные значения, первичные ключи). Ограничения могут также определяться связью со значениями, находящимися в другой таблице, допуская, например, вставку в столбец только тех значений, которые в данный момент содержатся также в другом столбце другой или этой же таблицы (внешний ключ). Эти ограничения носят дина- мический характер.
Существуют два основных типа ограничений — ограничения на столбцы и ограничения на таблицу. Ограничения на столбцы (COLUMN CONSTRAINTS) применимы только к отдельным столбцам, а огра- ничения на таблицу (TABLE CONSTRAINTS) применимы к группам, состоящим из одного или более столбцов. Ограничения на столбец
добавляются в конце определения столбца после указания типа дан- ных и перед окончанием описания столбца (запятой). Ограничения на таблицу размещаются в конце определения таблицы, после определе- ния последнего столбца. Команда CREATE TABLE имеет следующий синтаксис, расширенный включением ограничений:
CREATE TABLE <имя таблицы>
(<имя столбца><тип данных><ограничения на столбец>,
<имя столбца><тип данных><ограничения на столбец>,
...
<ограничения на таблицу>
(<имя столбца> [, <имя столбца>...])...);
Поля, заданные в круглых скобках после описания ограничений таблицы, — это поля, на которые эти ограничения распространяются. Ограничения на столбцы применяются к тем столбцам, за именами которых они описаны.
4.5.1. Ограничение NOT NULL. Чтобы запретить возможность использования в поле NULL -значений, можно при создании таблицы командой CREATE TABLE указать для соответствующего столбца клю- чевое слово NOT NULL. Это ограничение применимо только к столб- цам таблицы. Как уже говорилось выше, NULL — это специальный маркер, обозначающий тот факт, что поле пусто. Но он полезен не всегда. Первичные ключи, например, в принципе не должны содер- жать NULL -значений (быть пустыми), поскольку это нарушило бы требование уникальности первичного ключа (более строго – функци- ональную зависимость атрибутов таблицы от первичного ключа). Во многих других случаях также необходимо, чтобы поля обязательно содержали определенные значения. Если ключевое слово NOT NULL размещается непосредственно после типа данных (включая размер) столбца, то любые попытки оставить значение поля пустым (ввести в поле NULL -значение) будут отвергнуты системой.
Например, для того, чтобы в определении таблицы STUDENT1 запретить использование NULL -значений для столбцов STUDENT_ID, SURNAME и NAME, можно записать следующее:
CREATE TABLE STUDENT1 (STUDENT_ID INTEGER NOT NULL,
SURNAME CHAR (25) NOT NULL, NAME CHAR (10) NOT NULL, STIPEND INTEGER,
KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE, UNIV_ID INTEGER);
Важно помнить, что если для столбца указано NOT NULL, то при использовании оператора INSERT обязательно должно быть указано
конкретное значение, вводимое в это поле. При отсутствии ограниче- ния NOT NULL в столбце значение может отсутствовать, если только не указано значение столбца по умолчанию (DEFAULT). Если при создании таблицы ограничение NOT NULL не было указано, то его можно указать позже, используя оператор ALTER TABLE. Для вновь вводимого с помощью оператора ALTER TABLE столбца можно задать ограничение NOT NULL, если таблица, в которую добавляется столбец, пустая, или если для столбца указывается значение по умолчанию.
4.5.2. Уникальность как ограничение на столбец. Иногда тре- буется, чтобы все значения, введенные в столбец, отличались друг от друга. Например, этого требуют первичные ключи. Если при создании таблицы для столбца указывается ограничение UNIQUE, то база дан- ных отвергает любую попытку ввести в это поле какой-либо строки значение, уже содержащееся в том же поле другой строки. Можно предложить следующее определение таблицы STUDENT, использующее ограничение UNIQUE:
CREATE TABLE STUDENT1
(STUDENT_ID INTEGER NOT NULL UNIQUE, SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL, STIPEND INTEGER,
KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE, UNIV_ID INTEGER);
Объявляя поле STUDENT_ID уникальным, можно быть уверенным, что в таблице не появится записей для двух студентов с одинаковы- ми идентификаторами. Столбцы, отличные от первичного ключа, для которых требуется поддержать уникальность значений, называются возможными ключами (CANDIDATE KEYS) или уникальными ключами (UNIQUE KEYS).
4.5.3. Уникальность как ограничение таблицы. Можно устано- вить требование уникальности для сочетания значений группы полей. В этом случае ключевое слово UNIQUE указывается в качестве огра- ничений таблицы. При объединении полей в группу важен порядок, в котором они указываются. Ограничение на таблицу UNIQUE полезно, если требуется поддерживать уникальность группы полей. Например, если в нашей базе данных не допускается, чтобы студент сдавал в один день больше одного экзамена, то можно в таблице объявить уникальной комбинацию значений полей STUDENT_ID и EXAM_DATE. Для этого следует создать таблицу EXAM_MARKS таким способом:
CREATE TABLE EXAM_MARKS1 (EXAM_ID INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL, SUBJ_ID INTEGER NOT NULL, MARK INTEGER,
EXAM_DATE DATE NOT NULL,
UNIQUE (STUDENT_ID, EXAM_DATE));
Обратите внимание на то, что оба поля в ограничении таблицы UNIQUE все еще используют ограничение столбца NOT NULL. Если бы использовалось ограничение столбца UNIQUE для поля STUDENT_ID, то такое ограничение таблицы было бы необязательным.
Если значения поля STUDENT_ID должны быть уникальными для каждой строки в таблице EXAM_MARKS, то это можно сделать, объ- явив UNIQUE как ограничение самого поля STUDENT_ID. В этом случае будет обеспечена уникальность в комбинации значений полей STUDENT_ID, EXAM_DATE. Следовательно, указание UNIQUE как огра- ничение таблицы наиболее полезно использовать в случаях, когда не требуется уникальность индивидуальных полей, как это имеет место на самом деле в рассматриваемом примере.
4.5.4. Присвоение имен ограничениям. Ограничениям таблиц можно присваивать уникальные имена. Преимущество явного задания имени ограничения состоит в том, что в этом случае при выдаче системой сообщения о нарушении установленного ограничения будет указано его имя, что упрощает обнаружение ошибок.
Для присвоения имени ограничению используется несколько изме- ненный синтаксис команд CREATE TABLE и ALTER TABLE.
Приведенный выше пример запроса изменяется следующим обра- зом:
CREATE TABLE EXAM_MARKS1 (EXAM_ID INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL, SUBJ_ID INTEGER NOT NULL, MARK INTEGER,
EXAM_DATE DATE NOT NULL, CONSTRAINT STUD_SUBJ
UNIQUE (STUDENT_ID, EXAM_DATE));
В этом запросе STUD_SUBJ_CONSTR — это имя, присвоенное ука- занному ограничению таблицы.
4.5.5. Ограничение первичных ключей. Первичные ключи таб- лицы — это специальные случаи комбинирования ограничений UNIQUE и NOT NULL. Первичные ключи имеют следующие особенности:
| • |
| • |
| • |
Улучшенный вариант создания таблицы STUDENT1 с объявленным первичным ключом имеет теперь следующий вид:
CREATE TABLE STUDENT1
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL, STIPEND INTEGER,
KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE, UNIV_ID INTEGER);
4.5.6. Составные первичные ключи. Ограничение PRIMARY KEY может также быть применено для нескольких полей, составля- ющих уникальную комбинацию значений — составной первичный ключ. Рассмотрим таблицу EXAM_MARKS. Очевидно, что ни к полю идентификатора студента (STUDENT_ID), ни к полю идентификатора предмета обучения (EXAM_ID) по отдельности нельзя предъявить тре- бование уникальности. Однако для того, чтобы в таблице не могли появиться разные записи для одинаковых комбинаций значений полей STUDENT_ID и EXAM_ID (конкретный студент на конкретном экза- мене не может получить более одной оценки), имеет смысл объявить уникальной комбинацию этих полей. Для этого мы можем приме- нить ограничение таблицы PRIMARY KEY, объявив пару EXAM_ID и STUDENT_ID первичным ключом таблицы:
CREATE TABLE NEW_EXAM_MARKS (STUDENT_ID INTEGER NOT NULL,
SUBJ_ID INTEGER NOT NULL, MARK INTEGER,
DATA DATE, CONSTRAINT EX_PR_KEY
PRIMARY KEY (EXAM_ID, STUDENT_ID));
4.5.7. Проверка значений полей. Ограничение CHECK позволяет определять условие, которому должно удовлетворять вводимое в поле таблицы значение, прежде чем оно будет принято. Любая попытка обновить или заменить значение поля таким, для которого предикат, задаваемый ограничением CHECK, имеет значение ложь, будет отвер- гаться.
Рассмотрим таблицу STUDENT. Значение столбца STIPEND в этой таблице STUDENT выражается десятичным числом. Наложим на зна- чения этого столбца следующее ограничение — величина размера стипендии должна быть меньше 200.
Соответствующий запрос имеет следующий вид:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER CHECK (STIPEND < 200), KURS INTEGER,
CITY CHAR (15), BIRTHDAY DATE, UNIV_ID INTEGER);
4.5.8. Проверка ограничивающих условий с использованием составных полей. Ограничение CHECK можно использовать в каче- стве табличного ограничения, то есть при необходимости включить более одного поля строки в ограничивающее условие.
Предположим, что ограничение на размер стипендии (меньше 200) должно распространяться только на студентов, живущих в Воронеже. Это можно указать в запросе со следующим табличным ограничением CHECK:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL, STIPEND INTEGER,
KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE,
UNIV_ID INTEGER UNIQUE,
CHECK ((STIPEND < 200 AND CITY = ‘Воронеж’) OR
NOT (CITY = ‘Воронеж’)));
или в несколько иной записи:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL, STIPEND INTEGER,
KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE,
UNIV_ID INTEGER UNIQUE, CONSTRAINT STUD_CHECK
CHECK (STIPEND < 200 AND CITY = ‘Воронеж’) OR
NOT (CITY = ‘Воронеж’)));
4.5.9. Установка значений по умолчанию. В SQL имеется воз- можность при вставке в таблицу строки, не указывая значений неко-
торого поля, определять значение этого поля по умолчанию. Наиболее часто используемым значением по умолчанию является NULL. Это значение принимается по умолчанию для любого столбца, для которого не было установлено ограничение NOT NULL.
Значение поля по умолчанию указывается в команде CREATE TABLE тем же способом, что и ограничение столбца, с помощью ключевого слова
DEFAULT <значение по умолчанию>;
Опция DEFAULT не является ограничением, так как она не ограни- чивает значения, вводимые в поле, а просто конкретизирует значение поля в случае, если оно не было задано.
Предположим, что основная масса студентов, информация о кото- рых находится в таблице STUDENT, проживает в Воронеже. Чтобы при задании атрибутов не вводить для большинства студентов название города ‘Воронеж’, можно установить его как значение поля CITY по умолчанию, определив таблицу STUDENT следующим образом:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER CHECK (STIPEND < 200), KURS INTEGER,
CITY CHAR (15) DEFAULT ‘Воронеж’, BIRTHDAY DATE,
UNIV_ID INTEGER);
Другая цель практического применения задания значения по умол- чанию — это использование его как альтернативы для NULL. Как уже отмечалось выше, присутствие NULL в качестве возможных значений поля существенно усложняет интерпретацию операций сравнения, в которых участвуют значения таких полей, поскольку NULL представля- ет собой признак того, что фактическое значение поля неизвестно или неопределенно. Следовательно, строго говоря, сравнение с ним любого конкретного значения в рамках двузначной булевой логики некоррект- но, за исключением специальной операции сравнения IS NULL, которая определяет, является ли содержимое поля каким-либо значением или оно отсутствует. Действительно, каким образом в рамках двузначной логики ответить на вопрос, истинно или ложно условие: CITY = ‘Воронеж’, если текущее значение поля CITY неизвестно (содержит NULL)?
Во многих случаях использование вместо NULL значения, подстав- ляемого в поле по умолчанию, может существенно упростить исполь- зование значений поля в предикатах.
Например, можно установить для столбца опцию NOT NULL, а для неопределенных значений числового типа установить значение
по умолчанию “равно нулю”, или для полей типа CHAR — пробел, использование которых в операциях сравнения не вызывает никаких проблем.
При использовании значений по умолчанию в принципе допустимо применять ограничения UNIQUE или PRIMARY KEY в соответствую- щем поле. При этом, однако, следует иметь в виду отсутствие в таком ограничении практического смысла, поскольку только одна строка в таблице сможет принять значение, совпадающее с этим значением по умолчанию.
УПРАЖНЕНИЯ
1. Создайте таблицу EXAM_MARKS так, чтобы не допускался ввод в таблицу двух записей об оценках одного студента по конкретным экзамену и предмету обучения, а также, чтобы не допускалось проведение двух экзаменов по любым предметам в один день.
2. Создайте таблицу предметов обучения SUBJECT так, чтобы ко- личество отводимых на предмет часов по умолчанию было равно 36, не допускались записи с отсутствующим количеством часов, поле SUBJ_ID являлось первичным ключом таблицы, а значения семестров (поле SEMESTR) лежали в диапазоне от 1 до 12.
3. Создайте таблицу EXAM_MARKS таким образом, чтобы значения поля EXAM_ID были больше значений поля SUBJ_ID, а значения поля SUBJ_ID были больше значений поля STUDENT_ID; пусть также будут запрещены значения NULL в любом из этих трех полей.
4.6. Поддержка целостности данных
В таблицах рассматриваемой базы данных значения некоторых по- лей связаны друг с другом. Так, поле STUDENT_ID в таблице STUDENT и поле STUDENT_ID в таблице EXAM_MARKS связаны тем, что описы- вают одни и те же объекты, т.е. содержат идентификаторы студентов, информация о которых хранится в базе. Более того, значения иден- тификаторов студентов, которые допустимы в таблице EXAM_MARKS, должны выбираться только из списка значений STUDENT_ID, фак- тически присутствующих в таблице STUDENT, т.е. принадлежащих реально описанным в базе студентам. Аналогично, значения поля UNIV_ID таблицы STUDENT должны соответствовать идентификато- рам университетов UNIV_ID, фактически присутствующим в таблице UNIVERSITY, а значения поля SUBJ_ID таблицы EXAM_MARKS долж- ны соответствовать идентификаторам предметов обучения, фактически присутствующим в таблице SUBJECT.
Ограничения, накладываемые указанным типом связи, называют- ся ограничениями ссылочной целостности. Они составляют важную часть описания характеристик предметной области, обеспечения кор-
ректности данных, хранящихся в таблицах. Команды описания таблиц DML имеют средства, позволяющие описывать ограничения ссылочной целостности и обеспечивать поддержание такой целостности при ма- нипулировании значениями полей базы данных.
4.6.1. Внешние и родительские ключи. Когда каждое значение, присутствующее в одном поле таблицы, представлено в другом поле другой или этой же таблицы, говорят, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей. Поле, которое ссылается на другое поле, называется внешним ключом, а поле, на которое ссылается другое поле, называется родительским ключом. В качестве родительского ключа может выступать только поле, являющееся возможным (первичным или альтернативным) клю- чом отношения. Например, поле UNIV_ID таблицы STUDENT — это внешний ключ, ссылающийся на поле UNIV_ID таблицы UNIVERSITY, являющееся ее первичным ключом и выступающее в данном случае в качестве родительского ключа для этого внешнего ключа.
Хотя в приведенном примере имена внешнего и родительского клю- чей совпадают, они не обязательно должны быть одинаковыми, хотя часто их сознательно задают одинаковыми, чтобы соединение было более наглядным.
4.6.2. Составные внешние ключи. Подобно первичному ключу, внешний ключ может состоять как из одного, так и из нескольких полей. Внешний ключ и родительский ключ, на который он ссылается, конечно же, должны быть определены на одинаковом множестве полей (по количеству полей, типам полей и порядку следования полей). Внешние ключи, состоящие из одного поля — применяемые в типовых таблицах настоящего издания — наиболее часты на практике. Чтобы сохранить простоту обсуждения, будем говорить о внешнем ключе, как об одиночном столбце, хотя все, что будет излагаться о поле, которое является внешним ключом, справедливо и для составных внешних ключей, определенных на группе полей.
4.6.3. Смысл внешнего и родительского ключей. Когда поле является внешним ключом, оно определенным образом связано с таб- лицей, на которую этот ключ ссылается. Каждое значение в этом поле (внешнем ключе) непосредственно привязано к конкретному значению в другом поле (родительском ключе). Значения родительского ключа должны быть уникальными, так как он одновременно является ключом отношения. Значения внешнего ключа не обязательно должны быть уникальными, т.е. в отношении может быть любое число строк с одинаковым сочетанием значений атрибутов, составляющих внешний ключ. При этом строки, содержащие одинаковые значения внешнего ключа, должны обязательно ссылаться на конкретное, присутствую- щее в данный момент в таблице, значение родительского ключа или быть неопределенными (NULL). Ни в одной строке таблицы не должно быть значений внешнего ключа, для которых в текущий момент от-
сутствуют соответствующие значения родительского ключа. Другими словами, не должно быть так называемых “висячих” ссылок внешнего ключа. Если указанные требования выполняются в конкретный момент существования базы данных, то говорят, что данные находятся в со- гласованном состоянии, а сама база находится в состоянии ссылочной целостности.
4.6.4. Ограничение внешнего ключа (FOREIGN KEY). Для ре- шения вопросов поддержания ссылочной целостности в SQL исполь- зуется ограничение FOREIGN KEY. Назначение FOREIGN KEY — это ограничение допустимых значений поля множеством значений роди- тельского ключа, ссылка на который указывается при описании данно- го ограничения FOREIGN KEY.
Проблемы обеспечения ссылочной целостности возникают как при вводе значений поля, являющегося внешним ключом, так и при модификации/удалении значений родительского ключа, т. е. по- ля, на которое ссылается этот ключ. Одно из действий ограничения FOREIGN KEY — это отклонение (блокировка) ввода значений внеш- него ключа, отсутствующих в таблице с родительским ключом. Это ограничение воздействует также на возможность изменять или удалять значения родительского ключа.
Ограничение FOREIGN KEY используется в командах CREATE TABLE и ALTER TABLE при создании или модификации таблицы, содержащей поле, которое требуется объявить внешним ключом. В команде указывается имя родительского ключа, на который имеется ссылка в ограничении FOREIGN KEY.
4.6.5. Внешний ключ как ограничение таблицы. Синтаксис ограничения FOREIGN KEY имеет следующий вид:
FOREIGN KEY (<список столбцов>)
REFERENCES <родительская таблица>
[(<родительский ключ>)];
В этом предложении <список столбцов> — это список из одного или более столбцов таблицы, которые будут созданы или изменены ко- мандами CREATE TABLE или ALTER TABLE (должны быть отделены друг от друга запятыми). Параметр <родительская таблица> — это имя таблицы, содержащей родительский ключ. Это, в частности, может быть именем таблицы, которая создается или изменяется текущей командой. Параметр <родительский ключ> представляет собой спи- сок столбцов родительской таблицы, которые составляют собственно родительский ключ. Оба списка столбцов, определяющих внешний и родительский ключи, должны быть совместимы, а именно:
| • |
| • |
с соответствующими (1-м, 2-м, 3-м и т. д.) столбцами списка родительского ключа.
Создадим таблицу STUDENT с полем UNIV_ID, определенным в качестве внешнего ключа, ссылающегося на таблицу UNIVERSITY:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25),
NAME CHAR (10), STIPEND INTEGER, KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE, UNIV_ID INTEGER,
CONSTRAINT UNIV_FOR_KEY FOREIGN KEY (UNIV_ID)
REFERENCES UNIVERSITY (UNIV_ID));
Если ограничение FOREIGN KEY устанавливается в уже существу- ющей таблице с помощью оператора ALTER TABLE, то имеющиеся в этой таблице значения внешнего ключа и значения родительского клю- ча таблицы, на которую ссылается устанавливаемый внешний ключ, должны находиться в состоянии ссылочной целостности. В противном случае команда будет отклонена.
Синтаксис команды ALTER TABLE в этом случае имеет следующий вид:
ALTER TABLE <имя таблицы>
ADD CONSTRAINT <имя ограничения>
FOREIGN KEY (<список столбцов внешнего ключа>)
REFERENCES <имя родительской таблицы>
[(<список столбцов родительского ключа>)];
Например, команда
ALTER TABLE STUDENT
ADD CONSTRAINT STUD_UNIV_FOR_KEY
FOREIGN KEY (UNIV_ID)
REFERENCES UNIVERSITY (UNIV_ID);
добавляет ограничение внешнего ключа для таблицы STUDENT.
4.6.6. Внешний ключ как ограничение столбцов. Если опреде- ляемый внешний ключ не является составным, а состоит из единствен- ного столбца, то ограничение внешнего ключа может устанавливаться непосредственно в строке, описывающей этот столбец. При таком ва- рианте, называемом ссылочным ограничением столбца, ключевое сло- во FOREIGN KEY фактически не используется. Просто используется ключевое слово REFERENCES, и далее указывается имя родительского ключа, подобно следующему примеру:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25),
NAME CHAR (10), STIPEND INTEGER, KURS INTEGER, CITY CHAR (15), BIRTHDAY DATE,
UNIV_ID INTEGER REFERENCES UNIVERSITY(UNIV_ID));
Команда определяет поле STUDENT.UNIV_ID как внешний ключ, использующий в качестве родительского ключа поле UNIVERSITY.UNIV_ID, являющееся ключом таблицы UNIVERSITY.
Эта форма эквивалентна следующему ограничению таблицы
STUDENT:
FOREIGN KEY (UNIV_ID)
REGERENCES UNIVERSITY (UNIV_ID)
или, в другой записи,
CONSTRAINT UNIV_FOR_KEY FOREIGN KEY (UNIV_ID)
REFERENCES UNIVERSITY (UNIV_ID).
Если в родительской таблице у родительского ключа указано огра- ничение PRIMARY KEY, то при указании ограничения FOREIGN KEY, накладываемого на таблицу или на столбцы, можно не указывать список столбцов родительского ключа. Естественно, в случае исполь- зования ключей со многими полями порядок столбцов в соответству- ющих внешних и первичных ключах должен совпадать, и в любом случае должен быть соблюден принцип совместимости между двумя ключами.
Например, если ограничение PRIMARY KEY размещено в поле
UNIV_ID таблицы UNIVERSITY:
CREATE TABLE UNIVERSITY
(UNIV_ID INTEGER PRIMARY KEY, UNIV_NAME CHAR (10),
RATING INTEGER, CITY CHAR (15));
то в таблице STUDENT поле UNIV_ID можно использовать в качестве внешнего ключа, не указывая в ссылке имя родительского ключа:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY, SURNAME CHAR (25),
NAME CHAR (10), STIPEND INTEGER, KURS INTEGER,
CITY CHAR (15), BIRTHDAY DATE,
UNIV_ID INTEGER REFERENCES UNIVERSITY);
Такая возможность встроена в язык для обеспечения использования первичных ключей в качестве родительских.
4.6.7.Поддержание ссылочной целостности и ограничения значений родительского ключа. Поддержание ссылочной целостно- сти требует выполнения некоторых ограничений на значения, которые могут быть заданы в полях, объявленных как внешний ключ и роди- тельский ключ. Набор значений родительского ключа должен быть та- ким, чтобы каждому значению внешнего ключа в родительской таблице обязательно соответствовала одна и только одна строка, указанная со- ответствующим родительским ключом. Это означает, что родительский ключ должен быть уникальным. Следовательно, при объявлении внеш- него ключа необходимо убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY, или ограничения UNIQUE.
4.6.8.Использование первичного ключа в качестве уникаль- ного внешнего ключа. Ссылка внешних ключей только на первичные ключи считается хорошим стилем программирования SQL-запросов. В этом случае используемые внешние ключи связываются не просто с родительскими ключами, на которые они ссылаются, а с одной конкретной строкой родительской таблицы, в которой будет найдено соответствующее значение родительского ключа. Сам по себе роди- тельский ключ не обеспечивает никакой информации, которая бы не была уже представлена во внешнем ключе. Внешний ключ — это не просто связь между двумя идентичными значениями столбцов двух таблиц, но связь между двумя строками двух таблиц.
Так как назначение первичного ключа состоит именно в том, чтобы однозначно идентифицировать строку, то использование ссылки на него в качестве внешнего ключа является более логичным и более однозначным выбором для внешнего ключа. Внешний ключ, который не имеет никакой другой цели, кроме связывания строк, напоминает пер- вичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранения наглядности и простоты структуры базы данных.
4.6.9.Ограничения значений внешнего ключа. Внешний ключ может содержать только те значения, которые фактически представ- лены в родительском ключе или являются пустыми (NULL). Попытка ввести другие значения в этот ключ должна быть отклонена, поэтому объявление внешнего ключа как NOT NULL не является обязательным.
4.6.10. Действие ограничений внешнего и родительского клю- чей при использовании команд модификации. Как уже говори- лось, при использовании команд INSERT и UPDATE для модификации
значений столбца, объявленного как внешний ключ, вновь вводимые значения должны уже быть обязательно представлены в фактически присутствующих значениях столбца, объявленного родительским клю- чом. При этом можно помещать в эти поля пустые (NULL) значе- ния, несмотря на то, что значения NULL недопустимы в родительских ключах. Можно также удалять (DELETE) любые строки с внешними ключами из таблицы, в которой эти ключи объявлены.
При необходимости модификации значений родительского ключа дело обстоит иначе. Использование команды INSERT, которая осу- ществляет ввод новой записи, не вызывает никаких особенностей, при которых возможно нарушение ссылочной целостности. Однако команда UPDATE, изменяющая значение родительского ключа, и ко- манда DELETE, удаляющая строку, содержащую такой ключ, содержат возможность нарушения согласованности значений родительского и ссылающихся на него внешних ключей. Например, может возникнуть так называемая “ висячая ” ссылка внешнего ключа на несуществующее значение родительского ключа, что совершенно недопустимо. Чтобы при применении команд UPDATE и DELETE к п