Оператор PRAGMA в SQLite: синтаксис, функции и список команд

PRAGMA — это команда SQL, используемая в SQLite для изменения настроек библиотеки или получения внутренней информации, которая не связана с таблицами. Она выполняется так же, как другие SQL-команды, включая SELECT и INSERT, но имеет свои особенности.

Ниже — разделы про что такое PRAGMA в SQLite, синтаксис команды PRAGMA и pRAGMA как табличная функция, чтобы быстро понять прикладную ценность материала, ограничения и реальные узкие места.

---

Что такое PRAGMA в SQLite

PRAGMA — это команда SQL, используемая в SQLite для изменения настроек библиотеки или получения внутренней информации, которая не связана с таблицами. Она выполняется так же, как другие SQL-команды, включая SELECT и INSERT, но имеет свои особенности

C API SQLite предоставляет файловый контроль SQLITE_FCNTL_PRAGMA, который даёт реализациям VFS возможность добавлять новые операторы PRAGMA или переопределять смысл встроенных операторов PRAGMA

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Синтаксис команды PRAGMA

PRAGMA [schema-name.] pragma-name [(pragma-value)] [= pragma-value]

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

Истина	Ложь
1	0
yes	no
true	false
on	off

Некоторые ключевые аргументы могут быть оформлены в кавычках (например, 'yes'). Кроме того, определённые pragma могут воспринимать строковые литералы, и если pragma принимает ключевой аргумент, она часто также принимает его числовой эквивалент. Например, значения «0» и «no» считаются равными, аналогично «1» и «yes». При запросе значения настройки некоторые pragma возвращают числа вместо ключевых слов

Для некоторых pragma существует необязательное имя схемы, предшествующее их названию. Эта схема может представлять собой название подключённой базы данных через ATTACH, или «main» для основной базы данных, или «temp» для временной базы. Если имя схемы не указано, по умолчанию используется «main». В ряде случаев это имя может игнорироваться. Далее в тексте отмечены pragma, для которых это имя имеет значение

PRAGMA как табличная функция

Начиная с SQLite версии 3.16.0, каждую PRAGMA можно вызывать как табличную функцию (table-valued function). Например:

PRAGMA index_info('idx52');

эквивалентно:

SELECT * FROM pragma_index_info('idx52');

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

SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
FROM sqlite_schema AS m, pragma_index_list(m.name) AS il, pragma_index_info(il.name) AS ii
WHERE m.type='table'
ORDER BY 1;

Базу данных information_schema можно эмулировать через ATTACH:

ATTACH ':memory:' AS 'information_schema';

Список всех команд PRAGMA в SQLite

Представленный список содержит все доступные pragma. Если имя pragma зачёркнуто, то оно устарело — его не следует использовать; такие операции существуют только для совместимости с историческими версиями. Программа, отмеченные ¹, доступны только в сборках с нестандартными параметрами компиляции, а ² — для тестирования SQLite и не рекомендованы для применения в приложениях

• analysis_limit
• application_id
• auto_vacuum
• automatic_index
• busy_timeout
• cache_size
• cache_spill
• ~~case_sensitive_like~~¹
• cell_size_check
• checkpoint_fullfsync
• collation_list
• compile_options
• ~~count_changes~~¹
• ~~data_store_directory~~¹
• data_version
• database_list
• ~~default_cache_size~~¹
• defer_foreign_keys
• ~~empty_result_callbacks~~¹
• encoding
• foreign_key_check
• foreign_key_list
• foreign_keys
• freelist_count
• ~~full_column_names~~¹
• fullfsync
• function_list
• hard_heap_limit
• ignore_check_constraints
• incremental_vacuum
• index_info
• index_list
• index_xinfo
• integrity_check
• journal_mode
• journal_size_limit
• legacy_alter_table
• legacy_file_format
• locking_mode
• max_page_count
• mmap_size
• module_list
• optimize
• page_count
• page_size
• parser_trace²
• pragma_list
• query_only
• quick_check
• read_uncommitted
• recursive_triggers
• reverse_unordered_selects
• schema_version³
• secure_delete
• ~~short_column_names~~¹
• shrink_memory
• soft_heap_limit
• stats³
• synchronous
• table_info
• table_list
• table_xinfo
• temp_store
• ~~temp_store_directory~~¹
• threads
• trusted_schema
• user_version
• vdbe_addoptrace²
• vdbe_debug²
• vdbe_listing²
• vdbe_trace²
• wal_autocheckpoint
• wal_checkpoint
• writable_schema³

Описание каждой PRAGMA

analysis_limit

PRAGMA analysis_limit;
PRAGMA analysis_limit = N;

Эта pragma позволяет установить ограничение на приближённое количество строк, анализируемых с помощью команды ANALYZE для каждого индекса. Если аргумент N не указан, сохраняется предыдущее ограничение анализа. Если значение установлено на ноль, анализ отключается, и команда будет проверять все строки индекса

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

Данная pragma может значительно ускорить процесс выполнения команды ANALYZE для крупных баз данных. Хотя результаты могут быть менее точными, если проверяются только части индексов, они обычно остаются в пределах приемлемого качества. Установка значения N на 100 или 1000 поможет выполнить ANALYZE быстро даже на масштабных базах данных

Эта pragma была добавлена в SQLite версии 3.32.0 (2020-05-22). Текущая реализация использует только младшие 31 бит значения N — биты более высокого порядка молча игнорируются. Будущие версии SQLite могут начать использовать биты более высокого порядка

Начиная с SQLite версии 3.46.0 (2024-05-23), рекомендуемым способом запуска ANALYZE является команда PRAGMA optimize. PRAGMA optimize автоматически устанавливает разумное временное ограничение анализа, которое гарантирует быстрое завершение команды PRAGMA optimize даже на очень больших базах данных. Приложения, использующие PRAGMA optimize вместо непосредственного запуска ANALYZE, не нуждаются в установке ограничения анализа

