Alter table drop column пример аксесс. Синтаксис оператора alter table

В этом учебном пособии вы узнаете, как использовать MySQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).

Описание

MySQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. Оператор MySQL ALTER TABLE также используется для переименования таблицы.

Добавить столбец в таблицу

Синтаксис

Синтаксис добавления столбца в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name

FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, то новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу MySQL, используя оператор ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;

Этот MySQL пример ALTER TABLE добавит столбец с именем last_name в таблицу contacts . Он будет создан как столбец NOT NULL и появится в таблице после поля contact_id .

Добавить несколько столбцов в таблицу

Синтаксис

Синтаксис добавления нескольких столбцов в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition

ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
new_column_name — имя нового столбца для добавления в таблицу.
column_definition — тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу MySQL, используя оператор ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id, ADD first_name varchar(35) NULL AFTER last_name;

Этот пример ALTER TABLE добавит в таблицу contacts два столбца — last_name и first_name .

Поле last_name будет создано как столбец varchar (40) NOT NULL и появится в таблице contacts после столбца contact_id . Столбец first_name будет создан как столбец NULL varchar (35) и появится в таблице после столбца last_name .

Изменить столбец в таблице

Синтаксис

Синтаксис для изменения столбца в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name

[ FIRST | AFTER column_name ];

table_name — имя таблицы для изменения.

Пример

Рассмотрим пример, который показывает, как изменить столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts MODIFY last_name varchar(50) NULL;

Этот пример ALTER TABLE изменит столбец с именем last_name как тип данных varchar (50) и установит для столбца значения NULL.

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
column_name — имя столбца для изменения в таблице.
column_definition — измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице MySQL, используя оператор ALTER TABLE.

MySQL

ALTER TABLE contacts MODIFY last_name varchar(55) NULL AFTER contact_type, MODIFY first_name varchar(30) NOT NULL;

Этот пример ALTER TABLE будет изменять в таблице contacts два столбца — last_name и first_name .
Поле last_name будет изменено на столбец NULL varchar (55) и появится в таблице после столбца contact_type . Столбец first_name будет изменен на столбец varchar (30) NOT NULL (и не изменит позицию в определении таблицы contacts , так как не указано FIRST | AFTER).

Удаление столбца из таблицы

Синтаксис

Синтаксис для удаления столбца из таблицы в MySQL (с использованием оператора ALTER TABLE):
Например:

ALTER TABLE table_name
DROP COLUMN column_name;

table_name — имя таблицы для изменения.
column_name — имя столбца для удаления из таблицы.

Пример

Рассмотрим пример, который показывает, как удалить столбец из таблицы в MySQL с помощью оператора ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts DROP COLUMN contact_type;

Заданным образом модифицирует структуру таблицы.

Синтаксис:

ALTER TABLE TableName1 ADD | ALTER FieldName1 FieldType [(nFieldWidth [, nPrecision])] ] ] - Или - ALTER TABLE TableName1 ALTER FieldName2 ] - Или - ALTER TABLE TableName1 FieldName3] ] ] TAG TagName4 REFERENCES TableName2 ] ]

Параметры:
TableName1
Задает имя таблицы, структура которой модифицируется.
ADD FieldName1
Задает имя добавляемого поля.
ALTER FieldName1
Задает имя существующего поля, подлежащего модификации.
FieldType [(nFieldWidth [, nPrecision]])
Задает тип поля, ширину поля и точность поля (число десятичных знаков) для нового или модифицируемого поля.
FieldType это одна буква, указывающая тип данных поля. Для некоторых типов полей требуется задавать nFieldWidth и/или nPrecision .
В следующей таблице перечислены значения параметра FieldType ; для каждого из них указывается, требуются ли параметры nFieldWidth и nPrecision .

