Модифікація таблиць. Оператор ALTER TABLE

Перед вивченням даної теми рекомендується ознайомитись з наступною темою:

• Створення таблиць. Оператор CREATE TABLE

---

Зміст

• 1. Модифікація таблиці. Оператор ALTER TABLE. Особливості застосування
• 2. Зміна параметрів існуючого поля. Комбінація ключових слів ALTER COLUMN
• 3. Приклад зміни типу поля (стовпця) таблиці та задавання нових обмежень. ALTER TABLE + ALTER COLUMN
• 4. Додавання нових полів до таблиці. Ключове слово ADD
• 5. Приклад додавання полів до таблиці
• 6. Видалення полів. Ключові слова DROP COLUMN
• 7. Приклад створення таблиці, додавання нового та видалення існуючого поля з таблиці (ALTER TABLE + ADD, ALTER TABLE + DROP COLUMN)
• 8. Видалення декількох полів (ALTER TABLE + DROP COLUMN)
• 9. Приклад зміни обмежень в заданому полі таблиці. Поєднання ALTER TABLE + ADD + DROP
• 10. Приклад зміни назви поля. Комбінація операторів ALTER TABLE + DROP, ALTER TABLE + ADD
• Споріднені теми

---

Пошук на інших ресурсах:

1. Модифікація таблиці. Оператор ALTER TABLE. Особливості застосування

Оператор ALTER TABLE призначений для модифікації таблиці, яка була раніше створена. Оператор дозволяє виконувати наступні базові операції:

• додавати нові поля до таблиці;
• видаляти поля з таблиці;
• змінювати тип даних полів;
• додавати та видаляти обмеження на поля;
• інше.

Оператор має широкий спектр можливостей. У даній темі розглядаються тільки деякіосновні з них, а саме:

• додавання нових полів. Це забезпечується з допомогою ключового слова ADD;
• видалення полів. Тут використовується ключове слово DROP у поєднанні з словом COLUMN;
• редагування полів. У цьому випадку застосовується комбінація слів ALTER COLUMN.

  ⇑

2. Зміна параметрів існуючого поля. Комбінація ключових слів ALTER COLUMN

Щоб змінити тип поля, використовується наступний спрощений синтаксис

ALTER TABLE [Table_Name]
    ALTER COLUMN Column_Name New_Type NewRestrictions

тут

• Table_Name – ім’я таблиці, яка була створена раніше;
• Column_Name – назва поля таблиці Table_Name, яке було створене раніше;
• New_Type – назва типу, що призначається полю Column_Name;
• New_Restrictions – обмеження, що накладаються на поле. Тут можна задавати нові обмеження, які раніше не були накладені.

Рекомендовано, щоб на момент зміни, таблиця не містила записів.

  ⇑

3. Приклад зміни типу поля (стовпця) таблиці та задавання нових обмежень. ALTER TABLE + ALTER COLUMN

У прикладі створюється таблиця з полями A, B, C, D. Потім з допомогою оператора ALTER TABLE тип поля C змінюється з типу VARCHAR(20) на тип DECIMAL. Також задається обмеження NOT NULL.

/* Створити таблицю */
CREATE TABLE [MyTable] (
    A INT NOT NULL PRIMARY KEY,
    B FLOAT,
    C VARCHAR(20),
    D DATE
);

/*
Результат.
-------------------------------------
|  A  |   B   |      C      |   D   |
-------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
-------------------------------------
*/

GO

/* Змінити тип колонки C на DECIMAL та додати обмеження NOT NULL */
ALTER TABLE [MyTable]
    ALTER COLUMN C DECIMAL NOT NULL

/*
Результат.
---------------------------------
|  A  |   B   |    C    |   D   |
---------------------------------
| INT | FLOAT | DECIMAL |  DATE |
---------------------------------
*/

  ⇑

4. Додавання нових полів до таблиці. Ключове слово ADD

Додавання нового поля з допомогою оператора ALTER TABLE реалізується з використанням ключового слова ADD. У цьому випадку спрощена загальна форма оператора має вигляд

ALTER TABLE [Table_Name]
    ADD
        Field1 Type1,
        Field2 Type2,
        ...
        FieldN TypeN

тут

• Table_Name – ім’я таблиці, яка створюється;
• Field1, Field2, FieldN – імена полів, які додаються. Ці імена не повинні повторювати вже існуючі імена таблиці. Іншими словами, імена полів у таблиці повинні бути унікальними;
• Type1, Type2, TypeN – відповідно типи полів Field1, Field2, FieldN.

Якщо потрібно додати одне поле, то синтаксис оператора наступний

ALTER TABLE [Table_Name]
    ADD
        Field Type

тут

• Field – ім’я поля, яке додається;
• Type – тип поля, який додається.

