Модификация объектов базы данных

С помощью языка определения данных (data definition language, DDL), входящего в состав SQL, мы можем создавать, модифицировать и уничтожать практически любые объекты, входящие в состав базы данных.

Для модификации большинства объектов базы данных используются операторы:

CREATE типобъекта имяобъекта	создание нового объекта
ALTER типобъекта имяобъекта	модификация существующего объекта
DROP типобъекта имяобъекта	уничтожение объекта базы данных

Создание таблиц. Ограничения

Для создания таблиц базы данных

CREATE TABLE [имясхемы.]имятаблицы

( {описаниестолбца | ограничениетаблицы }, …)

[ENABLE проверяемыеограничения]

[DISABLE отключаемыеограничения]

[AS запрос];

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

имястолбца типданныхстолбца [DEFAULT выражение] [ограничениястолбца]

Имя таблицы должно быть уникальным в пределах схемы, а имя столбца – в рамках таблицы. Фраза DEFAULT содержит выражение, значение которого будет вычисляться, если операторы INSERT попытаются вставить пустое значение (хотя результат вычисления этого выражения также может быть NULL). Ограничения (CONSTRAINT) позволяют осуществить дополнительные проверки при последующей вставке или модификации информации в таблице. Если при этих операциях ограничения нарушаются, соответствующая операция DML отвергается.

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

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

[CONSTRAINT имяограничения] видыограничения

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

• NOT NULL – запрещает вводить в соответствующий столбец пустые значения;

• UNIQUE – требует, чтобы значение, вводимое в столбце, было уникальным в пределах таблицы. Комбинация ограничений UNIQUE и NOT NULL является либо первичным (PRIMARY KEY), либо возможным ключом (CANDIDATE KEY). Первичный ключ может быть определен в таблице только один раз, и желательно, чтобы его значение было неизменным в течение всего времени жизни строки.

• CHECK(выражение) – проверяется указанное логическое выражение, и данные включаются только в случае его истинности.

CREATE TABLE emp ( …

sex CHAR(1) -- пол

NOT NULL CHECK(sex IN('m', 'f')),

marriage CHAR(1) -- семейное положение

DEFAULT 's' CHECK(marriage IN('s', 'm', 'd', 'w')),

birthdate DATE -- дата рождения

CHECK(birthdate > to_date('01.01.1900', 'dd.mm.yyyy')),

… );

• REFERENCES таблица1[(столбец1)] [ON UPDATE действие] [ON DELETE действие]– это ограничение дает возможность задать внешний ключ и тем самым связать несколько таблиц. Сразу следует сказать, что стандарт SQL не дает возможности явно установить связь между несколькими таблицами, как это сделано в СУБД MS Access. Вместо этого мы пользуемся ограничением REFERENCES, которое работает так: в качестве значения поля может быть взято только «поле1» из «таблицы1». «Таблицу1» в дальнейшем будем называть главной, а описываемую – подчиненной таблицей. Указав дополнительно ограничение NOT NULL, мы обеспечиваем обязательность значения этого поля.

CREATE TABLE CAREER

(JOBNO NUMBER(4) -- код должности

REFERENCES JOB(JOBNO) NOT NULL,

EMPNO NUMBER(4) -- код работника

REFERENCES EMP(EMPNO) NOT NULL,

В качестве внешнего ключа допускается использовать значения первичного ключа или поля, связанного ограничением UNIQUE в связанной таблице базы данных. Однако значения внешнего ключа сами по себе не обязаны быть уникальными; так, в таблице CAREER может быть несколько строк с одним и тем же значением поля EMPNO. Тем самым реализуется хорошо известное вам соотношение «один ко многим».

Фразы ON UPDATE и ON DELETE позволяют описать действия, которые выполняются в подчиненной таблице при попытке изменить в главной таблице значение поля, являющегося внешним ключом (т.н. родительского ключа). Стандартом SQL допускаются следующие варианты действий:

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

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

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

CREATE TABLE EMP

(EMPNO NUMBER(4) PRIMARY KEY, -- код работника

COUNTRYNO NUMBER(3), -- код страны

CITYNO NUMBER(3), -- код города

PHONENO NUMBER(8), -- телефонный номер

…

FOREIGN KEY(COUNTRYNO, CITYNO, PHONENO) REFERENCES PHONEBOOK);

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

CREATE TABLE CAREER (…

STARTDATE DATE, -- дата приема

ENDDATE DATE, -- дата увольнения

CHECK (STARTDATE < ENDDATE));

Ограничения могут отключаться в каких-то режимах. Это достигается за счет указания фраз ENABLE или DISABLE.

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