@sqlite

Часть 9: Транзакции в базах данных. BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT

2022-01-06T16:33:43.720Z

Напомню, что команда BEGIN TRANSACTION говорит СУБД о том, что клиент хочет начать транзакцию и при этом СУБД должна изолировать данные, с которыми будет работать пользователь в транзакции. В этой статье мы ознакомимся с синтаксисом команды BEGIN TRANSACTION и некоторыми ее особенностями в SQLite3.

Синтаксис и использование оператора BEGIN TRANSACTION в SQLite

Рассмотрим первую команду из группы управления транзакциями в SQLite3. Команда BEGIN TRANSACTION в SQLite позволяет начать транзакцию. Давайте рассмотрим синтаксис команды BEGIN TRANSACTION в SQLite3.

Команда BEGIN TRANSACTION в базах данных SQLite

Синтаксис команды BEGIN TRANSACTION в базах SQLite3 довольно прост: сначала идут два ключевых слово BEGIN TRANSACTION, а далее уже идут команды транзакции. Этот синтаксис СУБД SQLite3 позволяет упростить еще сильнее, мы можем не писать ключевое слово TRANSACTION.

SQLite3 позволяет выбрать режим блокировки при выполнении транзакции, для этого используются ключевые слова: DEFERRED, IMMEDIATE, EXCLUSIVE.

DEFERRED - База данных блокируется на запись при выполнение первого оператора чтения или записи, который находится внутри тела транзакции (после команды BEGIN). При такой блокировки база данных будет доступна другим пользователям только для чтения, но не для записи. Режим DEFERRED является стандартным в SQLite3 и его можно не указывать.

IMMEDIATE - Выполнит блокировку базы данных после ключевого слова BEGIN.

EXCLUSIVE - Заблокирует базу данных для других пользователей не только на запись, но и на чтение. Блокировка происходит после ключевого слова BEGIN.

Команда COMMIT в базах данных SQLite (оператор COMMIT в SQLite3)

Рассмотрим вторую команду управления транзакциями в базах данных SQLite. COMMIT TRANSACTION в SQLite3 позволяет подтвердить операции, выполненные внутри транзакции. Хочу обратить ваше внимание, что SQL предложение, содержащее команду BEGIN и предложение, содержащее команду COMMIT – это две разных команды для СУБД, между которыми может быть множество запросов к базе данных распределенных во времени. Оператор COMMIT в базах данных SQLite подтверждает изменения, внесенные транзакцией. Давайте взглянем на синтаксис команды COMMIT в SQLite3. Хотя я и использую термин оператор, вместо команда, позволю напомнить, что SQL операторы и команды — это разные языковые конструкции.

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

Синтаксис оператора COMMIT TRANSACTION в SQLite3 очень прост: ключевое слово COMMIT, которое можно заменить словом END, за которым идет ключевое слово TRANSACTION. SQLite3 позволяет не писать ключевое слово TRANSACTION. Давайте посмотрим на примере работу команды COMMIT TRANSACTION в базах данных SQLite3.

Пример использования оператора COMMIT в базах данных SQLite

--Начинаем транзакцию

BEGIN;

-- Выберем первых 10 записей из таблицы City

SELECT * FROM city LIMIT 10;

-- Удаляем первую строку из таблицы city

DELETE FROM city WHERE  id = 1;

-- Посмотрим на первых 10 записей

SELECT * FROM city LIMIT 10;

/* 
 * Пока вы не выполните команду COMMIT,
 * строка не будет удалена, так как транзакция еще не подтверждена,
 * в этом можно убедиться, подключившись к базе данных World
 * другим клиентом, выполнив SELECT
 */

COMMIT;

На самом деле это пример ради примера, удалить первую строку из таблицы командой DELETE мы могли бы и не используя транзакции. Так же хочу заметить, что если вы попробуете установить второе соединение с базой данных World в тот момент, когда транзакция не завершена, то таблица City будет доступна только для чтения из-за свойства изоляции транзакции. А теперь, воспользовавшись командой SELECT, убедимся, что внесенные изменения вступили в силу.

-- Посмотрим на первых 10 записей после подтверждения транзакции

SELECT * FROM city LIMIT 10;

По сути, пока вы не выполните команду COMMIT, SQLite3 не внесет ни одного изменения в базу данных (не выполнит ни одну команду после оператора BEGIN). Как только вы сказали SQLite COMMIT, все изменения будут выполнены.