FieldType nFieldWidth nPrecision Описание C n - Символьное поле ширины n D - - Дата T - - Поле типа DateTime N n d Числовое поле ширины n, имеющее d десятичных знаков F n d Поле данных с плавающей запятой ширины n, имеющее d десятичных знаков I - - Поле целых чисел B - d Поле данных с двойной точностью Y - - Поле типа Currency L - - Логическое M - - Поле типа Memo G - - Поле типа General

Параметры nFieldWidth и nPrecision игнорируются в случае типов D, T, I, Y, L, M, G и P. Если для типа N, F или B значение nPrecision не указано, оно принимается равным нулю по умолчанию (нет десятичных знаков).
NULL
Разрешает полю принимать значения NULL .
NOT NULL
Запрещает полю принимать значения NULL .
Если опущены предложения NULL и NOT NULL , то допустимость значений NULL для поля определяется текущей установкой SET NULL . Однако если опустить предложения NULL и NOT NULL и задать PRIMARY KEY или UNIQUE , текущая установка SET NULL будет игнорироваться и по умолчанию для поля будет установлено NOT NULL .
CHECK lExpression1
Задает правило проверки достоверности уровня поля. Вычисление lExpression1 должно давать логическое выражение; это может быть функция, определенная пользователем, или хранимая процедура. Учтите, что это правило проверяется при добавлении пустой записи. Если данное правило не допускает пустые значения полей в присоединяемой записи, генерируется ошибка.
ERROR cMessageText1
Задает текст сообщения об ошибке, отображаемого при обнаружении ошибки в соответствии с правилом поля. Это сообщение выдается только при изменении данных в окне просмотра или окне редактирования.
DEFAULT eExpression1
Задает для поля значение, принимаемое по умолчанию. Тип данных выражения eExpression1 должен совпадать с типом данных поля.
PRIMARY KEY
Создает тег первичного индекса. Имя тега совпадает с именем поля.
UNIQUE
Создает тег индекса-кандидата с тем же именем, которое носит поле. Подробнее об индексах-кандидатах см. главу 7 "Работа с таблицами" Руководства разработчика.
Замечание Индексы-кандидаты (созданные с помощью опции UNIQUE , включенной в команды ALTER TABLE и CREATE TABLE для совместимости с ANSI ) отличаются от индексов, созданных с помощью опции UNIQUE команды INDEX . Индекс, созданный опцией UNIQUE в команде INDEX , допускает дублирование ключей индекса; в индексах-кандидатах повторяющиеся ключи не разрешены.
В поле, используемом для первичного индекса или индекса-кандидата, не допускаются значения NULL и повторяющиеся записи.
Если вы создаете новое поле с помощью предложения ADD COLUMN, то при создании первичного индекса или индекса-кандидата для поля, поддерживающего значения NULL, Visual FoxPro не будет генерировать ошибку. Однако если вы попытаетесь ввести значение NULL или дубликат в поле, используемое для первичного индекса или индекса-кандидадата, Visual FoxPro выдаст ошибку.
Если вы модифицируете существующее поле, а выражение первичного индекса или индекса-кандидата состоит только из имени одного этого поля, Visual FoxPro проверит поле не содержит ли оно значений NULL или повторяющихся записей. Если такие значения будут обнаружены, Visual FoxPro сгенерирует ошибку и таблица остается без изменений. Если выражение индекса содержит несколько полей или выражение, включающее одиночное поле, Visual FoxPro не будет проверять наличие значений NULL или дублирующих записей.
REFERENCES TableName2 TAG TagName1
Задает родительскую таблицу, с которой устанавливается постоянное отношение. TAG TagName1 задает тег индекса родительской таблицы, на котором базируется данное отношение. Имя тега индекса может содержать не более 10 символов.
NOCPTRANS
Запрещает для символьных и memo-полей трансляцию в другую кодовую страницу. Если таблица преобразуется в другую кодовую таблицу, поля с опцией NOCPTRANS не участвуют в трансляции. NOCPTRANS можно задать только для символьных полей и memo-полей.
В следующем примере создается таблица с именем MYTABLE , которая содержит два символьных поля и два memo-поля. Второе символьное поле char2 и второе memo-поле memo2 определены с опцией NOCPTRANS для предотвращения трансляции.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,; memo1 M, memo2, M NOCPTRANS)