Цей випадок також передбачає додавання обмежень на поля. У цьому випадку, після типу Type вказується одне або декілька обмежень (NOT NULL, NULL та інші).

  ⇑

5. Приклад додавання полів до таблиці

У прикладі з допомогою команди CREATE TABLE формуються поля з такими назвами та їх типами:

• поле A типу Int. Це поле має обмеження NOT NULL та PRIMARY KEY;
• поле B типу Float.

Потім до цих полів додаються наступні поля з допомогою команди ALTER TABLE з ключовим словом ADD:

• поле C типу Varchar(20);
• поле D типу Date.

 

/* Створити таблицю з іменем MyTable.
   Додати стовпці A, B
*/
CREATE TABLE [MyTable] (
    A INT NOT NULL PRIMARY KEY,
    B FLOAT
);

GO

/* Результат:
---------------
|  A  |   B   |
---------------
| INT | FLOAT |
---------------
*/

/* Додати стовпці C, D */
ALTER TABLE [MyTable]
    ADD
        C VARCHAR(20),
        D DATE

/* Результат:
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

  ⇑

6. Видалення полів. Ключові слова DROP COLUMN

Для видалення полів з таблиці використовується оператор ALTER TABLE у поєднанні з ключовими словами DROP COLUMN. У цьому випадку спрощена загальна форма оператора наступна

ALTER TABLE [Table_Name]
    DROP COLUMN
        Field1,
        Field2,
        ...
        FieldN

тут

• Table_Name – ім’я таблиці;
• Column1, Column2, ColumnN – імена полів (стовпців) таблиці, які потрібно видалити. Якщо задати неіснуючі поля, то буде виведено повідомлення про помилку.

Якщо з таблиці потрібно видалити тільки одне поле, то вигляд оператора ALTER TABLE буде наступний

ALTER TABLE [Table_Name]
    DROP COLUMN
        Field

тут

• Field – ім’я поля, яке видаляється.

  ⇑

7. Приклад створення таблиці, додавання нового та видалення існуючого поля з таблиці (ALTER TABLE + ADD, ALTER TABLE + DROP COLUMN)

У прикладі демонструється використання наступних команд:

• CREATE TABLE – створення таблиці з 4-х полів;
• ALTER TABLE з ключем ADD – додавання нового поля до таблиці;
• ALTER TABLE з ключем DROP COLUMN – видалення поля з таблиці.

 

/* Створити таблицю з іменем MyTable.
   Додати стовпці A, B, C, D
*/
CREATE TABLE [MyTable] (
    A INT,
    B FLOAT,
    C VARCHAR(20),
    D DATE
);

GO

/* Результат:
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

/* Додати стовпчик E типу CHAR(10) */
ALTER TABLE [MyTable]
    ADD
        E CHAR(10)

GO

/* Результат:
-----------------------------------------------
|  A  |   B   |      C      |   D  |     E    |
-----------------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE | CHAR(10) |
-----------------------------------------------
*/

/* Видалити стовпчик C */
ALTER TABLE [MyTable]
    DROP COLUMN C

/* Результат:
---------------------------------
|  A  |   B   |   D  |    E     |
---------------------------------
| INT | FLOAT | DATE | CHAR(10) |
---------------------------------
*/

  ⇑

8. Видалення декількох полів (ALTER TABLE + DROP COLUMN)

У прикладі спочатку створюється таблиця, що містить 6 полів з іменами A, B, C, D, E, F. Потім з допомогою команди ALTER TABLE з таблиці видаляються два поля з іменами A, C.

/* Створити таблицю з іменем MyTable.
   Додати стовпці A, B, C, D, E, F
*/
CREATE TABLE [MyTable] (
    A INT NOT NULL,
    B FLOAT,
    C VARCHAR(20),
    D DATE,
    E CHAR(10),
    F REAL
);

GO

/* Результат:
------------------------------------------------------
|  A  |   B   |      C      |   D  |     E    |   F  |
------------------------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE | CHAR(10) | REAL |
------------------------------------------------------
*/

/* Видалити стовпці C, A */
ALTER TABLE [MyTable]
    DROP COLUMN C, A

/* Результат:
----------------------------------
|   B   |   D  |    E     |   F  |
----------------------------------
| FLOAT | DATE | CHAR(10) | REAL |
----------------------------------
*/

  ⇑

9. Приклад зміни обмежень в заданому полі таблиці. Поєднання ALTER TABLE + ADD + DROP

Якщо до існуючого поля потрібно додати обмеження, то можна виконати таку послідовність дій:

• видалити поле;
• створити нове поле з таким самим іменем з заданим обмеженням. Якщо дані в таблиці вже існують, то потрібно виконати операції копіювання цих даних в іншу таблицю.

Код на мові T-SQL, що виконує операції зі зміни обмежень наступний