Команда ROLLBACK в базах данных SQLite (оператор ROLLBACK в SQLite3)

Рассмотрим третью команду из группы управления транзакциями – команду ROLLBACK TRANSACTION. Команда ROLLBACK TRANSACTION в базах данных SQLite используется для отмены запросов, введенных после команды BEGIN. Если команда COMMIT подтверждает изменения, то оператор ROLLBACK TRANSACTION их откатывает и завершает транзакцию. Давайте посмотрим на синтаксис команды ROLLBACK в базах данных SQLite3.

Синтаксис команды ROLLBACK TRANSACTION в SQLite. Синтаксис оператора ROLLBACK TRANSACTION в базах данных SQLite3

Синтаксис оператора ROLLBACK в SQLite3 очень прост: сначала идет ключевое слово ROLLBACK, а затем идет ключевое слово TRANSACTION, которое можно не писать. Сейчас не обращайте внимание на SAVEPOINT, на мой взгляд это отдельная конструкция, которую мы рассмотрим в следующей записи. Давайте рассмотрим пример использования команды ROLLBACK TRANSACTION в SQLite.

Для примера использования оператора ROLLBACK TRANSACTION в SQLite3 будем использовать демонстрационную базу данных World.db3. Совет: если вы самостоятельно будете повторять пример ROLLBACK TRANSACTION в SQLite, то выполняйте все команды по очереди, а не копируйте весь листинг целиком.

--Начинаем транзакцию

BEGIN;

/*
 * Выберем первых 10 записей из таблицы City
 * (в результате мы получим записи со 2
 * по 11, т.к. ранее мы уже удалили запись с id =1)
 */

SELECT * FROM city LIMIT 10;

-- Удаляем вторую строку из таблицы city

DELETE FROM city WHERE  id = 2;

-- Посмотрим на первых 10 записей и увидим, что вывелись записи с 3 по 12

SELECT * FROM city LIMIT 10;

/*
 * Пока вы не выполните команду ROLLBACK,
 * транзакция не будет завершена,а вы
 * будете видеть все изменения
 */

ROLLBACK;

А теперь давайте убедимся в том, что оператор ROLLBACK TRANSACTION сработал и мы не внесли никаких изменений в таблицу базы данных SQLite3. Для этого мы выведем записи со второй по одиннадцатую командой SELECT:

/*
 * Посмотрим на первых 10 записей после подтверждения транзакции,
 * вы увидите записи со 2 по 11
 */
 
SELECT * FROM city LIMIT 10;

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

Не важно какие команды выполнялись внутри транзакции, команда ROLLBACK TRANSACTION отменит любые. Вы создали базу данных командой CREATE или вы добавили несколько строк в таблицу командой INSERT, да даже если вы удалили таблицу командой DROP... ROLLBACK TRANSACTION вернет базу данных к тому состоянию, в каком она была до транзакции.

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

Команда SAVEPOINT в базах данных SQLite (оператор SAVEPOINT в SQLite3)

Бывают такие ситуации, когда нужно отменить операции произведенные в транзакции, но не завершать ее и эта запись, как раз таки и покажет вам, как это сделать.

В базах данных SQLite для таких целей есть команда SAVEPOINT. Оператор SAVEPOINT является, с одной стороны, псевдонимом команд COMMIT и BEGIN, но в тоже время, использование оператора SAVEPOINT в базах данных SQLite3 позволяет откатить транзакцию на контрольную точку, но не завершать ее, чтобы выполнить операции повторно. В этой статье мы с вами разберем синтаксис команды SAVEPOINT, особенности ее реализации в SQLite и посмотрим несколько примеров использования SAVEPOINT в SQLite3.

Команда SAVEPOINT в базах данных SQLite – это еще один способ начать транзакцию. Команда SAVEPOINT в SQLite3 может работать, как самостоятельно, так и внутри конструкции BEGIN … COMMIT. По сути, оператор SAVEPOINT в SQLite3 создает транзакцию с именем, имя транзакции может быть не уникальным, а работает транзакция SAVEPOINT в базах данных SQLite3 в режиме DEFERRED, о котором мы говорили, когда рассматривали оператор BEGIN. Давайте рассмотрим синтаксис команды SAVEPOINT в SQLite.

