SQL оператор ALTER TABLE
В этом учебном материале вы узнаете, как использовать SQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с множеством ясных и кратких примеров).
Описание
SQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. SQL оператор ALTER TABLE также используется для переименования таблицы.
Добавить столбец в таблицу
Синтаксис
Синтаксис SQL оператора ALTER TABLE для добавления столбца в таблицу.
Пример
Давайте посмотрим на SQL ALTER TABLE пример, который добавляет столбец.
Например.
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
ALTER TABLE — ADD Column
To add a column in a table, use the following syntax:
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE — DROP COLUMN
To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE — RENAME COLUMN
To rename a column in a table, use the following syntax:
ALTER TABLE — ALTER/MODIFY DATATYPE
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:
My SQL / Oracle (prior version 10G):
Oracle 10G and later:
SQL ALTER TABLE Example
Look at the "Persons" table:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
The "Persons" table will now look like this:
| ID | LastName | FirstName | Address | City | DateOfBirth |
|---|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes | |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes | |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit format.
DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
Name already in use
databasecourse_p1 / Theory / 2_SQL / DDL.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Используйте тип serial для первичного ключа! В PostgreSQL serial — автоинкрементированный тип, т.е. он будет увеличиваться на 1 с каждой добавленной записью. Такое поведение можно сделать при помощи Integer, но тогда придётся в ручную создать последовательность. Serial не совсем тип данных, это скорее короткая запись. Поэтому в таблицах, которые ссылаются на таблицу с serial должен использоваться уже тип integer (bigserial -> bigInt соответственно).
smallint integer bigint decimal point numeric point real double smallserial serial bigserial
Date/Time: timestamp without time zone timestamp with time zone date time without time zone time with time zone interval
character: varchar(n) — текст с n — максимальным значением символов text — текст без ограничений длины
Допустим, мы хотим добавить таблицу supplier_info, в которой есть ссылка на таблицу supplier. Это можно сделать следующими способами:
- На той же строчке, что и атрибут, без упоминания атрибута с которым связываем. Удобно, когда в supplier не составной первичный ключ. (Обратите внимание, что supplier_id — integer, а не serial! Это важно)
- На той же строке, но с указанием атрибута, с которым связываем
- Внутри Create Table, но уже после указания атрибутов. Этот вариант даёт возможность самостоятельно назвать внешний ключ
Оператор CREATE служит для создания любого типа объектов, из которых состоит база данных, в том числе таблиц.
Синтаксис команды создания таблицы:
Возможные ограничения в таблицах:
- NOT NULL – значение атрибута должно быть определено (опция NOT NULL);
- UNIQUE – значения атрибутов являются уникальными (уникальный ключ);
- PRIMARY KEY – атрибут является первичным ключом (первичный ключ);
- CHECK – определяет условие, которому должны удовлетворять значения атрибута (домен);
- DEFAULT – присвоение значений «по умолчанию» для атрибутов.
Используя приведенный выше синтаксис, вместе с созданием таблицы можно вместе с атрибутами таблицы сразу же задать и ограничения декларативной и ссылочной целостности, но только в том случае, если они касаются только одного конкретного атрибута. Если же первичный, уникальный или вторичный ключ должны быть составными, для их создания нужно применять команду ALTER TABLE.
Например, для создания таблицы Students достаточно выполнить следующую команду:
Обратите внимание. Если при создании вы используете «, например «student» (именно маленькими), то название таблицы/атрибута станет чувствительным к регистру. Плюсы — можно написать зарезервированное слово, например «count». Минусы — придётся в любом запросе ещё использовать кавычки, что не особо удобно.
Создание копии таблицы
Иногда необходимо создать таблицу в точности или частично по структуре и содержанию копирующую какую-либо из существующих таблиц. В этом случае используется следующий синтаксис:
Например, для того чтобы создать копию таблицы Students можно выполнить следующую команду:
Оператор ALTER служит для изменения структуры любых объектов, из которых состоит база данных. В зависимости от типа объекта, изменяются и параметры команды ALTER.
Добавление атрибута таблицы
Для добавления атрибута к таблице применяется следующий синтаксис:
Например, для добавления к таблице Students поля n_group можно выполнить следующую команду:
Удаление атрибута таблицы
Для удаления атрибута таблицы применяется следующий синтаксис:
Изменение типа атрибута, размера типа и наличия ограничения NOT NULL
Для изменения типа данных атрибута, размера типа данных или наличия опции NOT NULL используется следующие синтаксис:
При изменении типов данных и размеров типов данных атрибутов следует соблюдать осторожность. Дело в том, что при «сужении» области допустимых значений, который может принимать атрибут, могут быть утеряны («обрезаны») данные, выходящие за пределы новой области допустимых значений.
Создание первичного ключа
Для создания первичного ключа используется следующий синтаксис:
Например, для создания первичного ключа таблицы Students можно выполнить следующую команду:
Если создаваемый первичный ключ является составным, то есть состоит из нескольких атрибутов, атрибуты перечисляются через запятую.
Создание уникального ключа
Для создания уникального ключа используется следующий синтаксис:
Например, для создания уникального ключа в таблице Students, гарантирующего уникальность значений в атрибуте Comments, можно выполнить следующую команду:
Для создания домена используется следующий синтаксис:
При наличии домена в таблице, каждый раз при изменении или добавлении значений в атрибуты, затрагиваемые условным выражением, СУБД будет проверять новое значение атрибутов на удовлетворение указанному выражению. Новое значение будет присвоено атрибуту только в том случае, если значение удовлетворяет указанному условию. При составлении условных выражений можно использовать логические операции >, <, >=, <=, <>, =, функции IN (проверка вхождения в множество), BETWEEN (проверка вхождения в диапазон), LIKE (проверка на удовлетворению шаблону). Кроме того, несколько условных выражений можно комбинировать при помощи логических операторов AND, OR и NOT.
Создание внешнего ключа
Для добавления внешнего ключа используется следующий синтаксис:
Параметры ON UPDATE и ON DELETE указываются при необходимости осуществлять каскадные действия при, соответственно, изменении или удалении значений атрибутов первичного ключа главной таблицы. При попытке изменения значения атрибута, входящего в состав первичного ключа, или удаления строки, на которую ссылаются строки из подчиненных таблиц, СУБД принимает решение либо о выполнении одного из каскадных действий, либо о запрещении SQL-оператора, изменяющего или удаляющего строку в главной таблице.
Если параметры ON UPDATE или ON DELETE равны NO ACTION или они не указаны совсем, СУБД запретит выполнение SQL-оператора, нарушающего ссылочную целостность.
Если после параметров ON UPDATE или ON DELETE стоит значение CASCADE, то произойдет каскадное изменение или удаление строк в подчиненных таблицах. Иными словами, при удалении строки из главной таблицы, будут удалены все строки в подчиненных таблицах, которые ссылались на удаляемую строку. При изменении значения первичного ключа главной таблицы и наличии параметра ON UPDATE со значением CASCADE все значения внешних ключей, ссылающихся на изменяемый атрибут, также изменят свое значение.
Использовать значение CASCADE параметров ON UPDATE и ON DELETE следует с особой осторожностью, поскольку одна ошибочная команда, например, удаления строки в одной из таблиц может повлечь за собой необратимые последствия, которые могут привести к полной неработоспособности приложений базы данных. Если все-таки необходимо выполнять каскадное изменение или удаление данных в подчиненных таблицах, рекомендуется, по возможности, реализовывать это посредством хранимых процедур.
Указание значения SET NULL для параметров ON UPDATE или ON DELETE приведет к тому, что значения атрибутов внешних ключей, ссылающихся на изменяемое значение первичного ключа, установится в NULL.
Указание значения SET DEFAULT для параметров ON UPDATE или ON DELETE приведет к тому, что значения атрибутов внешних ключей, ссылающихся на изменяемое значение первичного ключа, примут значение по умолчанию, заданное при создании таблицы.
Пример создания внешнего ключа, связывающего таблицы Students_Hobby и Students можно выполнить следующую команду:
Удаление ограничений целостности
Для удаления ограничения целостности (первичного ключа, уникального ключа или внешнего ключа) применяется следующий синтаксис:
Например, для удаления внешнего ключа связывающего таблицы Students_Hobby и Students можно выполнить следующую команду:
Оператор DROP служит для удаления объектов из базы данных. Синтаксис удаления любого типа объекта из базы данных, в том числе таблицы, выглядит следующим образом:
DROP тип объекта имя объекта;
В данном случае таблица успешно удалится только в том случае, если на нее не ссылаются внешний ключи других таблиц. В том случае, если внешние ключи существуют, но удалить таблицу все-таки необходимо, можно, либо сначала удалить все внешние ключи, ссылающиеся на удаляемую таблицу, после чего удалить таблицу, либо используя параметр CASCADE CONSTRAINTS:
В последнем случае, если внешние ключи, ссылающиеся на удаляемую таблицу, существуют, они будут удалены без изменений в полях внешних ключей подчиненных таблиц, даже если при создании внешних ключей были указаны параметры, задающие каскадные действия.