application_id

PRAGMA schema.application_id;
PRAGMA schema.application_id = integer;

Pragma application_id используется для запроса или установки 32-битного знакового целого числа «Application ID» с обратным порядком байтов (big-endian), расположенного по смещению 68 в заголовке базы данных

Приложения, использующие SQLite в качестве формата файла приложения, должны устанавливать целое число Application ID в уникальное значение, чтобы такие утилиты, как file(1), могли определить конкретный тип файла, а не просто сообщать «SQLite3 Database». Список назначенных идентификаторов приложений можно найти в файле magic.txt в репозитории исходного кода SQLite. См. также pragma user_version

auto_vacuum

PRAGMA schema.auto_vacuum;
PRAGMA schema.auto_vacuum = 0 | NONE | 1 | FULL | 2 | INCREMENTAL;

Запрос или установка статуса автоматической очистки (auto-vacuum) в базе данных

Настройка по умолчанию для auto-vacuum — 0 или «none», если только не используется параметр компиляции SQLITE_DEFAULT_AUTOVACUUM. Значение «none» означает, что auto-vacuum отключён. Когда auto-vacuum отключён и данные удаляются из базы данных, размер файла базы данных остаётся прежним

Неиспользуемые страницы файла базы данных добавляются в «freelist» (список свободных страниц) и повторно используются для последующих вставок. Таким образом, дисковое пространство файла базы данных не теряется. Однако файл базы данных не уменьшается. В этом режиме команда VACUUM может использоваться для перестройки всего файла базы данных и тем самым освобождения неиспользуемого дискового пространства

Когда режим auto-vacuum равен 1 или «full», страницы freelist перемещаются в конец файла базы данных, и файл базы данных усекается для удаления страниц freelist при каждой фиксации транзакции. Следует отметить, однако, что auto-vacuum только усекает страницы freelist из файла. Auto-vacuum не дефрагментирует базу данных и не переупаковывает отдельные страницы базы данных так, как это делает команда VACUUM

Фактически, поскольку он перемещает страницы внутри файла, auto-vacuum может фактически усугубить фрагментацию

Автоматическая очистка возможна только в том случае, если база данных хранит некоторую дополнительную информацию, позволяющую отследить каждую страницу базы данных обратно до её ссылающегося элемента. Поэтому auto-vacuum должен быть включён до создания каких-либо таблиц. Невозможно включить или отключить auto-vacuum после того, как таблица была создана

Когда значение auto-vacuum равно 2 или «incremental», дополнительная информация, необходимая для выполнения автоматической очистки, хранится в файле базы данных, однако автоматическая очистка не происходит автоматически при каждой фиксации, как это происходит при auto_vacuum=full. В инкрементальном режиме для выполнения автоматической очистки необходимо вызвать отдельную pragma incremental_vacuum

Соединение с базой данных можно переключать между режимами полного и инкрементального автовакуума в любое время. Однако переход из режима «none» в режим «full» или «incremental» возможен только тогда, когда база данных новая (ещё не создано ни одной таблицы) или путём выполнения команды VACUUM

Чтобы изменить режим автовакуума, сначала используйте прагму auto_vacuum для установки нового желаемого режима, затем вызовите команду VACUUM для реорганизации всего файла базы данных. Переход из режима «full» или «incremental» обратно в «none» всегда требует выполнения VACUUM, даже для пустой базы данных

Если прагма auto_vacuum вызывается без аргументов, она возвращает текущий режим автовакуума

automatic_index

PRAGMA automatic_index;
PRAGMA automatic_index = boolean;

Запрашивает, устанавливает или отключает возможность автоматического индексирования. Автоматическое индексирование включено по умолчанию начиная с версии 3.7.17 (2013-05-20), однако это может измениться в будущих выпусках SQLite

busy_timeout

PRAGMA busy_timeout;
PRAGMA busy_timeout = milliseconds;

Запрашивает или изменяет настройку тайм-аута ожидания (busy timeout). Эта прагма является альтернативой C-интерфейсу sqlite3_busy_timeout() и предоставляется в виде прагмы для использования с языковыми привязками, которые не предоставляют прямого доступа к sqlite3_busy_timeout(). Каждое соединение с базой данных может иметь только один обработчик ожидания (busy handler)

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

cache_size

PRAGMA schema.cache_size;
PRAGMA schema.cache_size = pages;
PRAGMA schema.cache_size = -kibibytes;

Запрашивает или изменяет рекомендуемое максимальное количество страниц диска базы данных, которые SQLite будет удерживать в памяти одновременно для каждого открытого файла базы данных. Соблюдение этой рекомендации остаётся на усмотрение кэша страниц, определяемого приложением (Application Defined Page Cache)

Кэш страниц по умолчанию, встроенный в SQLite, выполняет этот запрос, однако альтернативные реализации кэша страниц, определяемые приложением, могут интерпретировать рекомендуемый размер кэша по-другому или полностью игнорировать его. Рекомендуемый размер кэша по умолчанию равен -2000, что означает ограничение размера кэша до 2 048 000 байт памяти

Рекомендуемый размер кэша по умолчанию можно изменить с помощью параметров компиляции SQLITE_DEFAULT_CACHE_SIZE. База данных TEMP имеет рекомендуемый размер кэша по умолчанию 0 страниц

Если аргумент N положительный, рекомендуемый размер кэша устанавливается равным N. Если аргумент N отрицательный, количество страниц кэша корректируется до числа страниц, которые использовали бы приблизительно abs(N*1024) байт памяти исходя из текущего размера страницы. SQLite запоминает количество страниц в кэше страниц, а не объём используемой памяти