Синтаксис команды SAVEPOINT в базах данных SQLite3 при завершении транзакции с подтверждением изменений

Синтаксис команды SAVEPOINT в SQLite3 при успешном завершении транзакции выглядит следующим образом: начинаем транзакцию командой SAVEPOINT, далее даем имя нашей транзакции, после чего следует набор SQL запросов внутри транзакции, а подтверждение транзакции происходит при помощи команды RELEASE SAVEPOINT и название метки. SQLite3 позволяет SAVEPOINT не писать, когда мы хотим завершить транзакцию. Рассмотрим синтаксис оператора SAVEPOINT в базах данных SQLite3 в том случае, когда нам необходимо отменить изменения.

Синтаксис оператора SAVEPOINT в базах данных SQLite с откатом внесенных изменений

Если нам необходимо отменить изменения транзакции в SQLite3 предусмотрен второй вариант использования SAVEPOINT: начинаем транзакцию с ключевого слова SAVEPOINT, за которым идет имя транзакции, далее следует набор запросов, после которого для отмены транзакции идет ключевое слово ROLLBACK TRANSACTION TO SAVEPOINT и имя транзакции. SQLite позволяет опускать слово TRANSACTION и в этом случае. Давайте рассмотрим примеры использования команды SAVEPOINT в SQLite3.

Для примера работы SAVEPOINT в SQLite будем использовать базу данных World.db3 из предыдущих записей. Если вы помните, мы уже удалили первую строку из таблицы City. Совет: если вы самостоятельно будете повторять пример SAVEPOINT в SQLite3, то выполняйте все команды по очереди, а не копируйте весь листинг целиком.

-- Создаем транзакцию с именем при помощи команды SAVEPOINT

SAVEPOINT  transact1;

-- Посмотрим первых 10 записей из таблицы City

SELECT * FROM City LIMIT 10;

-- Удалим запись с id = 2

DELETE FROM city WHERE  id = 2;

-- Посмотрим на первых 10 записей

SELECT * FROM city LIMIT 10;

/*
 * Пока вы не выполните команду RELEASE SAVEPOINT,
 * строка не будет удалена, так
 * как транзакция еще не подтверждена,
 * в этом можно убедиться, подключившись к
 * базе данных World другим клиентом, выполнив SELECT
 */
 
RELEASE transact1;
/*
 * Посмотрим на первых 10 записей после подтверждения транзакции,
 * строки с id = 2 вы не увидите
 */

В принципе, ничего хитрого внутри транзакции мы не сделали: воспользовавшись оператором SELECT мы посмотрели первых 10 строк из таблицы и удалили строку с id = 2 при помощи оператора DELETE. Эти действия можно было бы сделать не используя транзакцию и оператор SAVEPOINT. Убедиться в том, что изменения были подтверждены можно, опять же, воспользовавшись командой SELECT:

SELECT * FROM city LIMIT 10;

Так работает оператор SAVEPOINT в SQLite3 при успешном завершении транзакции. Давайте теперь посмотрим, как работает SAVEPOINT в SQLite3 в том случае, если транзакцию нужно откатить и завершить, как это было, когда мы рассматривали команду ROLLBACK TRANSACTION.

--Начинаем транзакцию с именем transact1

SAVEPOINT  transact1;

/*
 * Выберем первых 10 записей из таблицы City
 * (в результате мы получим записи с 3
 * по 12, т.к. ранее мы уже удалили запись с id =2)
 */

SELECT * FROM city LIMIT 10;

-- Удаляем третью строку из таблицы city

DELETE FROM city WHERE  id = 3;

-- Посмотрим на первых 10 записей и увидим, что вывелись записи с 4 по 13

SELECT * FROM city LIMIT 10;

/*
 * Пока вы не выполните команду ROLLBACK,
 * транзакция не будет завершена, а вы
 * будете видеть все изменения
 */
 
ROLLBACK;

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

/*
 * Посмотрим на первых 10 записей после подтверждения транзакции,
 * вы увидите записи с 3 по 12
 */
 
SELECT * FROM city LIMIT 10;

Так работает команда SAVEPOINT в базах данных SQLite3 при завершении транзакции с откатом. Давайте теперь посмотрим, как работает оператор SAVEPOINT в SQLite3 в том случае, если транзакцию нужно откатить, но не завершить.