ALTER FieldName2
Задает имя существующего поля, которое предстоит модифицировать.
SET DEFAULT eExpression2
Задает новое значение, которое будет приниматься по умолчанию для существующего поля. Тип данных выражения eExpression2 должен совпадать с типом данных, определенным для поля.
SET CHECK lExpression2
Задает новое правило для существующего поля. Вычисление lExpression2 должно давать логическое значение; это может быть определенная пользователем функция или хранимая процедура.
ERROR cMessageText2
Задает текст сообщения об ошибке. Это сообщение будет отображаться при обнаружении ошибки в соответствии с правилом уровня поля. Сообщение выдается только в том случае, когда происходит изменение данных в окне просмотра или окне редактирования.
DROP DEFAULT
Отменяет значение, принимаемое по умолчанию для существующего поля.
DROP CHECK
Отменяет правило для существующего поля.
DROP FieldName3
Задает поле, которое нужно удалить из таблицы. При удалении поля из таблицы также отменяется значение, принимаемое по умолчанию для этого поля.
Если для поля указывает выражение ключа индекса или выражение триггера, то после удаления поля эти выражения становятся недействительными. В таком случае при удалении поля не будет генерироваться ошибка, однако ошибки возникнут для недействительных выражений ключа индекса или триггера на этапе выполнения.
SET CHECK lExpression3
Задает правило уровня таблицы. Вычисление lExpression3 должно давать логическое выражение, это может быть определенная пользователем функция или хранимая процедура.
ERROR cMessageText3
Задает текст сообщения об ошибке, отображаемого при обнаружении ошибки в соответствии с правилом уровня таблицы. Это сообщение выдается только при изменении данных в окне просмотра или окне редактирования.
DROP CHECK
Отменяет правило уровня таблицы.
ADD PRIMARY KEY eExpression3 TAG TagName2
Добавляет в таблицу первичный индекс. eExpression3 задает выражение ключа первичного индекса, а TagName2 имя тега первичного индекса. Имя тега индекса может содержать не более 10 символов. Если TAG TagName2 опущено, а eExpression3 определяет одно поле, тег первичного индекса получает то же имя, что и поле, заданное в выражении eExpression3 .
DROP PRIMARY KEY
Удаляет первичный индекс и его тег. Поскольку таблица может иметь только один первичный ключ, его имя можно не указывать. При удалении первичного индекса удаляются и все постоянные отношения, основанные на первичном ключе.
ADD UNIQUE eExpression4
Добавляет в таблицу индекс-кандидат. eExpression4 задает выражение ключа индекса-кандидата, а TagName3 имя тега индекса-кандидата. Имя тега индекса может содержать не более 10 символов. Если выражение TAG TagName3 опущено, а eExpression4 определяет одно поле, тег индекса-кандидата получает то же имя, что и поле, заданное в выражении eExpression4 .
DROP UNIQUE TAG TagName4
Удаляет индекс-кандидат и его тег. Поскольку таблица может иметь несколько ключей-кандидатов, необходимо задать имя удаляемого тега индекса-кандидата.
ADD FOREIGN KEY TAG TagName4
Добавляет в таблицу внешний (отличный от первичного) индекс. eExpression5 задает выражение ключа внешнего индекса, а TagName4 имя тега внешнего индекса. Имя тега индекса может содержать не более 10 символов.
REFERENCES TableName2
Задает родительскую таблицу, с которой устанавливается постоянное отношение. Чтобы установить отношение с родительской таблицей на базе тега существующего индекса, следует указать TAG TagName5 . Имя тега индекса может содержать не более 10 символов. Если TAG TagName5 опущено, отношение устанавливается с помощью тега первичного индекса родительской таблицы.
DROP FOREIGN KEY TAG TagName6
Удаляет внешний ключ, у которого тег индекса имеет имя TagName6. Если опустить SAVE , тег индекса будет удален из структурного индекса. Включив SAVE , вы предотвратите удаление тега из структурного индекса.
RENAME COLUMN FieldName4 TO FieldName5
Позволяет изменять имя поля в таблице. FieldName4 задает имя поля, которое следует изменить. FieldName5 задает новое имя поля.
Внимание Будьте осторожны при переименовании полей таблиц выражения индекса, правила проверки достоверности полей и таблиц, команды, функции и т.п. могут продолжать ссылаться на старые имена полей.
NOVALIDATE
Указывает, что Visual FoxPro допускает внесение изменений в структуру таблицы, которые могут нарушить целостность табличных данных. По умолчанию Visual FoxPro запрещает команде ALTER TABLE вносить в структуру подобные разрушающие изменения. Включение опции NOVALIDATE позволяет снять этот запрет.