Поэтому если вы устанавливаете размер кэша с помощью отрицательного числа и впоследствии изменяете размер страницы (с помощью команды PRAGMA page_size), максимальный объём памяти кэша будет увеличиваться или уменьшаться пропорционально изменению размера страницы

Примечание об обратной совместимости: поведение cache_size с отрицательным N отличалось до версии 3.7.10 (2012-01-16). В более ранних версиях количество страниц в кэше устанавливалось равным абсолютному значению N

При изменении размера кэша с помощью прагмы cache_size изменение действует только в течение текущего сеанса. Размер кэша возвращается к значению по умолчанию при закрытии и повторном открытии базы данных

Реализация кэша страниц по умолчанию не выделяет весь объём памяти кэша сразу. Память кэша выделяется небольшими порциями по мере необходимости. Параметр page_cache является (рекомендуемой) верхней границей объёма памяти, которую может использовать кэш, а не объёмом памяти, который он будет использовать постоянно

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

cache_spill

PRAGMA cache_spill;
PRAGMA cache_spill = boolean;
PRAGMA schema.cache_spill = N;

Прагма cache_spill включает или отключает возможность менеджера страниц сбрасывать «грязные» страницы кэша в файл базы данных в середине транзакции. Cache_spill включён по умолчанию, и большинству приложений следует оставить его в таком состоянии, поскольку сброс кэша обычно выгоден. Однако сброс кэша имеет побочный эффект в виде получения блокировки EXCLUSIVE на файл базы данных

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

Форма PRAGMA cache_spill = N данной прагмы устанавливает минимальный пороговый размер кэша, необходимый для выполнения сброса. Количество страниц в кэше должно превышать как порог cache_spill, так и максимальный размер кэша, установленный оператором PRAGMA cache_size, чтобы произошёл сброс

Форма PRAGMA cache_spill = boolean данной прагмы применяется ко всем базам данных, подключённым к соединению с базой данных. Но форма PRAGMA cache_spill = N данного оператора применяется только к схеме «main» или к любой другой схеме, указанной в составе оператора

case_sensitive_like (устаревшая)

PRAGMA case_sensitive_like = boolean;

Поведение оператора LIKE по умолчанию — игнорировать регистр для символов ASCII. Таким образом, по умолчанию 'a' LIKE 'A' является истинным. Прагма case_sensitive_like устанавливает новую функцию LIKE, определяемую приложением, которая является чувствительной или нечувствительной к регистру в зависимости от значения прагмы case_sensitive_like

Когда case_sensitive_like отключена, выражается поведение LIKE по умолчанию. Когда case_sensitive_like включена, регистр становится значимым. Так, например, 'a' LIKE 'A' является ложным, но 'a' LIKE 'a' по-прежнему остаётся истинным

Эта прагма использует sqlite3_create_function() для перегрузки функций LIKE и GLOB, что может переопределить предыдущие реализации LIKE и GLOB, зарегистрированные приложением. Эта прагма изменяет только поведение оператора SQL LIKE. Она не изменяет поведение C-интерфейса sqlite3_strlike(), который всегда нечувствителен к регистру

ПРЕДУПРЕЖДЕНИЕ: Если база данных использует оператор LIKE где-либо в схеме, например в ограничении CHECK, в индексе выражения или в предложении WHERE частичного индекса, то изменение определения оператора LIKE с помощью данной прагмы может привести к тому, что база данных будет выглядеть повреждённой. PRAGMA integrity_check будет сообщать об ошибках

База данных на самом деле не повреждена в том смысле, что возврат поведения LIKE к тому, каким оно было при определении схемы и заполнении базы данных, устранит проблему. Если использование LIKE встречается только в индексах, проблему можно устранить, выполнив REINDEX. Тем не менее использование прагмы case_sensitive_like не рекомендуется

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

cell_size_check

PRAGMA cell_size_check;
PRAGMA cell_size_check = boolean;

Прагма cell_size_check включает или отключает дополнительную проверку корректности страниц b-дерева базы данных при их первоначальном чтении с диска. При включённой проверке размера ячеек повреждение базы данных обнаруживается раньше и с меньшей вероятностью «распространяется». Однако выполнение дополнительных проверок незначительно снижает производительность, поэтому проверка размера ячеек по умолчанию отключена

checkpoint_fullfsync

PRAGMA checkpoint_fullfsync;
PRAGMA checkpoint_fullfsync = boolean;

PRAGMA checkpoint_fullfsync запрашивает или изменяет флаг fullfsync для операций контрольных точек. Если этот флаг установлен, то метод синхронизации F_FULLFSYNC используется во время операций контрольных точек на системах, поддерживающих F_FULLFSYNC. Значение флага checkpoint_fullfsync по умолчанию — off. F_FULLFSYNC поддерживается только в Mac OS X

Если флаг fullfsync установлен, то метод синхронизации F_FULLFSYNC используется для всех операций синхронизации, и настройка checkpoint_fullfsync становится неактуальной

collation_list

PRAGMA collation_list;

Возвращает список последовательностей сортировки (collation sequences), определённых для текущего соединения с базой данных

compile_options

PRAGMA compile_options;

Эта pragma возвращает имена параметров времени компиляции, использованных при сборке SQLite, по одному параметру в строке. Префикс «SQLITE_» опускается из возвращаемых имён параметров. См. также интерфейс C/C++ sqlite3_compileoption_get() и SQL-функцию sqlite_compileoption_get()

count_changes (устаревшая)

PRAGMA count_changes;
PRAGMA count_changes = boolean;

Запрашивает или изменяет флаг count-changes. Обычно, когда флаг count-changes не установлен, операторы INSERT, UPDATE и DELETE не возвращают данных. Когда count-changes установлен, каждая из этих команд возвращает одну строку данных, состоящую из одного целочисленного значения — количества строк, вставленных, изменённых или удалённых командой

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

Другой способ получить количество изменённых строк — использовать интерфейсы sqlite3_changes() или sqlite3_total_changes(). Однако существует тонкое различие. Когда INSERT, UPDATE или DELETE выполняется для представления с использованием триггера INSTEAD OF, pragma count_changes сообщает количество строк в представлении, которые активировали триггер, тогда как sqlite3_changes() и sqlite3_total_changes() этого не делают

Эта pragma устарела и существует только для обратной совместимости. Новые приложения должны избегать её использования. Старые приложения должны прекратить использование этой pragma при первой возможности. Эта pragma может быть исключена из сборки, если SQLite скомпилирован с параметром SQLITE_OMIT_DEPRECATED

data_store_directory (устаревшая)

PRAGMA data_store_directory;
PRAGMA data_store_directory = 'directory-name';

Запрашивает или изменяет значение глобальной переменной sqlite3_data_directory, которую используют бэкенды интерфейса операционной системы Windows для определения места хранения файлов базы данных, указанных с использованием относительного пути

Изменение настройки data_store_directory не является потокобезопасным. Никогда не изменяйте настройку data_store_directory, если другой поток в приложении одновременно выполняет какой-либо интерфейс SQLite. Это приводит к неопределённому поведению. Изменение настройки data_store_directory выполняет запись в глобальную переменную sqlite3_data_directory, и эта глобальная переменная не защищена мьютексом

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

data_version

PRAGMA schema.data_version;

Команда PRAGMA data_version предоставляет индикатор того, что файл базы данных был изменён. Интерактивные программы, хранящие содержимое базы данных в памяти или отображающие его на экране, могут использовать команду PRAGMA data_version для определения необходимости сброса и перезагрузки памяти или обновления экранного отображения

Целочисленные значения, возвращаемые двумя вызовами PRAGMA data_version из одного и того же соединения, будут различаться, если в промежутке между ними изменения были зафиксированы в базе данных любым другим соединением. Значение PRAGMA data_version не изменяется для фиксаций, выполненных в том же соединении с базой данных

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

Сравнивать значения PRAGMA data_version имеет смысл только для одного и того же соединения с базой данных в двух разных точках времени

database_list

PRAGMA database_list;

Эта pragma работает как запрос, возвращающий одну строку для каждой базы данных, подключённой к текущему соединению. Второй столбец содержит «main» для основного файла базы данных, «temp» для файла базы данных, используемого для хранения объектов TEMP, или имя подключённой базы данных ATTACH для других файлов баз данных. Третий столбец содержит имя самого файла базы данных или пустую строку, если база данных не связана с файлом

default_cache_size (устаревшая)

Эта pragma запрашивает или устанавливает рекомендуемое максимальное количество страниц дискового кэша, которые будут выделены для каждого открытого файла базы данных. Отличие этой pragma от cache_size состоит в том, что установленное здесь значение сохраняется между соединениями с базой данных. Значение размера кэша по умолчанию хранится в 4-байтовом целом числе с обратным порядком байтов, расположенном по смещению 48 в заголовке файла базы данных

defer_foreign_keys

PRAGMA defer_foreign_keys;
PRAGMA defer_foreign_keys = boolean;

Когда pragma defer_foreign_keys включён, применение всех ограничений внешних ключей откладывается до фиксации самой внешней транзакции. По умолчанию pragma defer_foreign_keys имеет значение OFF, так что ограничения внешних ключей откладываются только в том случае, если они созданы как «DEFERRABLE INITIALLY DEFERRED». Pragma defer_foreign_keys автоматически отключается при каждом COMMIT или ROLLBACK

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

Интерфейс на языке C sqlite3_db_status(db, SQLITE_DBSTATUS_DEFERRED_FKS,...) может использоваться во время транзакции для определения наличия отложенных и неразрешённых ограничений внешних ключей

empty_result_callbacks (устаревшая)

PRAGMA empty_result_callbacks;
PRAGMA empty_result_callbacks = boolean;

Запрашивает или изменяет флаг empty-result-callbacks

Флаг empty-result-callbacks влияет только на API sqlite3_exec(). Обычно, когда флаг empty-result-callbacks сброшен, функция обратного вызова, переданная в sqlite3_exec(), не вызывается для команд, возвращающих ноль строк данных. Когда empty-result-callbacks установлен в этой ситуации, функция обратного вызова вызывается ровно один раз, с третьим параметром, установленным в 0 (NULL)

Это позволяет программам, использующим API sqlite3_exec(), получать имена столбцов даже в случае, когда запрос не возвращает данных

encoding

PRAGMA encoding;
PRAGMA encoding = 'UTF-8';
PRAGMA encoding = 'UTF-16';
PRAGMA encoding = 'UTF-16le';
PRAGMA encoding = 'UTF-16be';

В первой форме, если основная база данных уже создана, этот pragma возвращает кодировку текста, используемую основной базой данных: одну из 'UTF-8', 'UTF-16le' (UTF-16 с прямым порядком байтов) или 'UTF-16be' (UTF-16 с обратным порядком байтов). Если основная база данных ещё не создана, то возвращаемое значение — это кодировка текста, которая будет использована для создания основной базы данных, если она будет создана в этом сеансе

Вторая по пятую формы этой прагмы задают кодировку, с которой будет создана основная база данных, если она создаётся в данной сессии. Строка 'UTF-16' интерпретируется как «кодировка UTF-16 с использованием порядка байтов, принятого на данной машине». Изменить кодировку текста базы данных после её создания невозможно, и любая попытка сделать это будет молча проигнорирована

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

