Создание и управление базами данных в SQLite

Создание и открытие базы данных

С помощью sqlite3 можно создать новую базу данных или открыть уже существующую двумя способами. Во-первых, при запуске утилиты sqlite3, указав имя базы данных как аргумент. Если база данных уже существует, она будет загружена; если нет, она будет создана и открыта автоматически.

./sqlite3 your.db

Во-вторых, вы можете воспользоваться командой изнутри программы:

.open your.db

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

Создание и удаление таблицы

Для создания таблиц в базе данных используется SQL-директива CREATE TABLE. После этой команды указывается название таблицы, а в круглых скобках прописываются имена столбцов и их типы данных:

sqlite> CREATE TABLE pages (
   ...> title TEXT,
   ...> url TEXT,
   ...> theme INTEGER,
   ...> num INTEGER);

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

Чтобы получить список всех таблиц в базе, используйте команду .tables.

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

Первичные ключи и автоинкремент

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

Для этой цели часто создается дополнительное поле с именем ID или подобным. В базах данных для Android такому столбцу часто присваивается имя _id.

sqlite> CREATE TABLE pages (
   ...> _id INTEGER,
   ...> title TEXT,
   ...> url TEXT,
   ...> theme INTEGER,
   ...> num INTEGER);

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

sqlite> CREATE TABLE pages (
   ...> _id INTEGER PRIMARY KEY,
   ...> title TEXT,
   ...> url TEXT,
   ...> theme INTEGER,
   ...> num INTEGER);

Если для вас не важны сами идентификаторы, а лишь их уникальность, рекомендуется добавлять ограничение автоинкремент – AUTOINCREMENT.

sqlite> CREATE TABLE pages (
   ...> _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> title TEXT,
   ...> url TEXT,
   ...> theme INTEGER,
   ...> num INTEGER);

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

NOT NULL и DEFAULT

Ограничение NOT NULL позволяет запретить пустые значения в поле. Обычное поведение подразумевает, что полю, не являющемуся первичным ключом, можно не назначать значение, и оно будет иметь NULL. С использованием NOT NULL объявление нового значения становится обязательным.

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

Например, сделаем поля url, theme и num обязательными для заполнения. К полю num назначим значение по умолчанию 0, если пользователь не укажет его. Создание такой таблицы будет выглядеть следующим образом:

sqlite> CREATE TABLE pages (
   ...> _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> title TEXT,
   ...> url TEXT,
   ...> theme INTEGER,
   ...> num INTEGER NOT NULL DEFAULT 0);

Чтобы изучить структуру таблицы, можно использовать команды .schema и PRAGMA TABLE_INFO().

Внешний ключ

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

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

sqlite> CREATE TABLE pages (
   ...> _id INTEGER PRIMARY KEY,
   ...> name TEXT);

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

sqlite> CREATE TABLE pages (
   ...> _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> title TEXT,
   ...> url TEXT NOT NUL,
   ...> theme INTEGER NOT NUL,
   ...> num INTEGER NOT NULL DEFAULT 100
   ...> FOREIGN KEY (theme) REFERENCES sections(_id)
   ...> );

FOREIGN KEY накладывает ограничение, препятствуя записи в столбце theme значений, которые не встречаются как первичные ключи в таблице sections. Однако в SQLite, поддержка внешнего ключа по умолчанию не активирована. Это значит, что даже назначив столбец внешним ключом, можно вводить любые значения.

Чтобы включить поддержку внешних ключей в sqlite3, используйте команду PRAGMA foreign_keys = ON;. Это предотвратит добавление записи в таблицу, если значение внешнего ключа не найдено в другой таблице как первичный ключ.