Н а этом уроке SQL мы рассмотрим, как использовать команду команду ALTER к таблице в базе данных. Команда ALTER в основном используется для добавления, изменения и удаление столбцов, к индексам и ограничениям на таблицы в реляционной базе данных, например в , Oracle, Sybase и SQL Server. Хотя ALTER не является частью классической операции CRUD, это одна из важных команд DDL. Одним из наиболее частого использования команды ALTER в SQL является добавление и удаление индексов для повышения производительности запросов SQL SELECT. В этом обучающей статье мы увидим различные способы использования команды ALTER. С помощью команды ALTER можно добавлять, редактировать или изменять таблицы, представления в базы данных. Мы можем добавить новый столбец в таблице, мы можем изменить значение столбца или мы можем переименовать столбец.

10 Примеров команды ALTER в SQL

Давайте посмотрим некоторые из наиболее часто используемых примеров команды ALTER в SQL. В этом разделе мы рассмотрим, как использовать команду ALTER для добавления, изменения и удаления существующих столбцов в таблице.

Пример 1: Как добавить столбец в существующую таблицу с помощью ALTER в SQL:

Предположим, у нас есть одна таблица с именем студента с полем roll_number, stu_name, class, subject , и мы хотим добавить еще один столбец marks-obtained, как будет делать с помощью команды ALTER.

ALTER TABLE Student ADD marks_obtained Number (3);

Когда это поле добавляется в таблицу он будет содержать нулевое значение по умолчанию.

Пример 2: Как изменить существующий столбец с помощью команды ALTER в SQL :

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

ALTER TABLE Student ALTER COLUMN marks_obtained DECIMAL (5, 2);

Пример 3: Как удалить существующий столбец из таблицы с помощью команды ALTER в SQL:

Теперь, если мы хотим, удалить существующий столбец из таблицы мы используем DROP вместе с командой ALTER

ALTER TABLE Student DROP column marks_obtained;

Пример 4. Как удалить первичный ключ с помощью команды ALTER в базе данных :

Предположим, существует первичный ключ с именем ‘pk_roll_num’ для столбца roll_number из таблицы Student, и мы хотим отказаться от этого ограничения

ALTER TABLE Student DROP CONSTRAINT "pk_roll_num’;

Пример 5. Как добавить ограничения первичного ключа с помощью команды ALTER в SQL:

Теперь мы хотим добавить ограничение первичного ключа в таблице Student под названием ‘pk_roll_num’ для столбца roll_number ‘. Мы делаем так

ALTER TABLE Student ADD Constraint "pk_roll_num PRIMARY KEY(roll_number)

Пример 6. Как сбросить ограничение внешнего ключа с помощью команды ALTER :

Предположим, что существует ограничение внешнего ключа с именем ‘fk_roll_num’ для столбца roll_number ‘ из таблицы ‘Marks’, и мы хотим отказаться от этого ограничения