После того как кодировка для базы данных установлена, изменить её нельзя

Базы данных, созданные командой ATTACH, всегда используют ту же кодировку, что и основная база данных. Попытка выполнить ATTACH для базы данных с кодировкой текста, отличной от кодировки базы данных «main», завершится ошибкой

foreign_key_check

PRAGMA schema.foreign_key_check;
PRAGMA schema.foreign_key_check(table-name);

Прагма foreign_key_check проверяет базу данных или таблицу с именем «table-name» на наличие нарушений ограничений внешнего ключа. Прагма foreign_key_check возвращает одну строку для каждого нарушения внешнего ключа. В каждой строке результата четыре столбца. Первый столбец — имя таблицы, содержащей предложение REFERENCES

Второй столбец — rowid строки, содержащей недопустимое предложение REFERENCES, или NULL, если дочерняя таблица является таблицей WITHOUT ROWID. Третий столбец — имя таблицы, на которую ссылаются. Четвёртый столбец — индекс конкретного ограничения внешнего ключа, которое не прошло проверку. Четвёртый столбец в выводе прагмы foreign_key_check совпадает с первым столбцом в выводе прагмы foreign_key_list

Если указано «table-name», проверяются только те ограничения внешнего ключа, которые созданы предложениями REFERENCES в операторе CREATE TABLE для table-name

foreign_key_list

PRAGMA foreign_key_list(table-name);

Эта прагма возвращает одну строку для каждого ограничения внешнего ключа, созданного предложением REFERENCES в операторе CREATE TABLE таблицы «table-name»

foreign_keys

PRAGMA foreign_keys;
PRAGMA foreign_keys = boolean;

Запрашивает, устанавливает или сбрасывает принудительное применение ограничений внешнего ключа. Эта прагма не выполняет никаких действий внутри транзакции; включение или отключение принудительного применения ограничений внешнего ключа возможно только при отсутствии незавершённых BEGIN или SAVEPOINT

Изменение настройки foreign_keys влияет на выполнение всех операторов, подготовленных с использованием данного соединения с базой данных, включая те, что были подготовлены до изменения настройки. Любые существующие операторы, подготовленные с использованием устаревшего интерфейса sqlite3_prepare(), могут завершиться ошибкой SQLITE_SCHEMA после изменения настройки foreign_keys

Начиная с версии SQLite 3.6.19, настройка принудительного применения внешних ключей по умолчанию имеет значение OFF. Однако это может измениться в будущих выпусках SQLite. Значение по умолчанию для принудительного применения внешних ключей можно задать на этапе компиляции с помощью макроса препроцессора SQLITE_DEFAULT_FOREIGN_KEYS

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

freelist_count

PRAGMA schema.freelist_count;

Возвращает количество неиспользуемых страниц в файле базы данных

full_column_names (устаревшая)

PRAGMA full_column_names;
PRAGMA full_column_names = boolean;

Запрашивает или изменяет флаг full_column_names. Этот флаг совместно с флагом short_column_names определяет способ, которым SQLite присваивает имена столбцам результата операторов SELECT. Столбцы результата именуются путём применения следующих правил по порядку:

1. Если в результате есть предложение AS, то имя столбца — это правая часть предложения AS.
2. Если результат является общим выражением, а не просто именем столбца исходной таблицы, то имя результата является копией текста выражения.
3. Если прагма short_column_names включена (ON), то имя результата — это имя столбца исходной таблицы без префикса имени исходной таблицы: COLUMN.
4. Если обе прагмы short_column_names и full_column_names выключены (OFF), применяется случай (2).
5. Имя столбца результата представляет собой комбинацию имени исходной таблицы и имени исходного столбца: TABLE.COLUMN.

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

fullfsync

PRAGMA fullfsync;
PRAGMA fullfsync = boolean;

Запрашивает или изменяет флаг fullfsync. Этот флаг определяет, используется ли метод синхронизации F_FULLFSYNC в системах, которые его поддерживают. Значение флага fullfsync по умолчанию — off. F_FULLFSYNC поддерживается только в Mac OS X

См. также checkpoint_fullfsync

function_list

PRAGMA function_list;

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

hard_heap_limit

PRAGMA hard_heap_limit;
PRAGMA hard_heap_limit = N;

Эта прагма вызывает интерфейс sqlite3_hard_heap_limit64() с аргументом N, если N указан и является положительным целым числом, меньшим текущего жёсткого ограничения кучи. Прагма hard_heap_limit всегда возвращает то же целое число, которое было бы возвращено функцией на языке C sqlite3_hard_heap_limit64(-1). То есть она всегда возвращает значение жёсткого ограничения кучи, установленное после любых изменений, внесённых данной прагмой

Эта прагма может только уменьшить ограничение кучи, но не увеличить его. Для увеличения ограничения кучи необходимо использовать интерфейс на языке C sqlite3_hard_heap_limit64()

См. также прагму soft_heap_limit

ignore_check_constraints

PRAGMA ignore_check_constraints;
PRAGMA ignore_check_constraints = boolean;

Эта прагма включает или отключает принудительное применение ограничений CHECK. Настройка по умолчанию — off, что означает, что ограничения CHECK применяются принудительно по умолчанию

incremental_vacuum

PRAGMA schema.incremental_vacuum(N);
PRAGMA schema.incremental_vacuum;

Прагма incremental_vacuum вызывает удаление до N страниц из списка свободных страниц (freelist). Файл базы данных усекается на соответствующее количество страниц. Прагма incremental_vacuum не имеет эффекта, если база данных не находится в режиме auto_vacuum=incremental или если в списке свободных страниц нет страниц

