Sql добавить столбец в таблицу: ALTER TABLE SQL Server — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Как добавить столбец в таблицу sql

Уже не впервые на форумах встречаю вопрос о том, как добавить новый столбец в определенное место существующей таблицы, скажем, между первым и вторым столбцом. Этот наивный с точки зрения реляционной модели вопрос, тем не менее, имеет некоторый смысл с точки зрения языка SQL.

Я говорю «наивный», поскольку по определению атрибуты отношения не упорядочены, и обращение к значениям атрибута выполняется по его имени, но не по позиции. Что же касается языка SQL, то столбцы в таблице имеют порядок, который задается в операторе CREATE TABLE. Новый же столбец, который добавляется с помощью оператора ALTER TABLE, становится последним в таблице. Т.е. стандарт языка SQL не предусматривает возможности непосредственно добавить столбец в определенную позицию в списке столбцов.

Справедливости ради следует сказать, что некоторые реализации языка SQL расширяют стандарт в этом плане. Например, в MySQL в операторе ALTER TABLE вы можете указать позицию добавляемого столбца (новый столбец может стать первым или после указанного столбца).

Другой вопрос, а зачем это нужно? Мне приходит в голову такой вариант. Скажем, в клиентском приложении для генерации отчетов используется запрос типа

Если потребовалось добавить в таблицу Employees дополнительную информацию о сотрудниках, которая логически должна находиться в определенном месте (например, отчество непосредственно между именем и фамилией), то вместо того, чтобы вносить изменения в клиентские приложения, может оказаться проще изменить структуру таблицы Employees.

Итак, имеется таблица Employees, которая создается следующим оператором:

Теперь нам требуется добавить столбец middle_name (отчество) между столбцами first_name и last_name.

В MySQL это можно сделать просто:

В SQL Server так поступить нельзя, но можно использовать следующий алгоритм:

» создание новой таблицы требуемой структуры;
» копирование данных из таблицы Employees в эту новую таблицу;
» удаление таблицы Employees;
» переименование новой таблицы в таблицу с именем Employees.

Ниже приводятся операторы T-SQL, которые реализуют этот алгоритм.

Обратите внимание, что столбец middle_name допускает NULL-значения. Мы не можем добавить столбец в существующую таблицу (или, как в нашем случае, не задавая значения для этого столбца при копировании данных из таблицы Employees в таблицу Emp_temp), если он не имеет значения по умолчанию. Здесь мы принимаем по умолчанию значение NULL.

Мы можем выполнить два первых шага за одно действие с помощью оператора SELECT INTO, который «на лету» создает новую таблицу:

Оператор CAST позволяет нам тут же задать требуемый тип добавляемого столбца. Остальные столбцы наследуют типы из таблицы-источника.

Если вы хотите проверить работу последнего скрипта, приведите таблицу в исходное состояние, удалив добавленный ранее столбец:

Заметим, что при использовании оператора SELECT INTO теряются ключи. Поэтому нам придется добавить ограничение PRIMARY KEY (первичный ключ) либо во временную таблицу, либо уже в переименованную, чтобы получить в точности требуемую структуру:

Аналогичный алгоритм можно применить и для перестановки уже существующих столбцов. Помимо указанной причины такая перестановка может повысить производительность, связанную с сокращением объема данных, записываемых в журнал транзакций в некоторых реализациях. Это связано со спецификой обработки строк фиксированной и переменной длины. Вот какие рекомендации по этому поводу дает Джо Селко * :

» помещайте первыми нечасто обновляемые столбцы постоянной длины;
» затем помещайте нечасто обновляемые столбцы переменной длины;
» последними помещайте часто обновляемые столбцы;
» ставьте рядом столбцы, которые, как правило, обновляются одновременно.

* Селко Д. Стиль программирования Джо Селко на SQL. — М.: Изд-во «Русская редакция»; СПб.: Питер, 2006

У меня есть таблица SQL Server в производстве, которая имеет миллионы строк, и оказывается, что мне нужно добавить к ней столбец. Или, если быть более точным, мне нужно добавить поле к сущности, которую представляет таблица.

Синтаксически это не проблема, и если в таблице не было так много строк и не было в производстве, это было бы легко.

На самом деле то, что мне нужно, — это курс действий. Существует множество веб-сайтов с чрезвычайно большими таблицами, и они должны время от времени добавлять поля. Как они делают это без существенного простоя?

Одна вещь, которую я должен добавить, я не хотел, чтобы столбец разрешал null, что означало бы, что мне нужно иметь значение по умолчанию.

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

не займет много времени . Что займет много времени, так это вставить столбцы в середине других столбцов . b/c, тогда движок должен создать новую таблицу и скопировать данные в новую таблицу.

Единственное реальное решение для непрерывного времени бесперебойной работы — это резервирование .

Я признаю ответ @ Nestor, что добавление нового столбца не должно занять много времени в SQL Server, но, тем не менее, это все равно может быть отключением, которое неприемлемо для производственной системы. Альтернативой является изменение в параллельной системе, а затем, как только операция будет завершена, замените новое на старое.