--Начинаем транзакцию с именем transact1

SAVEPOINT  transact1;

/*
 * Выберем первых 10 записей из таблицы City
 * (в результате мы получим записи с 3
 * по 12, т.к. ранее мы уже удалили запись с id =2)
 */
 
SELECT * FROM city LIMIT 10;

-- Удаляем третью строку из таблицы city

DELETE FROM city WHERE  id = 3;

-- Посмотрим на первых 10 записей и увидим, что вывелись записи с 4 по 13

SELECT * FROM city LIMIT 10;

/*
 * Пока вы не выполните команду ROLLBACK TRANSACTION TO SAVEPOINT,
 * отката изменений не произойдет, и вы будете видеть все изменения
 */
 
ROLLBACK TRANSACTION TO SAVEPOINT transact1;

/*
 * Посмотрим на первых 10 записей после подтверждения транзакции,
 * вы увидите записи с 3 по 12
 */
 
SELECT * FROM city LIMIT 10;

Хочу обратить ваше внимание на то, что последний запрос SELECT будет выполняться в рамках транзакции transact1, так как мы сделали откат до метки transact1, то есть отменили все запросы, начиная с SAVEPOINT transact1, но транзакцию не завершили. Так же хочу обратить ваше внимание на следующий момент, когда я писал о SAVEPOINT я использовал два термина: первый — команда, второй -оператор. Первый термин правильный с формальной точки зрения, второй более популярный среди русскоязычных разработчиков. Про SQL операторы на моем блоге есть отдельная статья.

Часть 10.1: Создание таблиц в базах данных SQLite

2022-01-03T14:21:13.055Z

Синтаксис создания таблиц в базе данных SQLite3

Давайте повторим команду CREATE TABLE и посмотрим, как можно создавать таблицы в базах данных SQLite. Сперва вспомним общий синтаксис создания таблиц в SQLite, он показан на рисунке ниже.

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

Обратите внимание: создавать таблицы в SQLite мы можем р азличным образом, но любое предложение для создания таблицы в SQLite начинается с CREATE TABLE. Если мы хотим создать временную таблицу в базе данных SQLite, то необходимо использовать ключевое слово TEMP или TEMPORARY. Хочу обратить ваше внимание на то, что имена таблиц должны быть уникальны во всей базе данных, поэтому, если вы создаете таблицу, но у вас есть подозрение, что таблица с таким именем существует, то можете использовать конструкцию IF NOT EXISTS, таким образом SQLite сначала проверит: существует ли таблица с таким именем, а уже затем создаст новую, если таблицы с таким именем нет. В базах данных SQLite вы можете создавать таблицы, используя так называемый квалификатор: полное имя таблицы. Для этого, перед именем таблицы вам следует указать имя базы данных, в которой вы хотите создать таблицу.

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

Создания таблицы в базе данных SQLite без описания столбцов

Создание таблицы в базе данных SQLite без указания типа данных (на самом деле в SQLite нет типов данных, а есть классы данных) для столбца выглядит следующим образом:

CREATE TABLE table1 (a, b, c, d);

Как вы видите, в данном случае мы создадим таблицу с именем table1, в которой четыре столбца: a, b, c, d. Для столбцов в данном случае не указан класс данных, а так же не указаны ограничения, которые нужны для поддержания целостности данных.

Создание таблицы в базе данных SQLite3 с описанием столбцов и ограничениями

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

CREATE TABLE table2 (
  a INTEGER PRIMARY KEY NOT NULL,
  b TEXT  UNIQUE NOT NULL,
  c REAL NOT NULL
);

В первом примере мы создали таблицу table2 со столбцами a, b, c. И задали для каждого столбца описание: указали тип данных и ограничения: NOT NULL и PRIMARY KEY. Обратите внимание на терминологию SQLite знать не знает про реляционную базу данных и о том, что такое ключ или ключевой атрибут. Для SQLite первичный ключ – это ограничение, то есть некоторое правило, которое нельзя нарушать. Обратите внимание на то, что параметры столбца разделяются между собой пробелами. А теперь давайте посмотрим второй пример создания таблицы в базе данных SQLite с указанием типов данных и ограничений:

CREATE TABLE table3 (
  a INTEGER NOT NULL,
  b TEXT  UNIQUE NOT NULL,
  c REAL NOT NULL,
  PRIMARY KEY(a)
);

В данном случае мы объявили столбец «a» первичным ключом таблицы после описания столбцов, так тоже можно делать. Еще хочу обратить ваше внимание на то, что SQLite при создании таблицы неявно указывает сравнение данных для столбца. Если вам нужно явно указать SQLite как нужно сравнивать данные из столбца — используйте ключевое слово COLLATE.

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

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

CREATE TABLE world.table4 (
  a INTEGER NOT NULL,
  b TEXT UNIQUE NOT NULL,
  c REAL NOT NULL,
  PRIMARY KEY(a)
);

Синтаксис создания таблицы с использованием квалификатора ничем не отличается от предыдущих примеров, но вместо имени таблицы теперь использован квалификатор: world.table4.

Проверить и создать таблицу в базе данных, если она не существует

Когда вы создаете новые таблицы, то не всегда знаете структура базы данных, а, соответственно, не всегда знаете имена уже существующих таблиц, поэтому SQLite позволяет проверять имена таблиц на уникальность перед их созданием, для этого используется конструкция IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS TABLE table5 (
  a INTEGER NOT NULL,
  b TEXT  UNIQUE NOT NULL,
  c REAL NOT NULL,
  PRIMARY KEY(a)
);

Если таблицы с именем table5 в базе данных не будет, то SQLite создаст эту таблицу, если таблица table5 будет в базе данных, то таблица создана не будет.

Создание временных таблиц в базе данных SQLite3

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

CREATE TEMP TABLE table6 (
  a INTEGER NOT NULL,
  b TEXT  UNIQUE NOT NULL,
  c REAL NOT NULL,
  PRIMARY KEY(a)
);

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

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

.tables

Временные таблицы в SQLite имеют пометку temp. Вместо ключевого слова TEMP для создания временной таблицы можно использовать ключевое слово TEMPORARY:

CREATE TEMPORARY TABLE table7 (
  a INTEGER NOT NULL,
  b TEXT  UNIQUE NOT NULL,
  c REAL NOT NULL,
  PRIMARY KEY(a)
);

И затем проверить, что временная таблица действительно создалась.

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

.tables

Создание таблиц в базе данных SQLIte по запросу SELECT

Теперь посмотрим на второй вариант создания таблиц в SQLite, который заключается в использование ключевого слова AS и команды SELECT. Всё очень просто: сперва вы пишите CREATE TABLE, затем указываете имя таблицы, вместо имени можно использовать квалификатор, затем идет ключевое слово AS, после которого идет подзапрос SELECT, который, на самом деле, может быть сколь угодно длинным.

CREATE TABLE IF NOT EXISTS table8 AS

SELECT id, name, population FROM city LIMIT 15;

После чего можно проверить, что таблица в базе данных была действительно создана, воспользовавшись командой SELECT.

SELECT * FROM table8;

А потом попробуйте воспользоваться командой .schema (на моем блоге есть публикация обо всех полезных командах шелла SQLite3), чтобы посмотреть каким запросом была создана таблица в SQLite.

.schema table8

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

CREATE TABLE IF NOT EXISTS table9 AS

SELECT id AS 'Nomer', name AS 'Gorod', population
AS 'naselenie' FROM city LIMIT 15;

Затем выполним SELECT, чтобы убедиться в том, что имена столбцов будут отображаться, как Nomer, Gorod и naselenie.

SELECT * FROM table9;

И посмотрим схему только что созданной таблицы.

.schema table9

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

Часть 11.3: Первичные ключи в базах данных SQLite: PRIMARY KEY. Ограничение первичного ключа

2022-01-03T09:26:42.952Z

В теории баз данных ключ или ключевой атрибут — это столбец, который позволяет однозначно идентифицировать таблицу в базе данных. На практике, ни одна СУБД в мире не знает о таком столбце и не знает теории баз данных. На практике есть первичный ключ, который является правилом, которое SQLite ни когда не нарушит. SQLite будет следить всегда за тем, чтобы в столбце PRIMARY KEY были всегда уникальные и вечные значения.

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

Первичный ключ, как ограничение уровня таблицы в базах данных SQLite. PRIMARY KEY в SQLIte3

