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

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

Мы поговорим про виды ограничений уровня столбца в SQLite и рассмотрим каждое ограничение столбца в отдельности на примерах: типы данных, которые в реляционных базах данных являются ограничениями, но, когда говорят про SQLite3, то данное утверждение не совсем верно; значение NOT NULL, которое тоже является ограничение столбца в SQLite и других СУБД, UNIQUE, CHECK, AUTOINCREMENT, DEFAULT и COLLATE — это ограничения уровня столбца. Для нас это может быть значение по умолчанию, уникальное значение или что-то еще, но для SQLite3 — это ограничения, их мы и рассмотрим.

Виды ограничений уровня столбца в базах данных SQLite

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

Сверху на рисунке изображен синтаксис создания ограничений уровня столбца при создании таблицы в SQLite3 при помощи команды CREATE. Хочу обратить ваше внимание, что мы бы не стали относить ограничение первичного ключа PRIMARY KEY к ограничениям уровня столбца. Так же стоит заметить, что конструкция CONSTRAINT name является необязательной и ее часто опускают при создании таблиц в базах данных SQLite.

Давайте перечислим ограничения уровня столбца и дадим короткую характеристику каждому ограничению:

1. Тип данных столбца является ограничением уровня столбца, хоть и не указан на рисунке сверху. На самом деле в SQLite нет типов данных, это понятие заменено здесь на класс данных, а столбцу мы задаем аффинированный тип данных, который используется для сравнения. Тип данных в любой реляционной СУБД ограничивает типы данных для значений, которые могут храниться в столбце.
2. Ограничение NOT NULL является ограничений уровня столбца. Если вы задали столбцу ограничение NOT NULL, то SQLite при добавлении строк в таблицу базы данных командой INSERT не даст вам «забыть» записать в этот столбец значение отличное от NULL.
3. Ограничению UNIQUE заставляет SQLite проверять уникальность значений в столбце таблицы. Если вы задали для столбца ограничение UNIQUE, то SQLite не даст вам записать в него повторяющееся значение.
4. Ограничение CHECK, данное ограничение задает диапазон значений, которые могут храниться в столбце, например, в столбце зарплата вы можете установить ограничение, что зарплата не может быть меньше МРОТ при помощи ограничения CHECK.
5. Ограничение уровня столбца DEFAULT позволяет задать значение по умолчанию, которое будет добавляться автоматически, если вы забудете про столбец при добавлении новых строк в таблицу.
6. Ограничение уровня столбца COLLATE позволяет задать способ сравнения данных в SQLite, на первый взгляд COLLATE не является ограничение уровня столбца, но, когда мы удаляем данные из таблицы, то обычно используем клаузулу WHERE для фильтрации данных, таким образом мы выбираем, какие строки хотим удалить из таблицы командой DELETE.

Ниже мы на примерах рассмотрим каждый из перечисленных видов ограничения уровня столбца.

Типы данных, как ограничение уровня столбца в базах данных SQLite3

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

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

[php]

— Создаем таблицу и объявляем тип данных для столбца

CREATE TABLE table1 (

a INTEGER,

b REAL,

c TEXT

);

[/php]

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

[php]

— Добавление строк в таблицу

INSERT INTO table1

VALUES (20, 2.02345, ‘текст’);

[/php]

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

[php]

— Проверям типы данных для столбца

SELECT typeof (a), typeof (b), typeof© FROM table1;

[/php]

Выполнив команду SELECT, мы увидим типы данных значений в столбце:

[php]

integer|real|text

[/php]

Теперь давайте удалим данные из таблицы при помощи команды DELETE, но добавлять новые данные в таблицу будем нечестно:

[php]

— Удаляем данные из таблицы и добавляем новые данные с ошибкой

DELETE FROM table1;

INSERT INTO table1;

VALUES (20.12321312, ‘3212.02’, 158);

[/php]

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

[php]

— Проверяем какие сохранились значения и какой тип данных у этих значений

SELECT * FROM table1;

SELECT typeof (a), typeof (b), typeof© FROM table1;

[/php]

Результат будет таким:

[php]

20.12321312|3212.02|158

real|real|text

[/php]

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

[php]

— Удаляем данные из таблицы и добавляем новые данные с ошибкой

