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

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

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

./sqlite3 your.db

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

.open your.db 

Выяснить, какая база данных является текущей, можно с помощью команды .databases утилиты sqlite3. Если вы работаете с одной БД, а потом открываете другую, то текущей становится вторая БД.

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

Таблицы базы данных создаются с помощью директивы CREATE TABLE языка 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);

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

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

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

Если нам не важно, какие конкретно идентификаторы будут записываться в поле _id, а важна только уникальность поля, следует назначить полю еще один ограничитель – автоинкремент – AUTOINCREMENT.

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

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

NOT NULL и DEFAULT

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

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

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

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

С помощью команд .schema и PRAGMA TABLE_INFO() можно посмотреть схему таблицы.

Внешний ключ

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

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

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

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

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

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

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