Первичный ключ, это не только ключевой атрибут, который идентифицирует таблицу в базе данных и даже не только обязательное условие второй нормальной формы, а, соответственно, и третьей нормальной формы. Первичный ключ или столбец PRIMARY KEY – это ограничение уровня таблицы для любой реляционной СУБД.

Ограничение уровня таблицы – это правило, которое никогда не должна нарушать ни одна СУБД и не должна никому давать это делать. Первичный ключ или PRIMARY KEY в базах данных SQLite – это столбец, значения которого должны быть уникальными и вечными, и что бы не произошло, что бы не случилось, SQLite никогда не нарушит правило уникальности и вечности первичного ключа.

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

Когда мы создаем первичный ключ, как ограничение таблицы, то вместо PRIMARY KEY мы вправе использовать конструкцию UNIQUE KEY, SQLite это позволяет и никаких проблем с таким подходом у вас не будет.

Помимо всего прочего, первичный ключ в базе данных SQLite, вернее столбец, который мы назначили, как PRIMARY KEY, является еще и индексом таблицы. Благодаря индексам таблицы операция выборки данных из базы данных при помощи команды SELECT происходит значительно быстрее, но место на диске база данных начинает занимать больше

Вывод: первичный ключ в базах данных SQLite3 или PRIMARY KEY – это правила, которые нельзя нарушать: правила уникальности и вечности значений столбца PRIMARY KEY. Так же первичный ключ является индексом таблицы в базе данных SQLite. Столбец, который объявлен, как PRIMARY KEY – это индекс, которому мы можем даже дать имя.

Пример использования первичного ключа в SQLite. Пример PRIMARY KEY в SQLite

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

Давайте теперь на практике посмотрим, как первичный ключ может обеспечить целостность данных в базе данных и посмотрим, как в SQLite можно объявить столбец с ограничением PRIMARY KEY.

Создадим таблицу в базе данных с именем table1 при помощи команды CREATE:

CREATE TABLE table1 (
 a INTEGER PRIMARY KEY,
 b TEXT NOT NULL,
 c REAL
);

Первый столбец мы объявили первичным ключом таблицы table1 при помощи ключевой фразы PRIMARY KEY. Теперь давайте смотреть, как SQLite будет относиться к значениям, которые мы будем добавлять в первый столбец. Чтобы добавить строку в таблицу, нужно воспользоваться командой INSERT.

INSERT INTO table1 (NULL, 'Hello, World!', 0.2);

Мы пробуем добавить значение NULL в столбец PRIMARY KEY, и у нас ничего не получается, первичный ключ не может иметь значение NULL, так как значение NULL нельзя ни с чем сравнить однозначно. А ошибку SQLite напишет такую: Error: near «NULL»: syntax error. Давайте теперь попробуем «обмануть» SQLite и добавим две строки:

-- Добавляем две строки в таблицу,
-- не указывая значение для столбца PRIMARY KEY

INSERT INTO table1 (b, c) VALUES ('какой-то текст', 0.5);
INSERT INTO table1 (b, c) VALUES ('какой-то текст', 0.7);

SQLite добавляет эти строки в таблицу, хотя мы не указывали значение для первого столбца, давайте посмотрим на результат, сделав выборку данных из базы данных при помощи команды SELECT:

-- Делаем выборку из базы данных и смотрим результат

SELECT*FROM table1;

1|какой-то текст|0.5
2|какой-то текст|0.7

Как видите, в SQLite3 необязательно указывать ограничение уровня столбца AUTOINCREMENT для столбца с аффинированным типом данных INTEGER, который объявлен, как первичный ключ (PRIMARY KEY). А теперь попробуем добавить строку со значение a = 2 в таблицу table1 (это значение уже есть в таблице и SQLite не должна нам дать возможность добавить такую строку ).

INSERT INTO table1 (2, 'Hello, World!', 0.2);

SQLite заблокировала попытку добавления строки со значение a =2, так как правилом первичного ключа является его уникальность. Давайте подсунем SQLite в столбец «a» строку ‘2’. Тип данных будет другим, но, как мы знаем SQLite – это СУБД с динамической типизацией данных:

INSERT INTO table1 ('2', 'Hello, World!', 0.2);