DELETE FROM table1;

INSERT INTO table1

VALUES (’12 негритят’, ‘текст’, ‘честно добавляем текст’);

[/php]

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

Убедимся, что всё было добавлено:

[php]

— Проверяем какие сохранились значения и какой тип данных у этих значений

SELECT * FROM table1;

SELECT typeof (a), typeof (b), typeof© FROM table1;

[/php]

А вот и результат:

[php]

12 негритят|текст|честно добавляем текст

text|text|text

[/php]

Вывод: в SQLite3 тип данных не является ограничением уровня столбца, типы данных в SQLite нужны лишь для сравнения данных.

Значение по умолчанию, как ограничение уровня столбца в базах данных SQLite. Ограничение DEFAULT в SQLite

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

Создадим простую таблицу table2:

[php]

— Создаем таблицу с именем table2

— и задаем столбцу значение по умолчанию

CREATE TABLE table2 (

a INTEGER DEFAULT 15,

b TEXT,

c TEXT

);

[/php]

Мы создали таблицу и задали значение по умолчанию для первого столбца при помощи ограничения DEFAULT.

Теперь давайте добавим данные в таблицу командой INSERT:

[php]

— добавление данных в таблицу

INSERT INTO table2

VALUES (12, ‘текст первый’, ‘текст второй’);

[/php]

Мы честно добавили значения во все три столбца таблицы table2, но если мы забудем добавить значение для первого столбца, SQLite подставит в него значение 15 из-за ограничения DEFAULT, которое мы добавили.

[php]

INSERT INTO table2 (b, c)

VALUES (‘текстпервый’, ‘текствторой’);

[/php]

Проверку на то, что ограничение DEFAULT сработало, можно осуществить при помощи оператора SELECT:

[php]

— Проверяем работу ограничения DEFAULT

SELECT * FROM table2;

[/php]

Результат выборки из-за ограничения DEFAULT будет таким:

[php]

12|текст первый|текст второй

15|текст первый|текст второй

[/php]

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

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

Значение NOT NULL, как ограничение уровня столбца в базах данных SQLite3. Ограничение NOT NULL в SQLite

Если ограничение DEFAULT позволяет задать значение по умолчанию для столбца, то ограничение NOT NULL не дает пользователю добавить значение NULL. Ограничение уровня столбца NOT NULL исключает намеренные и ненамеренные ошибки ввода, например, пользователь намеренно пытается внести в базу данных значение NULL, а может, оператор просто забыл вписать значение в поле. И в первом, и во втором случае SQLite выдаст ошибку и скажет, что для добавления данных в таблицу значение должно отличаться от NULL.

Давайте в этом убедимся, создайте таблицу table3:

[php]

— создаем таблицу с ограничение столбца NOT NULL

CREATE TABLE table3 (A NOT NULL, b, c);

[/php]

А теперь давайте добавим данные в таблицу:

[php]

— добавляем данные

INSERT INTO table3 (a, b)

VALUES (‘значение’, NULL);

[/php]

А теперь проверим, что в итоге получилось командой SELECT:

[php]

— Проверяем значения в таблице

SELECT typeof (a), typeof (b), typeof© FROM table3;

[/php]

Результат будет следующим:

[php]

text|null|null

[/php]

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

[php]

— Проверка ограничения NOTNULLв SQLite3

DELETE FROM table3;

INSERTINTOtable3 (a, b, c)

VALUES (NULL, 100, ‘какой-то текст’);

[/php]

В результате мы увидим, что ограничение столбца NOT NULL сработало, а SQLite3 выдала ошибку: Error: NOT NULL constraint failed: table3.A. Но что будет, если мы «забудем» добавить значение в первый столбец и как сработает ограничение NOT NULL в базе данных.

[php]

— Мы «забыли» добавить значение

DELETEFROMtable3;

INSERTINTOtable3 (b, c)

VALUES (100, ‘какой-то текст’);

[/php]

SQLite сработала верно, выдав ошибку: Error: NOTNULLconstraintfailed: table3.A.Так как ограничение столбца NOT NULL сработало, а в том случае, когда мы пытаемся добавить данные, не указав значение для столбца по умолчанию записывается значение NULL.