Если в списке свободных страниц меньше N страниц, или если N меньше 1, или если аргумент «(N)» опущен, то весь список свободных страниц очищается

index_info

PRAGMA schema.index_info(index-name);

Эта прагма возвращает одну строку для каждого ключевого столбца в указанном индексе. Ключевой столбец — это столбец, явно указанный в операторе CREATE INDEX или в ограничении UNIQUE или PRIMARY KEY, создавшем индекс. Записи индекса также обычно содержат вспомогательные столбцы, указывающие обратно на индексируемую строку таблицы. Вспомогательные столбцы индекса не отображаются прагмой index_info, но перечисляются прагмой index_xinfo

Выходные столбцы прагмы index_info:

• Ранг столбца внутри индекса (0 означает крайний левый)
• Ранг столбца внутри индексируемой таблицы; значение -1 означает rowid, значение -2 означает, что используется выражение
• Имя индексируемого столбца; этот столбец равен NULL, если столбец является rowid или выражением

Если индекса с именем index-name не существует, но существует таблица WITHOUT ROWID с таким именем, то начиная с версии SQLite 3.30.0 (2019-10-04) данная pragma возвращает столбцы PRIMARY KEY таблицы WITHOUT ROWID в том виде, в котором они используются в записях базового b-дерева, то есть с удалёнными дублирующимися столбцами

index_list

PRAGMA schema.index_list(table-name);

Данная pragma возвращает по одной строке для каждого индекса, связанного с указанной таблицей. Выходные столбцы pragma index_list:

• Порядковый номер, присвоенный каждому индексу для целей внутреннего отслеживания
• Имя индекса
• «1», если индекс является UNIQUE, и «0», если нет
• «c», если индекс был создан оператором CREATE INDEX; «u», если индекс был создан ограничением UNIQUE; или «pk», если индекс был создан ограничением PRIMARY KEY
• «1», если индекс является частичным индексом, и «0», если нет

index_xinfo

PRAGMA schema.index_xinfo(index-name);

Данная pragma возвращает информацию о каждом столбце в индексе. В отличие от pragma index_info, эта pragma возвращает информацию о каждом столбце индекса, а не только о ключевых столбцах. Ключевой столбец — это столбец, явно указанный в операторе CREATE INDEX, ограничении UNIQUE или ограничении PRIMARY KEY, которым был создан индекс

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

Выходные столбцы pragma index_xinfo:

• Ранг столбца внутри индекса (0 означает крайний левый; ключевые столбцы идут перед вспомогательными)
• Ранг столбца внутри индексируемой таблицы, или -1, если столбец индекса является rowid индексируемой таблицы, и -2, если индекс построен по выражению
• Имя индексируемого столбца, или NULL, если столбец индекса является rowid индексируемой таблицы или выражением
• 1, если столбец индекса отсортирован в обратном (DESC) порядке по индексу, и 0 в противном случае
• Имя последовательности сопоставления, используемой для сравнения значений в столбце индекса
• 1, если столбец индекса является ключевым столбцом, и 0, если столбец индекса является вспомогательным столбцом

Если индекса с именем index-name не существует, но существует таблица WITHOUT ROWID с таким именем, то начиная с версии SQLite 3.30.0 (2019-10-04) данная pragma возвращает столбцы таблицы WITHOUT ROWID в том виде, в котором они используются в записях базового b-дерева, то есть сначала дедублированные столбцы PRIMARY KEY, а затем столбцы данных

integrity_check

PRAGMA schema.integrity_check;
PRAGMA schema.integrity_check(N);
PRAGMA schema.integrity_check(TABLENAME);

Данная pragma выполняет низкоуровневую проверку форматирования и согласованности базы данных. Pragma integrity_check ищет:

• Записи таблицы или индекса, нарушающие порядок следования
• Неправильно отформатированные записи
• Отсутствующие страницы
• Отсутствующие или лишние записи индекса
• Ошибки ограничений UNIQUE, CHECK и NOT NULL
• Целостность списка свободных страниц
• Разделы базы данных, используемые более одного раза или не используемые вовсе

Если pragma integrity_check обнаруживает проблемы, возвращаются строки (в виде нескольких строк с одним столбцом в каждой), описывающие эти проблемы. Pragma integrity_check вернёт не более N ошибок до завершения анализа, при этом N по умолчанию равно 100. Если pragma integrity_check не обнаруживает ошибок, возвращается одна строка со значением 'ok'

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

Список свободных страниц проверяется при частичной проверке целостности только в том случае, если TABLENAME равно sqlite_schema или одному из его псевдонимов. Поддержка частичных проверок целостности была добавлена в версии 3.33.0 (2020-08-14)

PRAGMA integrity_check не обнаруживает ошибки FOREIGN KEY. Для поиска ошибок в ограничениях FOREIGN KEY используйте команду PRAGMA foreign_key_check

См. также команду PRAGMA quick_check, которая выполняет большую часть проверок PRAGMA integrity_check, но работает значительно быстрее

journal_mode

PRAGMA schema.journal_mode;
PRAGMA schema.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF;

Данная pragma запрашивает или устанавливает режим журналирования для баз данных, связанных с текущим соединением с базой данных

Первая форма данной pragma запрашивает текущий режим журналирования для базы данных. Если database не указана, запрашивается база данных «main»

Вторая форма изменяет режим журналирования для «database» или для всех подключённых баз данных, если «database» не указана. Возвращается новый режим журналирования. Если режим журналирования не удалось изменить, возвращается исходный режим журналирования

DELETE — режим журналирования по умолчанию. В режиме DELETE журнал отката удаляется по завершении каждой транзакции. Именно операция удаления является действием, которое вызывает фиксацию транзакции

