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

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

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

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

pl@desk:~$ sqlite3 first.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .databases
main: /home/pl/first.db r/w
sqlite> .open second.db
sqlite> .databases
main: /home/pl/second.db r/w
sqlite> .quit
pl@desk:~$ ls *.db
first.db  second.db

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

Таблицы базы данных создаются с помощью директивы CREATE TABLE языка SQL. После CREATE TABLE идет имя таблицы, после которого в скобках перечисляются имена столбцов и их тип:

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

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

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

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

sqlite> .open first.db
sqlite> CREATE TABLE pages (
(x1...> title TEXT,
(x1...> url TEXT,
(x1...> theme INTEGER,
(x1...> num INTEGER);
sqlite> .tables
pages
sqlite> DROP TABLE pages;
sqlite> .tables
sqlite>

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

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

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

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

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

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

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

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

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

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. В этом случае команда для создания таблицы будет такой:

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

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

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);
sqlite> .schema pages
CREATE TABLE pages (_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT,
                    url TEXT NOT NULL,
                    theme INTEGER NOT NULL,
                    num INTEGER NOT NULL DEFAULT 0);
sqlite> PRAGMA TABLE_INFO(pages);
0|_id|INTEGER|0||1
1|title|TEXT|0||0
2|url|TEXT|1||0
3|theme|INTEGER|1||0
4|num|INTEGER|1|0|0
sqlite>

Внешний ключ

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

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

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

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

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;. После этого добавить в таблицу запись, в которой внешний ключ не совпадает ни с одним первичным из другой таблицы, не получится.

sqlite> CREATE TABLE sections (_id INTEGER PRIMARY KEY, name TEXT);
sqlite> DROP TABLE pages;
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));
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite>