 |
Id_студента integer primary key,
|
|
|
|
ФИО CHAR(20) NOT NULL,
Специальность INTEGER CHECK (Специальность < 12),
Примечание VARCHAR);
Однако можно сначала определить домен с именем specDomain, задав для него тип данных и ограничение:
CREATE DOMAIN specDomain INTEGER
CHECK (Специальность < 12);
В определении домена, пока не связанного ни с каким столбцом какой-либо таблицы, задается тип значений домена и ограничение на эти значения.
С учетом того, что домен specDomain создан, определение таблицы Студент можно задать следующим образом:
CREATE TABLE Студент (
ID_студента INTEGER PRIMARY KEY,
ФИО CHAR(20) NOT NULL,
Специальность specDomain,
Примечание VARCHAR);
Рассмотрим еще один пример. Предположим, в нескольких таблицах вашей базы данных имеется столбец с именем Код_продукта и типом данных char(6), значения которого должны начинаться с символа 'А', 'с' или 'х’. Для таких столбцов, раз их несколько, можно предварительно создать общий домен:
CREATE DOMAIN ProdDomain CHAR (6)
CHECK (SUBSTRING (VALUE, 1, 1) IN ('A', 'C, 'X'));
После определения домена можно создать таблицу, использующую этот домен:
CREATE TABLE Товары (
Код_продукта ProdDomain,
Описание VARCHAR);
Ограничения для таблиц
Ограничения на вводимые данные можно назначить не только для отдельных столбцов, но и для таблицы в целом. Это удобно в тех случаях, когда необходимо для нескольких столбцов назначить одинаковые ограничения.
Кроме того, если первичный ключ составной (т. е. состоит из нескольких столбцов), то указать его можно только как ограничение для таблицы, а не для столбца. Все определение ограничения для таблицы указывается после определений столбцов и состоит из ключевого слова constraint, за которым следует выражение, определяющее непосредственно само ограничение.
Основные ограничения для таблицы.
|
|
|
Определение Описание
UNIQUE(списокСтолбцов) Значения в столбцах, указанных в списке, должны быть уникальными
PRIMARY KEY В каждой таблице должен быть только один первичный
(списокСтолбцов) ключ, который определен либо как ограничение для столбца, либо как ограничение для таблицы. Если первичный ключ составной, то он должен быть определен как ограничение для таблицы. В скобках указывается список столбцов, определяющих составной первичный ключ.
CHECK(условие) Позволяет производить проверку условия при вводе данных. Значение будет сохранено, если условие выполняется, в противном случае - нет. В отличие от ограничения для столбца, здесь можно использовать условия, оперирующие значениями различных столбцов таблицы.
FOREIGN KEY… Ограничения типа “внешний ключ”.
REFERENCES …
В следующем примере создается таблица студент (Фамилия, имя, Отчество, специальность, Примечание). Предполагается, что комбинация значений первых трех столбцов должна однозначно идентифицировать запись в таблице, т. е. являться первичным ключом.
CREATE TABLE Студент (
Фамилия CHAR(20),
Имя CHAR(15),
Отчество CHAR(20),
Специальность INTEGER,
Примечание VARCHAR,
CONSTRAINT PRIMARY KEY (Фамилия, Имя, Отчество)
);
Следующие два запроса на добавление записей будут выполнены, поскольку комбинации значений столбцов, определяющих первичный ключ, не содержат null и отличаются друг от друга:
INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность) VALUES ('Петров', 'Петр', 'Петрович', 5);
INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность) VALUES ('Петров', 'Петр', 'Иванович', 5);
Внешние ключи
Одна из важнейших разновидностей ограничений связана с определением внешних ключей. Внешний ключ'— это столбец или группа столбцов, соответствующих первичному ключу другой таблицы. Чтобы понять синтаксис выражения, определяющего внешний ключ, рассмотрим пример.
|
|
|
Пусть в базе данных имеются две таблицы:
v Заказы (ID_заказа, ID_клиента) — содержит сведения о том, какие заказы сделал тот или иной клиент;
v Клиенты (ID_клиента, Имя, Адрес, Телефон) — сведения о клиентах (справочник).
В таблице Клиенты столбец ID_клиента является первичным ключом, т. е. его значения отличны от null и уникальны. В таблице Заказы столбец ID_клиента не обязан иметь уникальны значения, поскольку один и тот же клиент может сделать несколько заказов. Вместе с тем любому значению столбец Заказы ID_клиента соответствует единственное значение столбца клиенты id_ клиента.
При описанных условиях столбец ID_клиента таблицы Заказы является внешним ключом, ссылающимся на первичный ключ ID_клиента таблицы Клиенты.
Внешний ключ определяется как ограничение для таблицы в выражении с ключевыми словами constrain foreign key (ограничение "внешний ключ"):
CONSTRAINT FOREIGN KEY внешнийКлюч REFERENCES внешняяТаблица (первичныйКлюч)
Здесь внешнийКлюч — имя столбца или список столбцов, разделенных запятыми, которые определяют внешний ключ, за ключевым словом references (ссылки) указывается внешняя таблица и ее первичный ключ, на который ссылается внешний ключ.
Для рассмотренного ранее примера таблицу Заказы можно определить следующим образом:
CREATE TABLE Заказы (
ID_заказа INTEGER,
ID_клиента INTEGER,
CONSTRAINT FOREIGN KEY ID_клиента REFERENCES Клиенты (ID_клиента));
Использование внешних ключей обеспечивает сохранение ссылочной целостности базы данных при изменении и удалении записей.
Если бы таблицы Заказы и Клиенты не были связаны, то при удалении записи из таблицы Клиенты в таблице Заказы могли остаться ссылки на клиента, о котором уже нет сведений. Этот факт обычно расценивается как аномалия удаления.
В случае определения в таблице Заказы внешнего ключа ID_клиента из таблицы клиенты не удастся удалить клиента, если он сделал хотя бы один заказ. Если требуется удалить из базы данных все, что касается определенного клиента, то сначала удаляются записи в таблице Заказы, а потом — в таблице Клиенты.
Аналогичная ситуация может произойти и при обновлении данных. Например, в таблице клиенты вы изменили идентификатор клиента, имеющего заказы и, таким образом, внешнему ключу теперь не на что ссылаться. Это аномалия изменения.
|
|
|
В данном случае необходимо сначала добавить новую запись в таблицу клиенты, указав в ней необходимое значение ID_клиента, затем изменить в таблице заказы все старые значения ID_клиента на то, которое вы только что ввели в новой записи таблицы клиенты, а затем удалить из таблицы клиенты запись со старым идентификатором клиента.
Чтобы в таблицах, связанных внешним ключом, не делать модификацию данных в несколько этапов, в выражении constrain foreign key можно использовать дополнительные ключевые слова:
v on delete cascade | set null (при удалении каскадировать | установить null);
v on update cascade | set null (при обновлении каскадировать | установить null).
Здесь вертикальная черта не является элементом синтаксиса, а лишь разделяет возможные варианты ключевых слов. Так, при использовании on delete cascade в случае удаления записи со значением первичного ключа, которое имеется во внешнем ключе другой таблицы, соответствующие записи удаляются автоматически из двух таблиц.
Например, при удалении из таблицы клиенты записи о клиенте, имеющем заказы, в таблице заказы также будут удалены все записи, ссылающиеся на данного клиента. Чтобы данная стратегия выполнялась, таблица заказы должна быть определена следующим образом:
CREATE TABLE Заказы (
ID_заказа INTEGER,
ID_клиента INTEGER,
CONSTRAINT FOREIGN KEY ID_клиента REFERENCES Клиенты (ID_клиента)
ON DELETE CASCADE);
Однако на практике обычно предпочитают сначала убедиться, что клиент не имеет заказов, и лишь затем вычеркнуть его из справочника. Вариант set null обычно используется при обновлении данных.
Например:
CREATE TABLE Заказы (
ID_заказа INTEGER,
ID_ клиента INTEGER,
CONSTRAINT FOREIGN KEY ID_клиента REFERENCES Клиенты (ID_клиента)
ON UPDATE SET NULL);
В данном случае при изменении (в том числе и при удалении) в таблице клиенты записи, на которую ссылается внешний ключ таблицы заказы, значения внешнего ключа устанавливаются в null. Однако этот вариант не сработает, если на столбец Заказы. ID_клиента наложено ограничение not null.
|
|
|
Обычно так и бывает, поскольку при оформлении заказа клиент должен быть обязательно указан. Поэтому, на всякий случай, лучше использовать ключевые слова on update cascade.
Примечание
Некоторые СУБД (например, PostgreSQL) допускают комбинирование дополнительных ключевых слов. Например:
|
|
|