ALTER TABLE Marks DROP CONSTRAINT "fk_roll_num’;

Пример 7: Как добавить ограничения внешнего ключа с помощью команды ALTER

Теперь мы хотим добавить ограничение внешнего ключа в таблицу Marks, названный ‘fk_roll_num’ для столбца roll_number “. Мы сделаем так:

ALTER TABLE Marks ADD Constraint "fk_roll_num FOREIGN KEY (roll_number) REFERENCES Students (roll_number);

Пример 8: Как добавить уникальные ключевые ограничения при помощи команды ALTER

Теперь, если мы хотим добавить уникальный ключ ограничения в таблицу Student под названием ‘unique_roll_no’ для столбца roll_number. Мы сделаем так:

ALTER TABLE Student ADD CONSTRAINT unique_roll_no UNIQUE (roll_Number);

Пример 9 : Как удалить уникальный ключ Constraint с помощью команды ALTER

Теперь, если мы хотим отказаться от уникального ключа ограничения в таблице Student под названием ‘unique_roll_no’ для столбца roll_number. Мы сделаем так:

ALTER TABLE Student DROP CONSTRAINT unique_roll_no);

Пример 10: Как добавить СНЕСК с помощью команды ALTER в SQL

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

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

Это все о примерах команды ALTER в SQL. Мы видели 10 различных способов использования команды ALTER в SQL. ALTER является одним из самых важных во время работы в любой базе данных, например, MySQL, Oracle или SQL Server, особенно из командной строки. Так как нам часто нужно добавлять, изменять и удалять столбцы, индексы и ограничения в таблице.

Если в Вашем проекте есть таблицы размер которых исчисляется гигабайтами, а для того чтобы поменять структуру такой таблицы вам на несколько часов приходится останавливать все сервисы - эта статья будет для Вас.

Дано: таблица размером в несколько десятков гигабайт данных. Задача - изменить структуру таблицы.

Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте - «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.

Предположим что структура нашей таблицы такая:

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
Мы хотим добавить в эту таблицу поле last_login.

Какие у нас есть варианты.

В лоб

Включаем мозг

И все таки - нужно добавить поле

Мой способ заключается в следующем