Например, если вам нужно добавить столбец, вы можете создать копию таблицы, затем добавить столбец в эту копию, а затем использовать sp_rename () , чтобы переместить старый стол в сторону и новую таблицу на место.

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

Для некоторых видов сложных обновлений вы можете полностью дублировать базу данных на отдельном сервере. Как только это будет готово, просто замените записи DNS для двух серверов и voilà!

Я поддержал биржевую компанию в 1990-х годах, которые управляли тремя дубликатами серверов баз данных. Что чтобы они могли внедрять обновления на один сервер, сохраняя один производственный сервер и один переход на другой ресурс сервер. Их операции имели стандартная процедура вращения три машины через производство, отказоустойчивости и обслуживания каждый день . Когда им необходимо обновить оборудования, программного обеспечения или изменить схемы базы данных, потребовалось три дня распространять изменения через их серверов, но они могли бы это сделать без прерывание обслуживания. Все спасибо к избыточности.

«Добавьте столбец, а затем выполните относительно небольшие пакеты UPDATE, чтобы заполнить столбец значением по умолчанию. Это должно предотвратить любые заметные замедления»

И после этого вы должны установить столбец NOT NULL, который будет срабатывать в одной большой транзакции. Так что все будет работать очень быстро, пока вы это не сделаете, поэтому вы, вероятно, очень мало на самом деле получили. Я знаю это только из первых рук.

Возможно, вы захотите переименовать текущую таблицу с X на Y. Вы можете сделать это с помощью этой команды sp_RENAME ‘[OldTableName]’, ‘[NewTableName]’.

Повторно создайте новую таблицу как X с новым столбцом, установленным в NOT NULL, а затем вставьте пакет из Y в X и включите значение по умолчанию либо в свою вставку для нового столбца, либо поместите значение по умолчанию в новый столбец при воссоздании таблицы X.

Я сделал этот тип изменений на столе с сотнями миллионов строк. Это продолжалось более часа, но это не привело к взрыву нашего журнала. Когда я попытался просто изменить столбец на NOT NULL со всеми данными в таблице, потребовалось более 20 часов, прежде чем я убил процесс.

Пробовали ли вы просто добавить столбец, заполняющий его данными, и установив столбец NOT NULL?

Поэтому, в конце концов, я не думаю, что есть волшебная пуля.

Я не хотел, чтобы столбец разрешал null, что означало бы, что мне нужно иметь значение по умолчанию.

Добавление столбца NOT NULL с DEFAULT Ограничение на таблицу из любого количества строк (даже миллиардов) стало легче много , начиная с SQL Server 2012 (но только для Enterprise Edition), поскольку они позволили ему работать в Интернете (в большинстве случаев), где для существующих строк значение будет считываться из метаданных и фактически не хранится в строке до тех пор, пока строка не будет обновлена, или кластеризованный индекс перестраивается. Вместо парафраза, вот соответствующий раздел на странице MSDN для ALTER TABLE :

Добавление NOT NULL столбцов в качестве онлайн-операции

Начиная с SQL Server 2012 Enterprise Edition добавление столбца NOT NULL со значением по умолчанию — это онлайн-операция, когда значением по умолчанию является константа времени . Это означает, что операция выполняется почти мгновенно, независимо от количества строк в таблице. Это связано с тем, что существующие строки в таблице не обновляются во время операции; вместо этого значение по умолчанию сохраняется только в метаданных таблицы, и значение просматривается по мере необходимости в запросах, которые обращаются к этим строкам. Такое поведение происходит автоматически; дополнительный синтаксис не требуется для реализации онлайн-операции за синтаксисом ADD COLUMN. Константа времени выполнения — это выражение, которое производит одно и то же значение во время выполнения для каждой строки таблицы независимо от ее детерминизма. Например, константное выражение «Мои временные данные» или системная функция GETUTCDATETIME () являются константами времени выполнения. Напротив, функции NEWID () или NEWSEQUENTIALID () не являются константами времени выполнения, поскольку для каждой строки таблицы создается уникальное значение. Добавление столбца NOT NULL со значением по умолчанию, который не является константой времени выполнения, всегда выполняется в автономном режиме, а эксклюзивная (SCH-M) блокировка выполняется на время операции.

В то время как существующие строки ссылаются на значение, хранящееся в метаданных, значение по умолчанию сохраняется в строке для любых вставленных новых строк и не указывает другое значение для столбца. Значение по умолчанию, хранящееся в метаданных, перемещается в существующую строку при обновлении строки (даже если фактический столбец не указан в инструкции UPDATE), или если таблица или кластерный индекс перестраиваются.

Колонки типа varchar (max), nvarchar (max), varbinary (max), xml, text, ntext, image, hierarchyid, геометрия, география или CLR UDTS не могут быть добавлены в онлайн-операции. Столбец нельзя добавить в сеть, если это приводит к тому, что максимально возможный размер строки превышает предел в 8,060 байт. В этом случае столбец добавляется как автономная операция.