/* 1. Створити таблицю з іменем MyTable.
   Задати стовпці A, B, C, D.
*/
CREATE TABLE [MyTable] (
    A INT NOT NULL PRIMARY KEY,
    B FLOAT,
    C VARCHAR(20),
    D DATE
);

GO

/* Результат:
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

/* 2. Додати до стовпця B обмеження NOT NULL */
/* 2.1. Видалити стовпчик B */
ALTER TABLE [MyTable]
    DROP COLUMN B

/* 2.2. Додати стовпчик B з обмеженням NOT NULL */
ALTER TABLE [MyTable]
    ADD
        B FLOAT NOT NULL

На рисунку 1 зображено вигляд вікна Microsoft SQL Server Management Studio з виконуваним прикладом.

Рисунок 1. Оператор ALTER TABLE. Додавання обмеження NOT NULL в полі B

  ⇑

10. Приклад зміни назви поля. Комбінація операторів ALTER TABLE + DROP, ALTER TABLE + ADD

У прикладі демонструється зміна назви заданого поля шляхом видалення існуючого (ALTER TABLE + DROP) та додавання нового з новим іменем (ALTER TABLE + ADD).

Якщо поле таблиці містить дані, то одним зі способів рішення задачі є створення тимчасової таблиці, в яку ці дані будуть скопійовані. Після цього, відбуваються зміни в основній таблиці і дані з тимчасової таблиці копіюються в цю таблицю. Такий спосіб рішення задачі наводиться нижче з метою демонстрації використання базових операцій для роботи з таблицями.

/* 1. Створити таблицю MyTable */
CREATE TABLE [MyTable] (
    A INT,
    B FLOAT,
    C VARCHAR(20)
);

/*
Результат.
-----------------------------
|  A  |   B   |      C      |
-----------------------------
| INT | FLOAT | VARCHAR(20) |
-----------------------------
*/

/* 2. Додати в таблицю MyTable довільні дані */
INSERT INTO [MyTable] ( A, B, C )
    VALUES ( 1, 5.88, 'First' )

INSERT INTO [MyTable] ( A, B, C )
    VALUES ( 2, 3.5, 'Second' )

INSERT INTO [MyTable] ( A, B, C )
    VALUES ( 4, 8.16, 'Third' )

/*
Результат. Таблиця MyTable
-----------------------------
|  A  |   B   |      C      |
-----------------------------
| INT | FLOAT | VARCHAR(20) |
-----------------------------
|  1  |  5.88 |   'First'   |
|  2  |  3.5  |   'Second'  |
|  4  |  8.16 |   'Third'   |
-----------------------------
*/

/* 3. Вивести дані таблиці MyTable для контролю */
SELECT * FROM MyTable

/*
  Змінити назву поля C на ABC у таблиці MyTable
*/
/* 4.1. Створити нову таблицю-копію, в якому поле C замінено на ABC */
CREATE TABLE [Temp] (
    A INT,
    B FLOAT,
    ABC VARCHAR(20)
)

/* 4.2. Скопіювати дані поля C у тимчасову таблицю */
/* 4.2.1. Копіювання MyTable.C => Temp.C */
INSERT INTO [Temp] ( A, B, ABC )
    SELECT *
    FROM [MyTable]

/* 4.2.2. Вивести дані тимчасової таблиці Temp */
SELECT * FROM [Temp]

/* 4.3. Видалити поле C таблиці MyTable */
ALTER TABLE [MyTable]
    DROP COLUMN C

/* 4.4. Додати поле ABC в таблицю MyTable */
ALTER TABLE [MyTable]
    ADD ABC VARCHAR(20)

/* 4.5. Видалити всі попередні дані з таблиці MyTable */
DELETE FROM [MyTable]

/* 4.6. Скопіювати дані з таблиці Temp в таблицю MyTable */
INSERT INTO [MyTable] ( A, B, ABC )
    SELECT *
    FROM [Temp]

/* 4.7. Видалити таблицю Temp */
DROP TABLE [Temp]

/* 5. Вивести дані таблиці MyTable */
SELECT * FROM [MyTable]

/*
Результат. Таблиця MyTable
-----------------------------
|  A  |   B   |     ABC     |
-----------------------------
| INT | FLOAT | VARCHAR(20) |
-----------------------------
|  1  |   5.88 |   'First'  |
|  2  |   3.5  |   'Second' |
|  4  |   8.16 |   'Third'  |
-----------------------------
*/

Результат виконання програми у Microsoft SQL Server Management Studio зображено на рисунку 2.

Рисунок 2. Результат виконання програми. Таблиця MyTable зі зміненим стовпцем ABC

  ⇑

---

Споріднені теми

• Microsoft SQL Server Management Studio 18. Приклад створення найпростішого запиту
• Створення таблиць. Оператор CREATE TABLE

  ⇑

---