Итак, подготавливаем 2 таблицы - первая с нужной структурой, вторая для логирования изменений.
CREATE TABLE `_users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", `lastvisit` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` (`id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT "0", `action` enum("updated","deleted") NOT NULL DEFAULT "updated", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ставим триггеры:
DELIMITER ;; CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END;; CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF (OLD.id != NEW.id) THEN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END IF; INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; DELIMITER ;
Теперь начинаем переливку. Для этого надо открыть 2 соединения с базой. В одном будет идти содственно переливка, в другом нужно будет ненадолго заблокировать таблицу на запись.
mysql> LOCK TABLES users WRITE; Query OK, 0 rows affected (0.00 sec) mysql> -- триггеры уже должны быть установлены mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.17 sec) mysql> -- в другой консоли пускаем переливку mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users; mysql> -- снова в первой консоли mysql> UNLOCK TABLES;
Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного - скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;

Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:
mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.16 sec) mysql> RENAME TABLE users TO __users, _users TO users; Query OK, 0 rows affected (0.11 sec)
Стоит заметит что в этот момент возможна небольшая потеря данных т.к. запросы выполняются не атомарно. Если это критично, лучше выключить сервис на некоторое время чтобы запросов на изменение не было. Можно например забрать права на запись у пользователя а выполнять команды под другим пользователем.

Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо
mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;
нужно перелить через mysqldump:
$ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2
Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.

Кстати, велосипед уже изобретен Facebook и называется

Манипулирование таблицами - один из самых распространенных видов деятельности, который администраторы баз данных и программисты осуществляют при работе с объектами баз. В этом разделе подробно описывается, как нужно создавать и изменять таблицы.

Стандарт ANSI является чем-то вроде наименьшего общего знаменателя для всех производителей, хотя не каждая возможность стандартной версии инструкций CREATE TABLE и ALTER TABLE реализуется каждым производителем. Тем не менее стандарт ANSI представляет собой базовую форму, которую можно использовать на всех платформах.

В свою очередь, платформы предлагают разнообразные расширения и дополнения к инструкциям CREATE TABLE и ALTER TABLE стандарта ANSI.

Как правило, нужно как следует продумать проект таблицы и способ ее создания. Этот процесс называется проектированием базы данных. Процесс анализа взаимоотношений таблицы с ее собственными данными и другими таблицами в базе данных называется нормализацией. Мы рекомендуем программистам и администраторам баз данных изучить принципы проектирования и нормализации перед тем, как использовать команды CREATE DATABASE.

Синтаксис SQL 2003

При выполнении инструкции CREATE TABLE стандарта SQL 2003 в базе данных создается постоянная или временная таблица. Синтаксис следующий.

CREATE [{LOCAL TEMPORARY) GLOBAL TEMPORARY}] TABLE имя_таблицы (имя_столбца тип_данных атрибуты [, …]) | [имя_столбца WITH OPTIONS опции] | | {SYSTEM GENERATED | USER GENERATED | DERIVED}] [, …]] [определение_таблицы]] имя_таблицы тип_данных атрибуты] | имя_столбца SET DEFAULT значение_по_умолчанию] | имя_столбца DROP DEFAULT] | имя_столбца ADD SCOPE имя_таблицы | имя_столбца DROP SCOPE {RESTRICT | CASCADE}] | имя_столбца {RESTRICT | CASCADE}] | |

Ключевые слова

TEMPORARY

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

(имя_столбца тип_данных атрибуты [,])

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

имя_столбца

Указывается имя столбца. Оно должно представлять собой идентификатор, допустимый с точки зрения правил конкретной СУРБД. Имя должно быть осмысленным!

тип_данных

Связывает со столбцом с именем имя_столбца определенный тип данных. Для тех типов данных, которые позволяют указывать их длину, существует дополнительный параметр длина, например VARCHAR(255). Тип данных должен быть допустимым в СУРБД. За полным описанием допустимых типов данных и их вариантов у конкретных производителей обращайтесь к главе 2. атрибуты

Связывает со столбцом указанные атрибуты-ограничения. Для одного столбца с именем имя_столбца можно указывать несколько атрибутов. Запятые не требуются. К типичным атрибутам ANSI относятся следующие.

NOT NULL

В столбце запрещаются значения NULL (или разрешаются, если предложение NOT NULL опущено). Любые инструкции INSERT и UPDATE, которые попытаются поместить значение NULL, в столбец с атрибутом NOT NULL не будут выполнены, и произойдет откат.

DEFAULT выражение

Столбец будет использовать значение выражения, если инструкция INSERT или UPDATE не вводит никакого значения. Выражение должно быть допустимым для типа данных столбца; например, в столбце типа INTEGER нельзя использовать никакие буквенные символы. Выражение может представлять собой строку или числовой литерал, но вы также можете указать пользовательскую или системную функцию. Стандарт SQL 2003 позволяет использовать в предложении DEFAULT следующие системные функции: NULL, USER, CURRENTJJSER, SESSION_USER, SYSTEMJJSER, CURRENT_PATH, CURRENT_D А ТЕ, CURRENTJIME, LOCALTIME, CURRENTJIMESTAMP, LOCALTJMESTAMP, ARRAY или ARRAY.

COLLATE имя_сопоставления

Определяется используемое сопоставление (collation), то есть порядок сортировки в соответствующем столбце. Имя сопоставления зависит от платформы. Если имя сопоставления не определяется, по умолчанию принимается сопоставление по набору символов, используемому в столбце. REFERENCES ARE CHECKED Параметр определяет, будут ли проверяться ссылки в столбце REF, определенном с опцией области действия (scope). Дополнительное предложение ON DELETE определяет, будут ли значения в записях, на которые ссылалась удаленная запись, устанавливаться в NULL или же на выполнение операции будет наложено ограничение.

CONSTRAINT имя ограничения [тип_ограничения [ограничение]]

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

имя_столбца

Столбец определяется со специальными опциями, такими, как опция области действия (scope), опция значения по умолчанию (default), ограничением уровня столбца или предложением COLLATE. Во многих реализациях предложение WITH OPTIONS ограничивается созданием объектных (typed) таблиц.

LIKE имя_таблицы

Новая таблица создается с теми же определениями столбцов, что и в существующей таблице имя_таблицы.

REFIS имя_столбца {SYSTEM GENERATED | USER GENERATED DERIVED]

Определяется столбец объектных идентификаторов (object identifier, OID) в объектных (typed) таблицах. Объектный идентификатор является необходимым для таблицы, являющейся корневой в иерархии таблиц. В соответствии с этим параметром столбец REF может генерироваться системой автоматически (SYSTEM GENERATED), вручную указываться пользователем при вводе строки (USER GENERATED) или создаваться на основе другого столбца REF (DERIVED). Параметр требует включать в столбец имя_стол6ца атрибут REFERENCES.

CONSTRAINT тип ограничения [имя ограничения] [, …]

Таблице присваивается одно или несколько ограничений. Этот параметр заметно отличается от ограничений уровня столбца, поскольку предполагается, что ограничения уровня столбца применяются только к столбцу, с которым они связаны. В случае ограничений уровня таблицы существует возможность связать с ограничением несколько столбцов. Например, в таблице продаж вам может понадобиться объявить уникальное ограничение на сцепленный ключ store_id, order_id и order_date. Сделать это можно только при помощи ограничения уровня таблицы. За подробным обсуждением ограничений обращайтесь к главе 2.

OF имя_типа [определение_таблицы]

Объявляется, что таблица основывается на готовом пользовательском типе. В этой ситуации таблица может иметь только один столбец для каждого атрибута структурированного типа плюс дополнительный столбец, определенный в предложении REF IS. Тип данных REF подробно описывается в разделе «Инструкция CREATE/ALTER ТУРЕ». Это предложение несовместимо с предложением LIKE имя_таблицы. Где:

UNDER супертаблица [определение/таблицы]

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

ON COMMIT {PRESERVE ROWS DELETE ROWS]

Предложение ON COMMIT PRESERVE ROWS сохраняет строки данных временной таблицы при выполнении инструкции COMMIT. Предложение ON COMMIT DELETE RO WS удаляет все строки данных во временной таблице при выполнении инструкции COMMIT.

ADD имя_столбца тип_данных атрибуты

В таблицу добавляется столбец с соответствующим типом данных и атрибутами.

ALTER имя_столбца SET DEFAULT значение_по_умолчанию

В столбец добавляется значение по умолчанию (если оно не существует) или изменяется существующее значение.

ALTER имя_столбца DROP DEFAULT

Значение по умолчанию полностью удаляется из указанного столбца.

ALTER имя_столбца ADD SCOPE имя_таблицы

В указанный столбец добавляется область действия (scope). Область действия представляет собой ссылку на пользовательский тип данных.

ALTER имя_столбца DROP SCOPE

Область действия удаляется из указанного столбца. Предложения RESTRICT и CASCADE объясняются в конце данного списка.

DROP COLUMN имя_столбца

Указанный столбец удаляется из таблицы. Предложения RESTRICT и CASCADE объясняются в конце данного списка.

ADD табличное_ограничение

В таблицу добавляется ограничение с указанным именем и характеристиками.

DROP CONSTRAINT имя ограничения

Существующее ограничение удаляется из таблицы.

RESTRICT

При указании этого предложения СУРБД отменяет команду, если находит в базе данных объекты, зависящие от данного объекта.

При указании этого предложения СУРБД удаляет все прочие объекты, зависящие от данного объекта.