SQL ALTER TABLE - изменение таблицы базы данных

Навигация по уроку

ALTER TABLE для добавления и удаления столбца, значения по умолчанию
ALTER TABLE для добавления ключей
ALTER TABLE для добавления и удаления ограничений

Связанные темы

Команда языка SQL ALTER TABLE предназначена для изменения структуры столбца таблицы базы данных, если таблица уже существует.

ALTER TABLE для добавления и удаления столбца, значения по умолчанию

Запрос с командой ALTER TABLE на добавление нового столбца в таблицу имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD ИМЯ_НОВОГО_СТОЛБЦА ТИП_ДАННЫХ

Пример 1. Есть база данных фирмы - Company. В ней есть таблица Employee (Сотрудник). Требуется добавить в эту таблицу новый столбец - SEX (Пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ADD SEX CHAR(1)

Таким образом в таблице появился новый столбец, значения которого могут принимать символьный тип и состоять из одного символа.

Пусть теперь требуется установить значение записей в таблице по умолчанию. Запрос с командой ALTER TABLE для этой цели содержит ещё одну команду - ALTER COLUMN - и имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ALTER COLUMN ИМЯ_СТОЛБЦА SET DEFAULT 'ЗНАЧЕНИЕ_ПО_УМОЛЧАНИЮ'

Пример 2. Пусть в фирме, база данных которой имеет название Company, трудятся в основном женщины. Администратор базы данных решил сократить процесс заполнения данными столбца SEX (Пол) и установить его значение по умолчанию - 'F' (что означает женский пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ALTER COLUMN SEX SET DEFAULT 'F'

Теперь только если новый сотрудник - мужчина, потребуется вписывать в столбец SEX значение 'M', а в большинстве случаев значение установится по умолчанию - 'F'.

При помощи запроса с командой ALTER TABLE можно и удалить столбец из таблицы. Синтаксис команды будет следующим:

ALTER TABLE ИМЯ_ТАБЛИЦЫ DROP COLUMN ИМЯ_СТОЛБЦА

Пример 3. Пусть в той же базе данных Company из таблицы Employee требуется удалить столбец SEX (Пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE DROP COLUMN SEX

ALTER TABLE для добавления ключей

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

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА)

Синтаксис команды в случае составного первичного ключа:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА_1, ИМЯ_ИДЕНТИФИКАТОРА_2)

Пример 4. Работаем всё с той же базой данных Company, со столбцом EMPLOYEE. Мы можем добавить простой первичный ключ этого столбца в виде идентификатора сотрудника и сделать это так:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID)

Если решено использовать составной ключ, состоящий из идентификатора сотрудника и идентификатора отдела, в котором сотрудник работает, то используем следующий запрос:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID, DEPT_ID)

ALTER TABLE для добавления и удаления ограничений

О том, что такое ограничения целостности, говорилось на уроке Реляционная модель данных. Если коротко - при удалении какой-либо строки из одной таблицы (1), в другой таблице (2), строка в которой ссылается на строку таблицы (1), не должно быть ссылок, которые не содержатся в таблице (1), то есть ссылок, ведущих в никуда.

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

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE SET NULL

Пример 5. Пусть в той же базе данных Company требуется установить ссылочное ограничение: при удалении какого-либо отдела из таблицы Dept в таблице Employee, содержатся ссылки на эти отделы, значение ссылки (внешнего ключа) должно быть уставлено в NULL (полностью неопределенное). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ADD CONSTRAINT DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT ON DELETE SET NULL

Таким образом, если в таблице Employee есть сотрудник, работающий в отделе, который вдруг был ликвидирован и запись о нём удалена из таблицы Dept, то в записи об этом сотруднике значение столбца DEPT_ID примет значение NULL.

Запросом с командой ALTER TABLE можно также установить запрет на удаление ссылающихся записей из таблицы (1), если существует хотя бы одна запись в таблице (2), на которую ссылается запись в таблице (1). Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE RESTRICT

Пример 6. Далее работаем с базой данных сети аптек, которую мы создали на уроке Создание базы данных SQL и таблиц базы данных.

Следующая команда с выражением alter table устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:

alter table Preparation add constraint GR_ID foreign key(GR_ID) references Group on delete restrict

Устанавливаем запрет на удаление аптеки из таблицы Pharmacy, если существует хотя бы один сотрудник этой аптеки, что определяется записью в таблице Employee:

alter table Employee add constraint PH_ID foreign key(PH_ID) references Pharmacy on delete restrict

Запросом с командой ALTER TABLE можно также определить, что если удалить запись в таблице (2), на которую ссылается запись в таблице (1), то должны быть удалены все соответствующие записи в таблице (1) (каскадное удаление). Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE CASCADE

Пример 7. Продолжаем работать с базой данных сети аптек.

Вновь изменяем таблицу AVAILABILITY и определяем, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице AVAILABILITY.

alter table AVAILABILITY add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:

alter table AVAILABILITY add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:

alter table DEFICIT add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

iv class="even">

Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:

alter table DEFICIT add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Запросом с командой ALTER TABLE можно установить проверочное ограничение. Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD CONSTRAINT CHECK_DATA CHECK(ПРОВЕРЯЕМОЕ_УСЛОВИЕ)

Пример 8. Продолжаем работать с базой данных сети аптек.

Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должно быть меньше значения DateEnd.

ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart < DateEnd)

Изменяем таблицу DEFICIT и определяем, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd:

ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart < DateEnd)

Поделиться с друзьями

Назад<<<

Содержание

Вперёд>>>