TRUNCATE — фиксирует транзакции путём усечения журнала отката до нулевой длины вместо его удаления. На многих системах усечение файла выполняется значительно быстрее, чем его удаление, поскольку не требует изменения содержащего каталога

PERSIST — предотвращает удаление журнала отката по завершении каждой транзакции. Вместо этого заголовок журнала перезаписывается нулями. Это не позволит другим соединениям с базой данных выполнить откат по журналу. Режим журналирования PERSIST полезен как оптимизация на платформах, где удаление или усечение файла обходится значительно дороже, чем перезапись первого блока файла нулями. См. также: PRAGMA journal_size_limit и SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT

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

WAL — использует журнал с упреждающей записью (write-ahead log) вместо журнала отката для реализации транзакций. Режим журналирования WAL является постоянным: после установки он сохраняется при нескольких соединениях с базой данных, а также после закрытия и повторного открытия базы данных. База данных в режиме журналирования WAL может быть доступна только в SQLite версии 3.7.0 (2010-07-21) или более поздней

OFF — полностью отключает журнал отката. Журнал отката никогда не создаётся, а значит, его никогда не нужно удалять. Режим журналирования OFF отключает возможности атомарной фиксации и отката в SQLite. Команда ROLLBACK больше не работает; её поведение становится неопределённым. Приложения должны избегать использования команды ROLLBACK, когда режим журналирования установлен в OFF

Если приложение аварийно завершает работу в середине транзакции при установленном режиме журналирования OFF, файл базы данных, скорее всего, окажется повреждён. Без журнала у оператора нет возможности отменить частично выполненные операции после ошибки ограничения. Это также может оставить базу данных в повреждённом состоянии

Например, если дублирующаяся запись вызывает сбой оператора CREATE UNIQUE INDEX на полпути, это оставит частично созданный, а значит, повреждённый индекс. Поскольку режим журналирования OFF позволяет повредить файл базы данных с помощью обычного SQL, он отключается при включении SQLITE_DBCONFIG_DEFENSIVE

Обратите внимание, что journal_mode для базы данных в памяти может быть только MEMORY или OFF и не может быть изменён на другое значение. Попытка изменить journal_mode базы данных в памяти на любое значение, отличное от MEMORY или OFF, игнорируется. Также обратите внимание, что journal_mode не может быть изменён во время активной транзакции

journal_size_limit

PRAGMA schema.journal_size_limit;
PRAGMA schema.journal_size_limit = N;

Если соединение с базой данных работает в режиме эксклюзивной блокировки или в режиме постоянного журнала (PRAGMA journal_mode=persist), то после фиксации транзакции файл журнала отката может оставаться в файловой системе

Это повышает производительность для последующих транзакций, поскольку перезапись существующего файла выполняется быстрее, чем добавление данных в конец файла, однако также потребляет пространство файловой системы. После крупной транзакции (например, VACUUM) файл журнала отката может занять очень большой объём пространства

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

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

Вторая форма прагмы используется для установки нового ограничения в байтах для указанной базы данных. Отрицательное число означает отсутствие ограничения. Чтобы всегда усекать журналы отката и файлы WAL до минимального размера, установите journal_size_limit в ноль. Обе формы прагмы возвращают одну строку результата, содержащую один целочисленный столбец — значение ограничения размера журнала в байтах

Ограничение размера журнала по умолчанию равно -1 (без ограничения). Препроцессорный макрос SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT может использоваться для изменения ограничения размера журнала по умолчанию во время компиляции

Эта прагма работает только с одной базой данных, указанной перед именем прагмы (или с базой данных «main», если база данных не указана). Нет возможности изменить ограничение размера журнала для всех подключённых баз данных с помощью одного оператора PRAGMA. Ограничение размера должно устанавливаться отдельно для каждой подключённой базы данных

legacy_alter_table

PRAGMA legacy_alter_table;
PRAGMA legacy_alter_table = boolean;

Эта прагма устанавливает или запрашивает значение флага legacy_alter_table. Когда этот флаг включён, команда ALTER TABLE RENAME (для изменения имени таблицы) работает так, как она работала в SQLite 3.24.0 (2018-06-04) и более ранних версиях

Более конкретно, когда этот флаг включён, команда ALTER TABLE RENAME переписывает только первое вхождение имени таблицы в её операторе CREATE TABLE и в любых связанных операторах CREATE INDEX и CREATE TRIGGER. Другие ссылки на таблицу остаются без изменений, включая:

• Ссылки на таблицу внутри тел триггеров и представлений
• Ссылки на таблицу внутри ограничений CHECK в исходном операторе CREATE TABLE
• Ссылки на таблицу внутри предложений WHERE частичных индексов

Настройка по умолчанию для этой прагмы — OFF, что означает, что все ссылки на таблицу в любом месте схемы преобразуются в новое имя. Эта прагма предоставляется как обходное решение для старых программ, содержащих код, который ожидает неполного поведения ALTER TABLE RENAME, обнаруженного в старых версиях SQLite. Новые приложения должны оставлять этот флаг выключенным

Для совместимости со старыми реализациями виртуальных таблиц этот флаг временно включается во время выполнения метода sqlite3_module.xRename. Значение этого флага восстанавливается после завершения метода sqlite3_module.xRename. Поведение устаревшего изменения таблицы также можно переключать с помощью параметра SQLITE_DBCONFIG_LEGACY_ALTER_TABLE интерфейса sqlite3_db_config()

Поведение устаревшего изменения таблицы является настройкой для каждого соединения. Включение или отключение этой функции влияет на все подключённые файлы баз данных в рамках соединения с базой данных. Настройка не сохраняется. Изменение этой настройки в одном соединении не влияет на другие соединения