Да, та же самая ошибка: Error: near «0.2»: syntax error. В данном случае, перед тем, как записать значение в таблицу SQLite его преобразовало в число, так как мы объявили для столбца «а» класс данных INTEGER, а затем сравнила значения из таблицы с тем, что мы хотели добавить, после чего заблокировала операцию добавления данных.

Теперь давайте попробуем изменить значение столбца «а». Для изменения данных в базе данных SQLite есть специальная команда UPDATE, воспользуемся ей:

UPDATE table1 SET a = 2 WHERE c = 0.5;
UPDATE table1 SET a = 3 WHERE c = 0.7;

Когда мы первый раз используем команду UPDATE, SQLite заблокирует нам эту операцию, так как первичный ключ должен быть уникальным, а значение 2 уже есть в столбце «а». Второй UPDATE будет выполнен, так как наша таблица еще не связана с другими таблицами и здесь не произойдет нарушение правила уникальности. Убедимся в этом при помощи команды SELECT:

-- Делаем выборку, чтобы убедиться, что второй UPDATE сработал

SELECT * FROM table1;

1|какой-то текст|0.5
3|какой-то текст|0.7

Если интересно, то при выполнении первого UPDATE, SQLite выдала ошибку: Error: UNIQUE constraint failed: table1.a. Но давайте немного изменим пример, пусть первичный ключ нашей таблицы будет с аффинированным типом данных TEXT. Создадим таблицу table2:

CREATE TABLE table2 (
  a TEXT PRIMARY KEY,
  b TEXT NOT NULL,
  c REAL
);

Структура таблицы та же, что и в первом случае, но теперь столбец PRIMARY KEY имеет класс данных TEXT. Выполним первый INSERT из примера с типом данных INTEGER:

INSERT INTO table2 (NULL, 'Hello, World!', 0.2);

Ограничение первичного ключа сработало, мы получили ошибку, а это значит, что SQLite не добавила значение NULL в столбец PRIMARY KEY с типом данных TEXT.Выполним второй INSERT, не указав никаких значений для первичного ключа:

INSERT INTO table2 (b, c) VALUES ('какой-то текст', 0.5);

SQLite добавила строку в базу данных, несмотря на то, что мы не указывали никаких значений для столбца PRIMARY KEY, давайте сделаем SELECT:

SELECT * FROM table2;

|какой-то текст|0.5

SELECT typeof(a), typeof(b), typeof(c) FROM table2;

null|text|real

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

А теперь в таблицу table2, попробуем добавить две строки:

INSERT INTO table2 (a,b,c) VALUES (5, 'Hello, World!', 0.2);
INSERT INTO table2 VALUES ('5', 'Hello, World!', 55);

Первая строка будет добавлена, во второй строке мы получим ошибку. Это произошло из-за того, что SQLite перед тем, как добавить первую строку, преобразовал число 5 в строку ‘5’, поэтому на вторую строку было наложено правило уникальности первичного ключа.

А теперь давайте создадим таблицу table3 с первичным ключом PRIMARY KEY, для которого мы не станем указывать тип данных и попробуем повторить последний эксперимент:

-- Создаем таблицу с первичным ключом, но без типов данных

CREATE TABLE table3 (
  a PRIMARY KEY,
  b NOT NULL,
  c
);

-- Добавляем в эту таблицу две строки

INSERT INTO table3 VALUES ('5', 'Hello, World!', 55);
INSERT INTO table3 (a,b,c) VALUES (5, 'Hello, World!', 0.2);

Все команды будут выполнены успешно, так как для столбца, объявленного, как PRIMARY KEY не был указан аффинированный тип данных, а значение 5 и ‘5’ разные значения, так как первое – это число, а второе – строка.

PRIMARY KEY, как индекс в базах данных

Мы утверждаем, что первичный ключ – это ограничение уровня таблицы, но столбец PRIMARY KEY мы объявляем, как ограничение уровня столбца, на самом деле, такая форма записи не совсем правильная. Давайте разберемся, почему это так и как нужно делать правильно. Во-первых, стоит сказать, что ограничения уровня таблицы – это такие же объекты баз данных, как и сами таблицы и мы можем им давать имена, которые должны быть уникальны во всей базе данных.

А теперь давайте объявим столбец PRIMARY KEY, как ограничение уровня таблицы:

CREATE TABLE table4(
  a INTEGER,
  b TEXT NOT NULL,
  c REAL,
  PRIMARY KEY (a)
);