Для добавления столбцов используется предложение ADD :

Новый столбец становится последним столбцом:

Указания по добавлению столбца

Столбцы можно добавлять и изменять.

Отсутствует возможность указания места расположения столбца. Новый столбец всегда становится последним столбцом.

В примере на рисунке в таблицу DEPT80 добавляется столбец с именем JOB_ID . Столбец JOB_ID становится последним столбцом в таблице.

Примечание . Если при добавлении столбца таблица уже содержит строки, тогда новый столбец первоначально является пустым или принимает для всех строк стандартные значения. Обязательный столбец NOT NULL можно добавить в таблицу, которая содержит данные в других столбцах, только если заданы стандартные значения. В пустую таблицу столбец NOT NULL можно добавить без стандартного значения.

Небольшие доработки по сайту иногда требуют вставки новых столбцов в таблицу базы данных MySQL. Речь идет о столбце, который необходимо добавить в какую-либо таблицу базы данных посредством консоли. Давайте рассмотрим несколько примеров добавление столбцов. Для вставки новых столбцов в MySQL есть команда ALTER TABLE ADD, про использование которой я буду говорить далее.
Если у вас был опыт работы с SQL запросами, то наверняка вам не составит большого труда разобраться что тут и к чему.

Синтаксис:

Описание параметров использованных в запросе:
table_name — название таблицы;
field_name — название столбца;
parametrs — параметры поля;

В параметрах столбца обязательным является указание типа данных.
Попробуем вставить в таблицу t_posts столбец tags текстового типа.

Новый столбец tags будет добавлено по умолчанию в конец таблицы. Чтобы столбец оказался в начале таблицы необходимо добавить ещё один ключевой параметр — FIRST.

После выполнения команды в таблицу будет добавлен новый столбец в начало таблицы. Если нужно разместить столбец таблицы не в начале и не в конце, а после определенного столбца, то следует добавить параметр AFTER с указанием столбца после которого будет размещен новый столбец.

После выполнение команды в таблицу t_post будет добавлен столбец типа TEXT после столбца description.

Есть возможность вставлять сразу несколько столбцов, указав их через запятую.

Команда предполагает добавление столбцов tags, prevew_text, detail_text типа TEXT.

По аналогии с указанием размещения после других столбцов.

Столбец tags будет добавлен после столбца comments, а столбец preview_text будет добавлен после столбца views.

Добавление нового столбца таблицы в определенную порядковую позицию в Microsoft SQL Server

можно ли добавить столбец в таблицу в определенной порядковой позиции в Microsoft SQL Server?

например, наши таблицы всегда имеют столбцы CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy в «конце» каждого определения таблицы? Я бы хотел, чтобы новый столбец отображался в SSMS над этими столбцами.

Если я создаю сценарии всех изменений моей базы данных, есть ли способ сохранить этот порядок в конце таблицы?

к твоему сведению, я не пытаюсь если это вообще будет сделано, объявите войну пламени. Если вы хотите прочитать о потоке, который быстро вырождается в это, вот хороший:

http://www.developersdex.com/sql/message.asp?p=581&r=5014513


вы должны создать временную таблицу, которая отражает схему исходной таблицы, но с того столбца, который вы хотите, а затем скопировать содержимое исходного темп. Удалите оригинал и переименуйте temp.

Это то, что SQL Management Studio делает за кулисами.

с помощью инструмента синхронизации схемы можно автоматически создавать эти сценарии.

23

поделиться

автор: Jose Basilio


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

Один: Создать/Копировать/Падение / Переименовать

