Определение ключей и ссылочных целостностей

Создание таблиц

Для создания таблиц используется оператор CREATE TABLE. Его формат:

CREATE TABLE <Имя_таблицы> [EXTERNAL [FILE] «<имя_файла>»]

(<опр_столбца> [, <опр_столбца> | <ограничение> ...]);

Здесь: <опр_столбца> – определение столбца БД. Определение столбца имеет такой формат:

<опр_столбца> = <имя_столбца> {<тип_данных> | COMPUTED [BY] (<выражение>) | <домен>}

[DEFAULT {<литерал> | NULL | USER}]

[NOT NULL] [<огранич_столбца>]

[COLLATE collation]

Здесь:

· <тип_данных> – тип столбца;

· <домен> – имя домена;

· DEFAULT – определяет значение, которое по умолчанию заносится в столбец при вставке новой записи;

· <огранич_столбца> – ограничения, накладываемые на значения столбца;

· COLLATE collation – определяет порядок сортировки символов (для символьных столб­цов).

Внешние и временные таблицы

С помощью спецификатора EXTERNAL [FILE] «имя_файла» можно создать внеш­нюю (т. е. расположенную вне БД) таблицу. При создании внешних таблиц все содержа­щиеся в таблице данные помещаются во внешний файл, однако сама таблица остается свя­занной с той БД, которая была активна на момент создания внешней таблицы. Сервер раз­решает вставлять в нее записи, но отказывается их изменять или удалять. При удалении таблицы оператором DROP TABLE внешний файл не уничтожается. Для внешней таб­лицы нельзя создать индексы. Основное назначение внешних таблиц – обмен данными между разными БД.

В реальной работе с БД часто возникает необходимость создания временных таблиц – для обработки статистических данных, проведения сложных сортировок и т. д. После использования временной таблицы она удаляется оператором DROP TABLE.

Вычисляемые столбцы

С помощью спецификатора COMPUTED [BY] в таблице можно создать вычисляе­мый столбец. Вычисление выражения осуществляет не клиентская программа, а сервер.

Пример. Определим столбец PRVS (пропуски «всего») в таблице PROPUSKU:

ALTER TABLE PROPUSKU

ADD PRVS COMPUTED (PRUV+PRNUV)

Здесь:

· PRUV – пропуски «уважительные»;

· PRNUV – пропуски «неуважительные».

Определение ключей и ссылочных целостностей

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

CREATE TABLE MyTable(

Col1 INT NOT NULL PRIMARY KEY,

Col2 VARCHAR(20)

);

Если в состав первичного ключа должны входить несколько столбцов, спецификатор ставится после определения всех столбцов:

CREATE TABLE MyTable(

Col1 INT NOT NULL,

Col2 VARCHAR(20) NOT NULL,

PRIMARY KEY(Col1, Col2)

);

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

Внешний ключ создается для обеспечения ссылочной целостности в дочерней таб­лице с помощью спецификатора FOREIGN KEY, который имеет такой формат:

FOREIGN KEY(<сп_столбцов_дочерней_табл>)

REFERENCES <имя_родит_табл>[<сп_столбцов_ родит_табл>]

[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

Здесь:

§ <сп_столбцов_дочерней_табл> – список столбцов дочерней таблицы, которые входят во внешний ключ;

§ <имя_родит_табл> – имя родительской таблицы;

§ <сп_столбцов_родит_табл> – список столбцов родительской таблицы, которые явля­ются ключевыми для связи таблицы (список можно опускать, если связь с родитель­ской таблицей устанавливается по первичному ключу);

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

• NO ACTION – блокировать удаление (изменение), если в дочерней таблице есть хотя бы одна запись, ссылающаяся на удаленное (измененное) значение;
• CASCADE – произвести каскадные изменения в дочерней таблице: удалить записи, ссылающиеся на удаленное значение первичного ключа, или изменить вторичный ключ в соответствии с новым значением первичного ключа;
• SET DEFAULT – установить значение вторичного ключа, заданное по умолчанию;
• SET NULL – установить значение NULL.

Пусть в БД созданы две таблицы: P – родительская и C – дочерняя.

CREATE TABLE P(

P_ID INT NOT NULL PRIMARY KEY,

P_Other INT

);

CREATE TABLE C(

C_Number INT,

C_Other INT,

FOREIGN KEY(C_Number) REFERENCES P

ON UPDATE CASCADE

ON DELETE NO ACTION

);

Сервер отслеживает ссылочную целостность этих таблиц.

	P_ID	P_Other			C_Number	C_Other
►
				►
	а)				б)

Рис. 1. Родительская (а) и дочерняя (б) таблицы до изменения первичного ключа

 

	P_ID	P_Other			C_Number	C_Other
►				►
	а)				б)

Рис. 2. Родительская (а) и дочерняя (б) таблицы после изменения первичного ключа

Удаление записи из родительской таблицы, в которой объявлена ссылочная целост­ность ON DELETE NO ACTION, возможно только в двух случаях:

§ в дочерней таблице нет записей, ссылающихся на удаляемое значение первичного ключа;

§ для родительской таблицы определен триггер BEFORE DELETE, в котором удаляются все детальные записи дочерней таблицы (триггер BEFORE DELETE срабатывает до серверной проверки ссылочной целостности):

CREATE TRIGGER BEF_DEL FOR P

BEFORE DELETE AS

BEGIN

DELETE FROM C

WHERE C_Number=OLD.P_ID;

END

Ссылочную целостность, объявляемую внешним ключом можно именовать. Для этого используется спецификатор CONSTRAINT <Имя_ссылочной_целостности>. Например:

CREATE TABLE C(

C_Number INT,

C_Other INT,

CONSTRAINT Cons_C

FOREIGN KEY(C_Number) REFERENCES P

ON UPDATE CASCADE

ON DELETE NO ACTION

);

Именование ссылочной целостности не влияет на работу сервера, однако именован­ную целостность впоследствии можно удалить без переопределения всей таблицы, сославшись не ее имя.