Довідка - по - sql (dml) як додати новий стовпець в таблицю між існуючими стовпцями
Вже не вперше на форумах зустрічаю питання про те, як додати новий стовпець в певне місце існуючої таблиці, скажімо, між першим і другим стовпцем. Цей наївний з точки зору реляційної моделі питання, проте, має певний сенс з точки зору мови SQL.
Я кажу "наївний", оскільки за визначенням атрибути відносини не впорядковані, і звернення до значень атрибута виконується по його імені, але не по позиції. Що ж стосується мови SQL, то стовпці в таблиці мають порядок, який задається в операторі CREATE TABLE. Новий же стовпець, який додається за допомогою оператора ALTER TABLE. стає останнім в таблиці. Тобто стандарт мови SQL не передбачає можливості безпосередньо додати стовпець в певну позицію в списку стовпців.
Справедливості заради слід сказати, що деякі реалізації мови SQL розширюють стандарт в цьому плані. Наприклад, в MySQL в операторі ALTER TABLE ви можете вказати позицію додається шпальти (новий стовпець може стати першим або після вказаного стовпця).
Інше питання, а навіщо це потрібно? Мені спадає на думку такий варіант. Скажімо, в клієнтському додатку для генерації звітів використовується запит типу
Якщо потрібно додати в таблицю Employees додаткову інформацію про співробітників, яка логічно повинна знаходитися в певному місці (наприклад, по батькові безпосередньо між ім'ям і прізвищем), то замість того, щоб вносити зміни в клієнтські програми, може виявитися простіше змінити структуру таблиці Employees.
Отже, є таблиця Employees, яка створюється наступним оператором:
Тепер нам потрібно додати стовпець middle_name (по батькові) між стовпцями first_name і last_name.
В MySQL це можна зробити просто:
У SQL Server так вчинити не можна, але можна використовувати наступний алгоритм:
Нижче наводяться оператори T-SQL. які реалізують цей алгоритм.
Зверніть увагу, що стовпець middle_name допускає NULL-значення. Ми не можемо додати стовпець в існуючу таблицю (або, як в нашому випадку, не ставлячи значення для цього стовпця при копіюванні даних з таблиці Employees в таблицю Emp_temp), якщо він не має значення за замовчуванням. Тут ми приймаємо за замовчуванням значення NULL.
Ми можемо виконати два перші кроки за одну дію за допомогою оператора SELECT INTO. який "на льоту" створює нову таблицю:
Оператор CAST дозволяє нам тут же задати необхідного типа додається стовпчика. Інші стовпці успадковують типи з таблиці-джерела.
Якщо ви хочете перевірити роботу останнього скрипта, приведіть таблицю в початковий стан, видаливши доданий раніше стовпець:
Зауважимо, що при використанні оператора SELECT INTO губляться ключі. Тому нам доведеться додати обмеження PRIMARY KEY (первинний ключ) або в тимчасову таблицю, або вже в перейменовану, щоб отримати в точності необхідну структуру:
Аналогічний алгоритм можна застосувати і для перестановки вже існуючих стовпців. Крім зазначеної причини така перестановка може підвищити продуктивність, пов'язану зі скороченням обсягу даних, що записуються в журнал транзакцій в деяких реалізаціях. Це пов'язано зі специфікою обробки рядків фіксованої і змінної довжини. Ось які рекомендації з цього приводу дає Джо Селко *:
»Розміщуйте першими нечасто оновлювані стовпці постійної довжини;
»Потім ставте нечасто оновлювані стовпці змінної довжини;
»Останніми розміщуйте часто оновлювані стовпці;
»Ставте поруч стовпці, які, як правило, оновлюються одночасно.