Модифікація таблиць. Оператор ALTER 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
⇑