это на самом деле то, что SQL Server делает в графическом интерфейсе: вот пример сценария, который он генерирует и выполняет, когда вы нажимаете кнопку «Сохранить» после добавления нового столбца в начало таблицы.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_SomeTable
    (
    MyNewColumn int NOT NULL,
    OriginalIntColumn int NULL,
    OriginalVarcharColumn varchar(100) NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_SomeTable SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_SomeTable ON
GO
IF EXISTS(SELECT * FROM dbo.SomeTable)
     EXEC('INSERT INTO dbo.Tmp_SomeTable (OriginalIntColumn, OriginalVarcharColumn FROM dbo.SomeTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_SomeTable OFF
GO
DROP TABLE dbo.SomeTable
GO
EXECUTE sp_rename N'dbo.Tmp_SomeTable', N'SomeTable', 'OBJECT' 
GO
GO
COMMIT

два: добавить СТОЛБЕЦ / ОБНОВИТЬ / УДАЛИТЬ СТОЛБЕЦ / ПЕРЕИМЕНОВАТЬ

этот метод в основном включает в себя создание копии всех существующих столбцов, которые вы хотите добавить в «право» вашего нового столбца, передачу данных в новый столбец, а затем удаление оригиналов и переименование новых. Это будет играть хаос с любыми индексами или ограничениями, которые у вас есть, так как вы должны их переустанавливать. Это технически возможно, но опять же отнимает много времени как с точки зрения разработки, так и с точки зрения исполнения.

CREATE TABLE MyTest (a int, b int, d int, e int)
INSERT INTO MyTest (a,b,d,e) VALUES(1,2,4,5)
SELECT * FROM MyTest -- your current table
ALTER TABLE MyTest ADD c int -- add a new column
ALTER TABLE MyTest ADD d_new int -- create copies of the existing columns you want to move
ALTER TABLE MyTest ADD e_new int
UPDATE MyTest SET d_new = d, e_new = e -- transfer data to the new columns
ALTER TABLE MyTest DROP COLUMN d -- remove the originals
ALTER TABLE MyTest DROP COLUMN e
EXEC SP_RENAME 'MyTest.d_new', 'd'; -- rename the new columns
EXEC SP_RENAME 'MyTest.e_new', 'e';
SELECT * FROM MyTest 
DROP TABLE MyTest -- clean up the sample

три: жить с этим

6

поделиться

автор: AHiggins


перейдите в SQL Server management Studio и» создайте » существующую таблицу. Вставить столбец в центре, щелкните правой кнопкой мыши в пустой области и выберите Создать Сценарий Изменения…

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

вам также может потребоваться снять этот флажок, чтобы разрешить создание сценариев изменений

4

поделиться

автор: KM.


насколько мне известно, нет известного метода изменения порядка столбца. За кулисами SQL Management Studio делает то, что сказал Хосе Базилио. И если у вас есть большая таблица, то непрактично изменять порядок столбцов таким образом.

вы можете использовать «просмотр». С представлениями SQL вы можете использовать любой порядок, который вам нравится, без влияния изменений столбца таблицы.

3

поделиться

автор: tbaskan


TFS 2013 сделает это за вас автоматически.

добавьте новые столбцы в таблицу в любом случае, а затем зафиксируйте изменения в TFS. Оттуда вы можете открыть файл sql таблицы в Visual Studio и вручную переместить порядок столбцов в скрипте создания T-SQL. Затем вы можете обновить целевую базу данных с помощью инструмента сравнения схем VS, найденного в разделе Инструменты > SQL Server > новое сравнение схем. Выберите проект базы данных с изменением в качестве источник и база данных, которую вы хотите обновить в качестве цели. Сравните, выберите сценарий таблицы и обновите его. VS упадет и добавит автоматически. Все ваши данные будут в безопасности, и индексы тоже.

0

поделиться

автор: Rickest Rick


грязный и простой.
Экспорт таблицы в CSV.
Вставить новые данные в нужное положение.
Откидной стол.
Создайте новую таблицу с требуемыми спецификациями столбцов.
Загрузите столбцы из csv в новую таблицу.

0

поделиться

автор: BioDeveloper


Я думаю, что просто добавить столбец ALTER TABLE table1 ADD .. а затем создайте таблицу tmp, такую как tmp_table1 из select like
SELECT col1,col2,col5,col3,col4 into tmp_table1 from table1;
а затем отбросьте table1 и переименуйте tmp_table1 в table1, то есть. Я надеюсь, что это поможет кому-то

0

поделиться

автор: Maurice Rwabuhungu


Я не уверен, что поток все еще активен. У меня был тот же запрос с базой данных MySQL. Щелкните правой кнопкой мыши таблицу и выберите «ALTER» автоматически сгенерированный код ниже. Образец предоставлен из sakila db, и он работал. Просто узнайте столбец, после которого вы хотите разместить свой новый столбец и использовать ключевое слово «AFTER»

ALTER TABLE `sakila`. `actor` 
CHANGE COLUMN `middle_name` `middle_name` VARCHAR(50) NULL DEFAULT NULL AFTER `first_name`; 

0

поделиться

автор: Kishore Ramakrishnan


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

SQL Server Management Studio имеет представление дизайна, которое, по-видимому, позволяет создавать столбец в любом месте в списке столбцов , но на самом деле здесь в фоновом режиме создается новая таблица с новыми столбцами, а старая удаляется.

нет Команды SQL DDL для создания столбца в определенном месте или для» переупорядочивания » столбцов. Это действительно не нужно.

Марк

-1

поделиться

автор: marc_s


Добавить столбец в существующую таблицу с помощью ALTER TABLE — Data to Fish

4 сентября 2021 г.

Вот синтаксис для добавления столбца в существующую таблицу с помощью ALTER TABLE:

 ALTER TABLE имя_таблицы
ДОБАВИТЬ new_column_name data_type
 

Далее вы увидите, как применить описанный выше синтаксис на простом примере.

Предположим, у вас есть таблица с названием products .

Таблица «продукты» в настоящее время содержит следующие столбцы и данные:

product_id имя_продукта
1 Настольный компьютер
2 Ноутбук
3 Планшет
4 Монитор
5 Принтер

Конечная цель — добавить дополнительный столбец под названием « цена » со следующими значениями (выделены желтым цветом):

product_id имя_продукта цена
1 Настольный компьютер 800
2 Ноутбук 1200
3 Планшет 200
4 Монитор 350
5 Принтер 150

Затем вы можете использовать приведенный ниже шаблон, чтобы добавить столбец «цена»:

 ALTER TABLE имя_таблицы
ДОБАВИТЬ new_column_name data_type
 

Для нашего примера:

  • Имя_таблицы: products
  • new_column_name: цена
  • Тип_данных: int

Таким образом, полный запрос ALTER TABLE для нашего примера будет выглядеть следующим образом:

 ALTER TABLE products
ДОБАВИТЬ цену
 

После выполнения вышеуказанного запроса будет добавлен новый столбец:

 Команды выполнены успешно
 

Давайте запустим следующий запрос SELECT, чтобы увидеть результаты:

 SELECT * FROM products
 

Обратите внимание, что столбец «цена» был добавлен в конец таблицы, где каждое значение в столбце «цена» в настоящее время равно NULL:

product_id имя_продукта цена
1 Настольный компьютер НУЛЕВОЙ
2 Ноутбук НУЛЕВОЙ
3 Планшет НУЛЕВОЙ
4 Монитор НУЛЕВОЙ
5 Принтер НУЛЕВОЙ

Затем вы можете использовать запрос UPDATE, чтобы заменить эти значения NULL фактическими ценами:

 UPDATE products
УСТАНОВИТЬ цену =
СЛУЧАЙ, КОГДА product_id = 1, ТОГДА 800
КОГДА product_id = 2, ТО 1200
КОГДА product_id = 3, ТО 200
КОГДА product_id = 4, ТО 350
КОГДА product_id = 5, ТО 150
КОНЕЦ
 

Запустите приведенный выше запрос UPDATE.

Затем повторите следующий запрос SELECT, чтобы увидеть новые значения:

 SELECT * FROM products
 

Теперь вы увидите актуальные цены:

product_id имя_продукта цена
1 Настольный компьютер 800
2 Ноутбук 1200
3 Планшет 200
4 Монитор 350
5 Принтер 150

До сих пор вы видели, как добавить столбец с целочисленными значениями.

Кроме того, вы можете добавить столбец со строковыми/текстовыми значениями.

Например, давайте добавим еще один столбец с именем « марка », который содержит строки. Тип данных для этого столбца будет nvarchar(50) :

 ALTER TABLE продукты
ДОБАВИТЬ бренд nvarchar(50)
 

Запустите приведенный выше запрос, и будет добавлен новый столбец «бренд»:

 Команды выполнены успешно
 

Давайте повторно запустим следующий запрос SELECT, чтобы увидеть результаты:

 SELECT * FROM products 

Как видите, столбец «бренд» был добавлен в конец таблицы со значениями NULL:

product_id имя_продукта цена марка
1 Настольный компьютер 800 НУЛЕВОЙ
2 Ноутбук 1200 НУЛЕВОЙ
3 Планшет 200 НУЛЕВОЙ
4 Монитор 350 НУЛЕВОЙ
5Принтер 150 НУЛЕВОЙ

Давайте теперь обновим значения в столбце «бренд» (поскольку мы имеем дело со строками, вам нужно будет поместить значения брендов в кавычки):

 ОБНОВИТЬ продукты
НАБОР марки =
СЛУЧАЙ, КОГДА product_id = 1 THEN 'A'
КОГДА product_id = 2, ТО 'B'
КОГДА product_id = 3, ТО 'C'
КОГДА product_id = 4, ТО 'D'
КОГДА product_id = 5, ТО 'Е'
КОНЕЦ
 

Наконец, выполните приведенный ниже запрос SELECT, чтобы увидеть результаты:

 SELECT * FROM products 

В конце таблицы вы увидите значения брендов:

product_id имя_продукта цена марка
1 Настольный компьютер 800 А
2 Ноутбук 1200 Б
3 Таблетка 200 С
4 Монитор 350 Д
5 Принтер 150 Е

ALTER TABLE

ALTER TABLE

alter-table-stmt:

ALTERTABLEимя-схемы. имя-таблицыRENAMETOnew-table-nameRENAMECOLUMNимя-столбцаTOnew-имя-столбцаADDCOLUMNcolumn-defDROPCOLUMNимя-столбца

определение-столбца:

SQLite поддерживает ограниченное подмножество ALTER TABLE.
Команда ALTER TABLE в SQLite позволяет
эти изменения существующей таблицы:
его можно переименовать;
столбец можно переименовать;
к нему можно добавить столбец;
или же
из него можно удалить столбец.

Синтаксис RENAME TO изменяет имя таблицы-имя
к новому имени таблицы.
Эта команда
нельзя использовать для перемещения таблицы между присоединенными базами данных, только для переименования
таблицу в той же базе данных.
Если в переименовываемой таблице есть триггеры или индексы, то они остаются
прикрепленный к таблице после ее переименования.

Примечание о совместимости:
Улучшено поведение ALTER TABLE при переименовании таблицы.
в версиях 3.25.0 (2018-09-15) и 3.26.0 (2018-12-01)
для переноса операции переименования в триггеры и
представления, ссылающиеся на переименованную таблицу. Это считается
улучшение. Приложения, зависящие от более старых (и
возможно ошибочное) поведение может использовать
Оператор PRAGMA legacy_alter_table=ON или
Параметр конфигурации SQLITE_DBCONFIG_LEGACY_ALTER_TABLE
на интерфейсе sqlite3_db_config(), чтобы сделать ALTER TABLE RENAME
вести себя так же, как и до версии 3.25.0.

Начиная с версии 3.25.0 (15.09.2018), ссылки на таблицу
в телах триггеров и определения представлений также переименовываются.

До версии 3.26.0 (2018-12-01), ссылки на FOREIGN KEY
в таблицу, которая переименовывается, редактировались только в том случае, если
PRAGMA Foreign_keys=ON, или другими словами, если
применялись ограничения внешнего ключа. С
PRAGMA external_keys=OFF, ограничения FOREIGN KEY не будут изменены
когда таблица, на которую ссылается внешний ключ («родительская таблица»)
был переименован. Начиная с версии 3.26.0, ограничения FOREIGN KEY
всегда преобразуются при переименовании таблицы, если только
Параметр PRAGMA legacy_alter_table=ON включен. Следующее
таблица суммирует разницу:

ПРАГМА внешние_ключи PRAGMA legacy_alter_table Родительская таблица
ссылки обновлены
Версия SQLite
Выкл. Выкл. Нет < 3.26.0
Выкл. Выкл. Да >= 3.26.0
Вкл. Выкл. Да все
Выкл. Вкл. Нет все
На На Да все

Синтаксис RENAME COLUMN TO изменяет
имя-столбца таблицы имя-таблицы
в новое имя столбца. Имя столбца изменено как
в самом определении таблицы, а также во всех индексах, триггерах,
и представления, которые ссылаются на столбец. Если бы изменение имени столбца
привести к семантической неоднозначности в триггере или представлении, то RENAME
COLUMN завершается с ошибкой, и изменения не применяются.

Синтаксис ДОБАВИТЬ СТОЛБЦ
используется для добавления нового столбца в существующую таблицу.
Новый столбец всегда добавляется в конец списка существующих столбцов.
Правило column-def определяет характеристики нового столбца.
Новый столбец может принимать любую из форм, допустимых в CREATE TABLE.
заявление со следующими ограничениями:

  • Столбец может не иметь ограничения PRIMARY KEY или UNIQUE.
  • Столбец может не иметь значения по умолчанию CURRENT_TIME, CURRENT_DATE,
    CURRENT_TIMESTAMP или выражение в скобках.
  • Если указано ограничение NOT NULL, столбец должен иметь
    значение по умолчанию, отличное от NULL.
  • Если ограничения внешнего ключа включены и
    столбец с предложением REFERENCES
    добавлен, столбец должен иметь значение по умолчанию NULL.
  • Столбец НЕ ВСЕГДА ГЕНЕРИРУЕТСЯ… СОХРАНЯЕТСЯ,
    хотя разрешены ВИРТУАЛЬНЫЕ столбцы.

При добавлении столбца с ограничением CHECK или ограничением NOT NULL
в сгенерированном столбце добавленные ограничения проверяются на все
ранее существовавшие строки в таблице и ошибка ADD COLUMN
если какое-либо ограничение не работает. Тестирование добавленных ограничений
против ранее существовавших строк — это новое улучшение в версии SQLite.
3.37.0 (27 ноября 2021 г.).

Команда ALTER TABLE работает путем изменения текста SQL схемы
хранится в таблице sqlite_schema.
В содержимое таблицы не вносятся изменения для переименования или добавления столбца без
ограничения.
Из-за этого,
время выполнения таких команд ALTER TABLE не зависит от
количество данных в таблице и такие команды будут
выполняется так же быстро для таблицы с 10 миллионами строк, как и для таблицы с 1 строкой.
При добавлении новых столбцов с ограничениями CHECK или добавлении сгенерированных
столбцы с ограничениями NOT NULL или при удалении столбцов все
существующие данные в таблице должны быть либо прочитаны (для проверки новых ограничений
против существующих строк) или написаны (чтобы удалить удаленные столбцы). В тех
случаях команда ALTER TABLE занимает время, пропорциональное
количество измененного содержимого таблицы.

После запуска ADD COLUMN в базе данных эта база данных не будет
быть удобочитаемым для SQLite версии 3.1.3 (20 февраля 2005 г.) и более ранних версий.

Синтаксис DROP COLUMN
используется для удаления существующего столбца из таблицы.
Команда DROP COLUMN удаляет именованный столбец из таблицы,
и переписывает его содержимое, чтобы очистить данные, связанные
с этой колонкой.
Команда DROP COLUMN работает, только если на столбец нет ссылки
любыми другими частями схемы и не является ПЕРВИЧНЫМ КЛЮЧОМ и
не имеет ограничения UNIQUE.
Возможные причины сбоя команды DROP COLUMN:

  • Столбец является ПЕРВИЧНЫМ КЛЮЧОМ или его частью.
  • Столбец имеет ограничение UNIQUE.
  • Столбец проиндексирован.
  • Столбец назван в предложении WHERE частичного индекса.
  • Имя столбца указано в ограничении CHECK таблицы или столбца
    не связанный с удаляемым столбцом.
  • Столбец используется в ограничении внешнего ключа.
  • Столбец используется в выражении сгенерированного столбца.
  • Столбец отображается в триггере или представлении.

SQLite сохраняет схему в виде обычного текста в таблице sqlite_schema.
Команда DROP COLUMN (и все другие варианты ALTER TABLE
также) измените этот текст, а затем попытайтесь повторно проанализировать всю схему.
Команда успешна только в том случае, если схема все еще действительна после
текст изменен. В случае команды DROP COLUMN
только измененный текст заключается в том, что определение столбца удалено из
СОЗДАТЬ ТАБЛИЦУ. Команда DROP COLUMN завершится ошибкой, если
любые следы столбца в других частях схемы, которые будут
запретить синтаксический анализ схемы после выполнения инструкции CREATE TABLE.
был изменен.

ALTER TABLE обычно завершается ошибкой и не вносит никаких изменений, если встречается
любые записи в таблице sqlite_schema, которые не анализируются. За
например, если есть искаженный VIEW или TRIGGER, связанный с
таблица с именем «tbl1», то попытка переименовать «tbl1» в «tbl1neo» будет
завершится ошибкой, так как связанные представления и триггеры не могут быть проанализированы.

Начиная с SQLite 3.38.0 (2022-02-22), эта проверка ошибок
можно отключить, установив «PRAGMA writable_schema=ON;». Когда
схема доступна для записи, ALTER TABLE молча игнорирует любые строки
sqlite_schema, которые не анализируются.

Единственными командами изменения схемы, напрямую поддерживаемыми SQLite, являются
«переименовать таблицу», «переименовать столбец», «добавить столбец», «удалить столбец»
команды, показанные выше. Тем не менее, приложения
может вносить другие произвольные изменения в формат таблицы, используя простой
последовательность операций.
Шаги для внесения произвольных изменений в структуру схемы некоторой таблицы X
являются следующими:

  1. Если ограничения внешнего ключа включены, отключите их, используя PRAGMA foreign_keys=OFF.

  2. Начать транзакцию.

  3. Запомните формат всех индексов, триггеров и представлений, связанных с таблицей X.
    Эта информация понадобится на шаге 8 ниже. Один из способов сделать это
    для запуска запроса, подобного следующему:
    ВЫБЕРИТЕ тип, sql FROM sqlite_schema, ГДЕ tbl_name = ‘X’.

  4. Используйте CREATE TABLE для создания новой таблицы «new_X», которая находится в желаемом
    пересмотренный формат таблицы X. Убедитесь, что имя «new_X» не конфликтует
    с любым существующим именем таблицы, конечно.

  5. Перенесите содержимое из X в new_X с помощью оператора
    например: INSERT INTO new_X SELECT … FROM X.

  6. Удалите старую таблицу X: DROP TABLE X.

  7. Измените имя new_X на X, используя: ALTER TABLE new_X RENAME TO X.

  8. Используйте CREATE INDEX, CREATE TRIGGER и CREATE VIEW.
    для восстановления индексов, триггеров и представлений
    связаны с таблицей X. Возможно, используется старый формат триггеров,
    индексы и представления, сохраненные на шаге 3 выше, в качестве руководства, внося изменения
    в соответствии с изменениями.

  9. Если какие-либо представления ссылаются на таблицу X таким образом, на который влияет
    изменить схему, затем удалить эти представления с помощью DROP VIEW и создать их заново
    с любыми изменениями, необходимыми для размещения изменения схемы
    используя СОЗДАТЬ ВИД.

  10. Если ограничения внешнего ключа были изначально включены
    затем запустите PRAGMA foreign_key_check, чтобы убедиться, что схема
    изменение не нарушило никаких ограничений внешнего ключа.

  11. Подтвердите транзакцию, начатую на шаге 2.

  12. Если ограничения внешних ключей были изначально включены, снова включите их сейчас.

Осторожно:
Позаботьтесь о том, чтобы точно следовать описанной выше процедуре. Коробки ниже
суммируйте две процедуры для изменения определения таблицы. Во-первых
С первого взгляда кажется, что они оба выполняют одно и то же. Тем не менее
процедура справа не всегда работает, особенно с
расширенные возможности таблицы переименования, добавленные версиями 3.25.0 и
3.26.0. В процедуре справа первоначальное переименование
таблицу во временное имя может повредить ссылки на эту таблицу в
триггеры, представления и ограничения внешнего ключа. Безопасная процедура на
слева строит исправленное определение таблицы, используя новый временный
имя, затем переименовывает таблицу в ее окончательное имя, которое не нарушает
ссылки.

  1. Создать новую таблицу
  2. Копировать данные
  3. Удалить старый стол
  4. Переименовать новое в старое
  1. Переименовать старую таблицу
  2. Создать новую таблицу
  3. Копировать данные
  4. Удалить старый стол

Правильно

Неверно

12-шаговая обобщенная процедура ALTER TABLE
выше будет работать, даже если
изменение схемы приводит к изменению информации, хранящейся в таблице.
Таким образом, описанная выше полная 12-шаговая процедура подходит для удаления столбца.
изменение порядка столбцов, добавление или удаление ограничения UNIQUE
или PRIMARY KEY, добавив ограничения CHECK или FOREIGN KEY или NOT NULL,
или изменение типа данных для столбца, например. Однако более простой
и более быстрая процедура может быть дополнительно использована для
некоторые изменения, которые никак не влияют на содержимое на диске.
Следующая более простая процедура подходит для удаления
CHECK или FOREIGN KEY или ограничения NOT NULL,
или добавление, удаление или изменение значений по умолчанию на
столбец.

  1. Начать транзакцию.

  2. Запустите PRAGMA schema_version, чтобы определить текущую схему
    номер версии. Этот номер потребуется для шага 6 ниже.

  3. Активировать редактирование схемы с помощью
    PRAGMA writable_schema=ВКЛ.

  4. Запустите оператор UPDATE, чтобы изменить определение таблицы X
    в таблице sqlite_schema:
    UPDATE sqlite_schema SET sql=… WHERE type=’table’ AND name=’X’;

    Осторожно: Внесение подобных изменений в таблицу sqlite_schema
    сделать базу данных поврежденной и нечитаемой, если изменение содержит
    синтаксическая ошибка. Предполагается, что тщательное тестирование ОБНОВЛЕНИЯ
    оператор должен быть выполнен в отдельной пустой базе данных, прежде чем использовать его в
    база данных, содержащая важные данные.

  5. Если изменение в таблице X также влияет на другие таблицы или индексы или
    триггеры представляют собой представления внутри схемы, а затем запускают операторы UPDATE для изменения
    эти другие таблицы также индексируют и представляют. Например, если имя
    изменение столбца, все ограничения FOREIGN KEY, триггеры, индексы и
    представления, которые ссылаются на этот столбец, должны быть изменены.

    Осторожно: Еще раз вносим изменения в sqlite_schema
    такая таблица сделает базу данных поврежденной и нечитаемой, если
    изменение содержит ошибку. Тщательно протестируйте всю эту процедуру
    в отдельной тестовой базе данных, прежде чем использовать ее на
    базу данных, содержащую важные данные, и/или делать резервные копии
    важные базы данных перед выполнением этой процедуры.

  6. Увеличьте номер версии схемы, используя
    PRAGMA schema_version=X, где X равно единице
    больше, чем старый номер версии схемы, найденный на шаге 2 выше.

  7. Отключить редактирование схемы с помощью
    PRAGMA writable_schema = ВЫКЛ.

  8. (Необязательно) Запустите PRAGMA целостность_проверить, чтобы убедиться, что
    изменения схемы не повредили базу данных.

  9. Подтвердите транзакцию, начатую на шаге 1 выше.

Если какая-то будущая версия SQLite добавит новые возможности ALTER TABLE,
эти возможности, скорее всего, будут использовать одну из двух процедур
изложено выше.

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

SQLite отличается тем, что хранит схему
в таблице sqlite_schema как исходный текст CREATE
операторы, определяющие схему. Следовательно, ALTER TABLE нуждается
чтобы пересмотреть текст инструкции CREATE. Делает
поэтому может быть сложно для некоторых «креативных» схем.

Подход SQLite к сохранению схемы в виде текста имеет преимущества
для встроенной реляционной базы данных. С одной стороны, это означает, что
схема занимает меньше места в файле базы данных. Это важно
поскольку общий шаблон использования SQLite состоит в том, чтобы иметь много маленьких,
отдельные файлы базы данных вместо того, чтобы помещать все в один
большой глобальный файл базы данных, что является обычным подходом для клиент/сервер
движки баз данных.
Поскольку схема дублируется в каждом отдельном файле базы данных, она
важно сохранить компактность представления схемы.

Сохранение схемы в виде текста, а не в виде проанализированных таблиц
придать гибкость реализации. Поскольку внутренний разбор
схема регенерируется каждый раз при открытии базы данных,
внутреннее представление схемы может меняться от одного релиза
к следующему. Это важно, так как иногда новые функции требуют
усовершенствования представления внутренней схемы. Изменение
представление внутренней схемы было бы намного сложнее, если бы
представление схемы было представлено в файле базы данных. Так, в др.
слов, сохранение схемы в виде текста помогает поддерживать обратную связь.
совместимость и помогает гарантировать, что старые файлы базы данных могут быть
чтение и запись более новыми версиями SQLite.

Сохранение схемы в виде текста также делает
Формат файла базы данных SQLite легче определить, документировать и
понять. Это помогает сделать файлы базы данных SQLite
рекомендуемый формат хранения для долгосрочного архивирования данных.