legacy_file_format

PRAGMA legacy_file_format;

Эта прагма больше не функционирует. Она стала заглушкой (no-op). Возможности, ранее предоставляемые PRAGMA legacy_file_format, теперь доступны с помощью параметра SQLITE_DBCONFIG_LEGACY_FILE_FORMAT интерфейса C-языка sqlite3_db_config()

locking_mode

PRAGMA schema.locking_mode;
PRAGMA schema.locking_mode = NORMAL | EXCLUSIVE;

Эта прагма устанавливает или запрашивает режим блокировки соединения с базой данных. Режим блокировки может быть либо NORMAL, либо EXCLUSIVE. В режиме блокировки NORMAL (по умолчанию, если не переопределено во время компиляции с помощью SQLITE_DEFAULT_LOCKING_MODE) соединение с базой данных снимает блокировку с файла базы данных по завершении каждой транзакции чтения или записи

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

Блокировки базы данных, полученные соединением в режиме EXCLUSIVE, могут быть освобождены либо путём закрытия соединения с базой данных, либо путём возврата режима блокировки к NORMAL с помощью этой прагмы с последующим обращением к файлу базы данных (для чтения или записи). Простой установки режима блокировки в NORMAL недостаточно — блокировки не освобождаются до следующего обращения к файлу базы данных

Существуют три причины для установки режима блокировки в EXCLUSIVE:

1. Приложение хочет предотвратить доступ других процессов к файлу базы данных
2. Количество системных вызовов для операций файловой системы уменьшается, что может привести к небольшому увеличению производительности
3. Базы данных WAL могут быть доступны в режиме EXCLUSIVE без использования общей памяти

Когда прагма locking_mode указывает конкретную базу данных, например:

PRAGMA main.locking_mode=EXCLUSIVE;

тогда режим блокировки применяется только к указанной базе данных. Если перед ключевым словом «locking_mode» не указан квалификатор имени базы данных, то режим блокировки применяется ко всем базам данных, включая любые новые базы данных, добавленные последующими командами ATTACH

База данных «temp» (в которой хранятся временные таблицы и индексы) и базы данных в памяти всегда используют режим эксклюзивной блокировки. Режим блокировки баз данных temp и в памяти не может быть изменён. Все остальные базы данных по умолчанию используют нормальный режим блокировки и на них влияет эта прагма

Если режим блокировки был EXCLUSIVE при первом входе в режим журнала WAL, то его нельзя изменить на NORMAL до выхода из режима журнала WAL. Если режим блокировки был NORMAL при первом входе в режим журнала WAL, то его можно переключать между NORMAL и EXCLUSIVE

Типичные ошибки при работе с PRAGMA

На практике я замечаю несколько ошибок, которые встречаются чаще всего при работе с командами PRAGMA

Использование устаревших pragma в новом коде. Pragma, помеченные как deprecated (устаревшие), продолжают работать, но могут быть исключены из будущих сборок SQLite. Если вы пишете новое приложение, избегайте count_changes, case_sensitive_like, full_column_names, data_store_directory, empty_result_callbacks и default_cache_size

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

Включение auto_vacuum после создания таблиц. Переключить базу данных из режима «none» в «full» или «incremental» можно только для пустой базы данных или через VACUUM. Если таблицы уже созданы, потребуется сначала выполнить VACUUM, а затем установить нужный режим

Изменение foreign_keys внутри транзакции. Эта прагма не выполняет никаких действий внутри активной транзакции. Включать или отключать принудительное применение ограничений внешнего ключа можно только при отсутствии незавершённых BEGIN или SAVEPOINT

Ожидание, что journal_mode=OFF безопасен. Режим OFF полностью отключает журнал отката. При аварийном завершении приложения файл базы данных, скорее всего, окажется повреждён. Этот режим также автоматически отключается при включении SQLITE_DBCONFIG_DEFENSIVE

Ответы на эти вопросы могут быть для вас полезными

Чем PRAGMA отличается от обычного SQL-запроса? PRAGMA — это расширение SQL, специфичное для SQLite. Оно выполняется через тот же интерфейс, что и SELECT или INSERT, но предназначено для управления внутренними настройками библиотеки или получения служебных данных, которые не хранятся в обычных таблицах

Можно ли вызывать PRAGMA как табличную функцию? Да, начиная с SQLite 3.16.0 каждую pragma можно вызывать как табличную функцию через pragma_имя(). Это позволяет использовать pragma в составных запросах — например, SELECT * FROM pragma_index_info('idx52')

Как быстро выполнить ANALYZE на большой базе данных, не блокируя работу приложения? Начиная с SQLite 3.46.0 рекомендуется использовать PRAGMA optimize вместо прямого вызова ANALYZE. PRAGMA optimize автоматически устанавливает разумное ограничение анализа и гарантирует быстрое завершение даже на очень больших базах данных

Если нужен прямой вызов ANALYZE, установите PRAGMA analysis_limit = 1000 — это существенно ускорит работу при приемлемом качестве статистики

Что произойдёт, если установить journal_mode=WAL, а потом закрыть базу данных? Режим WAL является постоянным: он сохраняется после закрытия и повторного открытия базы данных, а также при подключении новых соединений. Это отличает WAL от большинства других настроек PRAGMA, которые действуют только в рамках текущего сеанса

Как проверить целостность базы данных, не проверяя её целиком? Используйте PRAGMA integrity_check(TABLENAME) для частичной проверки конкретной таблицы и связанных с ней индексов. Для более быстрой, но менее полной проверки всей базы данных подойдёт PRAGMA quick_check. Ошибки внешних ключей эти команды не обнаруживают — для этого используйте PRAGMA foreign_key_check