Вывод: ограничение уровня столбца NOTNULLв базах данных SQLiteзаботится о поддержании целостности данных и не дает намеренно или случайно добавлять значения NULLв таблицы.

Значение UNIQUE, как ограничение уровня столбца в базах данных SQLite. Ограничение UNIQUE в SQLite

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

Ограничение UNIQUE не даст вам возможность добавить повторяющиеся значения в таблицу базы данных. Давайте посмотрим в действие ограничение уровня столбца UNIQUE. Создайте таблицу table4.

[php]

— Создаем таблицу с ораничением

— уровня столбца UNIQUE

CREATE TABLE table4 (a UNIQUE, b, c);

[/php]

А теперь давайте наполним таблицу данными:

[php]

— Добавляем данные в таблицу

INSERT INTO table4 (a, b, c) VALUES (NULL, 100, ‘какой-тотекст’);

INSERT INTO table4 (a, b, c) VALUES (NULL, 58, ‘какой-тотекст 2’);

INSERT INTO table4 (a, b, c) VALUES (0.21312312, 199, ‘какой-тотекст 3’);

[/php]

SQLite честно добавит три строки в таблицу и ограничение UNIQUE не сработает на значение NULL, поскольку NULL – это всегда уникальное значение, которое не равно ничему, даже самому себе. А теперь попробуем добавить четвертую строку в таблицу базы данных:

[php]

INSERT INTO table4 (a, b, c) VALUES (0.21312312, 105, ‘какой-то текст 4’);

[/php]

SQLite3 не даст нам добавить эту строку в таблицу и выдаст ошибку: Error: UNIQUEconstraintfailed: table4.a. Поскольку сработает ограничение уровня столбца UNIQUE, так как добавляемое значение в столбец «а» не уникально.

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

Значение AUTOINCREMENT, как ограничение уровня столбца в базах данных SQLite. Ограничение AUTOINCREMENTв SQLite3.

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

Создаемтаблицу:

[php]

CREATE TABLE table5 (a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT NOT NULL);

[/php]

А теперь добавим данные в таблицу table5:

[php]

INSERTINTO table5 (b) VALUES (‘какой-то текст’);

[/php]

Мы намеренно не стали указывать значение для первого столбца, чтобы посмотреть, как работает ограничение столбца AUTOINCREMENT в базе данных SQLite3. Сделаем выборку данных при помощи команды SELECT и посмотрим результат:

[php]

— ПроверяемработуограниченияAUTOINCREMENT

sqlite> select* from table5;

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

[/php]

Мы не добавляли единицу в первый столбец, за нас это сделало ограничение AUTOINCREMENT. Если вы создаете суррогатный ключ с ограничением уровня столбца AUTOINCREMNET, то не добавляйте вручную данные в ключевой столбец, СУБД будет делать это за вас.

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

Значение CHECK, как ограничение уровня столбца в базах данных SQLite. Ограничение CHECKв SQLite3.

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

[php]

CREATE TABLE table6 (a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT NOT NULL, c CHECK (c >95));

[/php]

Мы создали таблицу с тремя столбцами и задали третьему столбцу ограничение столбца CHECK, теперь SQLite не даст нам возможность записать в столбец «c» значение равное 95 или меньше, обратите внимание: выражение в скобках может быть очень сложным и SQLite – это СУБД с динамической типизацией данных, поэтому будьте очень аккуратны, используя ограничение уровня столбца CHECK. Теперь давайте попробуем добавить данные:

[php]

INSERT INTO table6 (b, c) VALUES (‘какой-тотекст’, 67);

[/php]

SQLite не даст нам возможность добавить эту строку, поскольку 67 меньше, чем 95. Мы получим предупреждение: Error: CHECK constraintfailed: table6. Оно говорит нам о том, что сработало ограничение уровня столбца CHECK.

Но, что будет, если мы попытаемся добавить значение NULL в столбец с ограничением CHECK? Давайте это выясним:

[php]

INSERTINTOtable6 (b, c) VALUES (‘какой-тотекст’, NULL);

[/php]

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

Вывод: ограничение уровня столбца CHECK в базах данных SQLiteпозволяет задать диапазон допустимых значений для хранимого значения в столбце.

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