Как изменить тип столбца в sqlite

Как изменить тип столбца в sqlite

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

Переименование таблицы

Для переименования таблицы применяется операторы RENAME TO , после которого указывается новое имя таблицы:

ALTER TABLE users RENAME TO people;

Здесь таблица users переименовывается в «people».

Добавление нового столбца

Добавим в таблицу users новый столбец email:

ALTER TABLE users ADD COLUMN email TEXT NOT NULL;

В данном случае столбец email имеет тип TEXT и для него определено ограничение NOT NULL .

Переименование столбца

Переименуем столбец email в login

ALTER TABLE users RENAME COLUMN email TO login;

Удаление столбца

Удалим столбец login из таблицы users:

ALTER TABLE users DROP COLUMN login;

ALTER TABLE оператор SQLite

В этом учебном пособии вы узнаете, как использовать SQLite оператор ALTER TABLE чтобы добавить столбец, изменить столбец, удалить столбец, переименовать столбец или переименовать таблицу (с синтаксисом и примерами).

Описание

В этом руководстве по SQLite объясняется, как использовать SQLite оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).

Добавления столбца

Синтаксис

Синтаксис добавления столбца в таблицу в SQLite (используя ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition;

table_name
Имя таблицы для изменения.

new_column_name
Имя нового столбца, добавляемого в таблицу.

column_definition
Тип данных и определение столбца (NULL или NOT NULL и т.д.).

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу SQLite с помощью опертора ALTER TABLE.

ALTER TABLE employees
ADD status VARCHAR ;

Этот SQLite пример ALTER TABLE добавит столбец с именем status в таблицу employees . Он будет создан как столбец, который допускает значения NULL.

Изменить столбец в таблице

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

Синтаксис

Синтаксис для изменения столбца в таблице в SQLite:

PRAGMA foreign_keys=off;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
);

INSERT INTO table1 (column1, column2, . column_n)
SELECT column1, column2, . column_n
FROM _table1_old;

Пример

Давайте рассмотрим пример, который показывает, как изменить столбец в таблице SQLite.

Например, если у нас была таблица employees , в которой был столбец с именем last_name , который был определен как тип данных CHAR:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name CHAR NOT NULL ,
first_name VARCHAR ,
hire_date DATE

И мы хотели изменить тип данных поля last_name на VARCHAR, мы могли бы сделать следующее:

PRAGMA foreign_keys = off ;
BEGIN TRANSACTION ;
ALTER TABLE employees RENAME TO _employees_old;
CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL ,
first_name VARCHAR ,
hire_date DATE
INSERT INTO employees (employee_id, last_name, first_name, hire_date)
SELECT employee_id,
first_name,
FROM _employees_old;
PRAGMA foreign_keys = on ;

Этот пример переименует нашу существующую таблицу employees в _employees_old . Затем он создаст новую таблицу employees с полем last_name , определенным как тип данных VARCHAR. Затем он вставит все данные из таблицы _employees_old в таблицу employees .

Удалить столбец из таблицы

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

Синтаксис

Синтаксис DROP A COLUMN в таблице в SQLite:

PRAGMA foreign_keys=off;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
);

INSERT INTO table1 (column1, column2, . column_n)
SELECT column1, column2, . column_n
FROM _table1_old;

Пример

Давайте рассмотрим пример, который показывает, как удалить столбец в таблице SQLite.

Например, если у нас была таблица employees , которая была определена следующим образом:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL ,
first_name VARCHAR ,
hire_date DATE

И мы хотели удалить столбец с именем hire_date , мы могли бы сделать следующее:

PRAGMA foreign_keys = off ;
BEGIN TRANSACTION ;
ALTER TABLE employees RENAME TO _employees_old;
CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL ,
first_name VARCHAR
INSERT INTO employees (employee_id, last_name, first_name)
SELECT employee_id,
first_name
FROM _employees_old;
PRAGMA foreign_keys = on ;

Этот пример переименует нашу существующую таблицу employees в _employees_old . Затем он создаст новую таблицу employees с удаленным полем hire_date . Затем он вставит все данные (исключая поле hire_date ) из таблицы _employees_old в таблицу employees .

Переименовать столбец в таблице

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

Синтаксис

Синтаксис RENAME A COLUMN в таблице в SQLite:

PRAGMA foreign_keys=off;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
);

INSERT INTO table1 (column1, column2, . column_n)
SELECT column1, column2, . column_n
FROM _table1_old;

Пример

Давайте рассмотрим пример, который показывает, как переименовать столбец в таблице SQLite.

Например, если у нас была таблица employees , которая была определена следующим образом:

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 10.3: Модификация и изменение таблиц в базах данных SQLite3

• 20.06.2016
• SQLite библиотека, Базы данных
• Комментариев нет

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В этой записи мы поговорим про модификацию таблиц в базах данных под управлением SQLite. На самом деле возможности SQLite по модификации таблиц: изначально можно только добавить новый столбец в таблицу базы данных и изменить имя таблицы в SQLite, но из этой записи вы узнаете об одном из приемов, который позволит вам делать любые модификации таблиц базы данных SQLite3.

Модификация и изменение таблиц в базах данных SQLite3

Синтаксис модификации таблицы в базе данных SQLite

SQLite3 позволяет модифицировать и изменять таблицы в базах данных. К сожалению, синтаксис команды ALTER в SQLite не так богат, как в других СУБД. Вообще, для модификации таблиц в SQLite у нас есть две функции: добавить столбец в таблицу и переименовать таблицу. Общий синтаксис модификации таблиц в базах данных SQLite представлен на рисунке ниже.

Синтаксис модификации таблиц в базах данных SQLite

Для того чтобы сказать SQLite о том, что мы хотим модифицировать таблицу, нам следует использовать команду ALTER TABLE, после чего мы указываем имя таблицы, которую хотим изменить или квалификатор, который говорит SQLite о том, какую таблицу в какой базе данных необходимо модифицировать. А дальше у нас два действия на выбор: мы можем добавить столбец к таблице SQLite3 и мы можем переименовать таблицу в SQLite.

Добавить столбец в таблицу SQLite3: ключевое слово ADD COLUMN

Давайте посмотрим, как мы можем добавить столбец в таблицу SQLite. Для добавления столбца к таблице нам следует использовать конструкцию ADD COLUMN, после которого идет имя столбца и его описание. Давайте посмотри на пример такого изменения таблицы SQLite. Но для начала создадим таблицу в базе данных SQLite при помощи команды CREATE TABLE:

[php] CREATE TABLE table1 (c0); [/php]

Мы создали таблицу со столбцом c0. Давайте теперь попробуем модфицировать таблицу средствами SQLite3.

[php]ALTER TABLE table1 ADD COLUMN c1;[/php]

Для добавления столбца в таблицу SQLite позволяет не писать ключевое слово COLUMN:

[php]ALTER TABLE table1 ADD COLUMN c2;[/php]

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

[php]ALTER TABLE table1 ADD COLUMN c3 TEXT;[/php]

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

• добавляемый столбец не может быть первичным ключом или иметь ограничение UNIQUE;
• столбец, который мы добавляем в таблицу SQLite3 не может иметь в качестве значения по умолчанию CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP или какое-либо составное выражение, которое записывается в скобках;
• если вы хотите задать ограничение NOT NULL, то столбцу, который вы хотите добавить с таблицу SQLite необходимо присвоить значение по умолчанию отличное от NULL;
• если в SQLite включены внешние ключи, то столбец, который вы хотите добавить в таблицу базы данных SQLite3 должен иметь значение по умолчанию.

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

[php] ALTER TABLE table1 ADD COLUMN c4 INTEGER DEFAULT 1 NOT NULL; [/php]

По сути, это все изменения столбцов, которые может делать команда ALTER в SQLite. Давайте теперь убедимся, что столбцы были добавлены в таблицу SQLite и с ними можно работать. Для начала добавим данные в нашу таблицу при помощи команды INSERT:

[php]INSERT INTO table1 VALUES (1, 2, 3, 4);[/php]

А теперь получим данные из нашей таблицы при помощи команды SELECT:

[php]SELECT * FROM table1;[/php]

Теперь мы убедились, что все изменения таблицы были сделаны, а новые столбцы были успешно добавлены.

Изменение имени таблицы в базе данных SQLite при помощи команды ALTER

Но еще команда ALTER может модифицировать таблицы, но, опять же, к сожалению, SQLite может только изменять имена таблиц. Давайте посмотрим, как при помощи команды ALTER можно изменить имя таблицы в SQLite. Давайте для начала создадим таблицу в базе данных SQLite, воспользовавшись командой CREATE TABLE:

[php]CREATE TABLE table2 (a, b, c, d);[/php]

Мы создали простую таблицу со столбцами a, b, c, d. Теперь давайте модифицируем таблицу командой ALTER:

[php]ALTER TABLE table2 RENAME TO table02;[/php]

Убедиться в том, что имя таблицы изменилось, можно SQLite командой .tables. Для изменения имя таблицы можно использовать не только ее имя, но и квалификатор: полное имя таблицы. Давайте изменим имя таблицы SQLite при помощи квалификатора:

[php]ALTER TABLE sampledb.table02 RENAME TO table2;[/php]

Мы изменили имя таблицы, используя квалификатор, замечу, что при указании нового имени его использовать не стоит. На этом можно было бы закончить рассказ про переименование таблиц в SQLite, но есть здесь и несколько тонкостей:

1. Когда вы переименовываете таблицы в SQLite, то триггеры и индексы, которые были закреплены за таблицей за ней и остаются.
2. Но, если внутри триггера использовалось старое имя таблицы, то вам придется удалить старый триггер и создать новый с новым именем таблицы, который использовалось при переименовании.
3. Такая же участь будет и у представлений (просмотров, VIEW). Чтобы VIEW корректно работали после переименования таблиц в базах данных SQLite, вам необходимо их пересоздать.

Вот такие вот неудобства есть при переименовании таблиц в SQlite.

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

Как сделать любую модификацию таблицы в базе данных SQLite

Давайте посмотрим на то, как мы можем вносить любые изменения в столбцы таблиц базы данных SQlite3: как можем поменять тип данных столбца в SQLite, как поменять значение по умолчанию для столбца в SQLite и как можно поменять ограничения столбца в SQLite, и даже как переназначить первичный ключ таблицы в SQLite. На самом деле, когда вы делаете такие изменения, вам следует быть осторожным, поскольку при таких изменениях может быть нарушена внутренняя логика и база данных перестанет корректно работать.

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

Второе, что необходимо для изменения столбцов в SQLite–использовать транзакции. Использование транзакций обезопасит вашу базу данных от различных сбоев. О транзакциях мы подробно поговорим в дальнейшем.

Давайте для примера создадим таблицу example:

[php]CREATE TABLE example (a, b, c, d);[/php]

Вы создали таблицу example, но не указали: ни типы данных для столбцов, ни ограничения, ни первичный ключ, нет у вас тут и индексов и столбцы как-то непонятно называются. Хорошо, что таблица пустая, ее можно просто удалить таблицу из базы данных и создать нормальную с подробным описанием. Однако бывают ситуации, когда в таблице уже несколько тысяч записей, а команда ALTER в SQLite не позволяет: переименовывать столбцы, добавлять индексы в таблицу, изменять первичный ключ, изменять тип данных столбцов, удалять столбцы. Всё, что может команда ALTER – добавить столбец и изменить имя таблицы. Но нам этого мало, поэтому смотрим пример, сначала приведу общий синтаксис.

ALTER TABLE example RENAME TO _example_old;

CREATE TABLE example (

( id datatype [ NULL | NOT NULL ],

name datatype [ NULL | NOT NULL ],

INSERT INTO example (id, name, age, address)

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

1. Отключаем внешние ключи, если они включены.
2. Запускаем транзакцию командой BEGIN TRANSACTION (кстати, это можно сделать и при помощи команды SAVEPOINT).
3. Изменяем имя существующей таблицы на любое другое.
4. Создаем новую таблицу с аналогичной, подобной, похожей структурой и даем столбцам нужные описания.
5. Добавляем данные в новую таблицу из старой при помощи команды INSERT с подзапросом SELECT.
6. Завершаем транзакцию командой COMMIT, обратите внимание: если вы используете команду ROLLBACK, то изменения не вступят в силу.
7. И затем опять включаем поддержку внешних ключей.

Думаю, принцип понятен и каких-то подробных примеров приводить не нужно, скажу лишь, что если вы хотите удалить столбец из таблицы SQLite, то при создании новой таблицы просто его не указывайте, а так же не используйте столбец в подзапросе SELECT, который хотите удалить. Если всё-таки нужны будут подробные примеры изменения и модификации столбцов и таблиц в SQLite, напишите об этом в комментариях.

Еще записи о создании сайтов и их продвижении, базах данных, IT-технология и сетевых протоколах

• Часть 11.7: Индексы в базах данных SQLite. Индексация таблиц в SQLite3. Алгоритм B-дерева в базах данных
• Часть 10.1: Создание таблиц в базах данных SQLite
• Часть 6.2: Команда ALTER в SQLite3. Оператор ALTER в SQLite3
• Часть 12.14: Объединение таблиц в SQL и базах данных SQLite: JOIN и SELECT
• Тема 10: Работа с таблицами в базах данных SQLite3
• Часть 10.6: Модификация и обновление данных в таблицах базы данных SQLite
• Часть 11.2: Ограничения уровня таблицы в базах данных SQLite3
• Часть 11.5: Каскадная модификации данных в базах данных SQLite3

Возможно, эти записи вам покажутся интересными

Related Posts

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Эта…

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В…

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3.…

Как добавить новый столбец в существующую таблицу sqlite3

Подскажите пожалуйста, есть база в которой есть таблица users (в ней уже много строк) со столбцами name | age | city , мне необходимо добавить новый столбец status и туда сразу поместить значение 0 для всех строк. Подскажите какой запрос это реализует. непойму можно ли сразу через ALTER TABLE добавить его значение 0

Отслеживать
задан 24 ноя 2022 в 11:34
27 6 6 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Точно так же, как и при создании таблицы, вы можете указать в конце определения столбца значение по умолчанию с помощью DEFAULT .
Что-то такое должно получиться:

ALTER TABLE users ADD status INTEGER DEFAULT 0; 

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