Напишите команду .schema, чтобы увидеть, какой запрос создал данную таблицу. Но, мы утверждали, что первичный ключ – это ни что иное, как индекс таблицы в базе данных SQLite3 и что у столбца PRIMARY KEY может быть имя, давайте этом посмотрим:

CREATE TABLE table5(
  a INTEGER,
  b TEXT NOT NULL,
  c REAL,
  CONSTRAINT ixpk PRIMARY KEY (a)
);

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

SELECT * FROM sqlite_master WHERE type = 'index';

Мы создали первичный ключ, объявив столбец «а», как ограничение уровня таблицы при помощи конструкции CONSTRAINT ixpk PRIMARY KEY (a) после объявления всех столбцов, но, в то же время, мы создали индекс с именем ixpk для таблицы table5.

Составной первичный ключ в базах данных SQLite

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

CREATE TABLE table6(
  a INTEGR NOT NULL,
  b TEXT,
  c REAL,
  CONSTRAINT new_pk PRIMARY KEY (a, b, c)
);

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

INSERT INTO table6 (a,b,c) VALUES (5, "Hello, World!", 0.2);
INSERT INTO table6 (a,b,c) VALUES (5, "Hello, World!", 0.8);
INSERT INTO table6 (a,b,c) VALUES (25, "Hello, World!", 0.2);
INSERT INTO table6 (a,b,c) VALUES (5, "Hello, World!", 0.2);

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

Часть 7.2: Команда INSERT в SQLite3. Оператор INSERT в SQLite3

2022-01-03T08:55:04.085Z

Команда INSERT в SQLite3. Оператор INSERT в SQLite3

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

Библиотека SQLite3 позволяет нам добавлять новые строки в таблицы базы данных. Для добавления новых строк используется команда INSERT, эта команда есть в любой СУБД. Добавлять новые строки при помощи команды INSERT в SQLite3 можно только в таблицы базы данных. Ниже вы сможете увидеть синтаксис команды INSERT в SQLite3.

Синтаксис команды INSERT в SQLite3. Синтаксис оператора INSERT в SQLite3

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

Примеры использования команды SELECTв SQLite3
Давайте посмотрим несколько примеров использования команды INSERT в SQLite3.

-- Создадимпростуютаблицу
CREATETABLEtable1 (a, b,c,d);

-- Добавим несколько значений в таблицу table1 при помощи команды INSERT
INSERT INTO table1  (a, b, c, d)
VALUES (1, 'Петров', 'Русский', 24);

INSERT INTO table1  (a, b, c, d)
VALUES (2, 'Нурутдинов', 'Татарин', 28);

INSERT INTO table1  (a, b, c, d)
VALUES (3, 'Иванько', 'Украинец', 31);

INSERT INTO table1  (a, b, c, d)
VALUES (4, 'Горлукович', 'Белорус', 18);

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

-- Выведем таблицу table1 на экран при помощи команды SELECT
SELECT * FROM table1;

В результате мы увидим следующее:

sqlite> SELECT * FROM table1;

a       b               c           d

1       Петров          Русский     24
2       Нурутдинов      Татарин     28
3       Иванько         Украинец    31
4       Горлукович      Белорус     18

sqlite>

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

-- Добавление данных в таблицу при помощи INSERTбез указания столбцов
INSERT INTO table1  VALUES (5, 'Сейтаридис', 'Грек', 48);

-- Проверим, что строка действительно была добавлена в таблицу
SELECT * FROM table1;

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

-- Создадим вторую таблицу с такой же структурой, как и у первой
CREATE TABLE table2(c1, с2, c3,с4);

-- Добавим во вторую таблицу данные из первой
-- при помощи команд INSERTи SELECT
INSERT INTO table2 (c1, с2, c3,с4)

SELECT a, b, c, d
FROM table1;

-- Проверим, что данные действительно были добавлены в таблицу
-- при помощи команды SELECT
SELECT * FROM table2;

Вторая таблица полностью дублирует первую, так как все данные были добавлены из первой таблицы:

sqlite>SELECT * FROMtable2;

c1      с2                 c3                с4

1       Петров           Русский             24
2       Нурутдинов       Татарин             28
3       Иванько          Украинец            31
4       Горлукович       Белорус             18
5       Сейтаридис       Грек                48

sqlite>