Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL PRAGMA statement (SQLite)

Reference for SQLite PRAGMA: read and change database settings, inspect tables, indexes and foreign keys, enable foreign key checks, and use table-valued pragmas.

PRAGMA is an SQLite-specific statement that reads or changes settings of the database engine and returns internal information about the database, such as the columns of a table, its indexes or its foreign keys. It is not part of the SQL standard, and no other database uses it.

A pragma with no value reads a setting. A pragma with a value changes it. Many settings apply only to the current connection and must be repeated every time you connect, so that is the first thing to check when a setting seems to "forget" its value.

SQL
PRAGMA foreign_keys;          -- read: 0 or 1
PRAGMA foreign_keys = ON;     -- write
PRAGMA table_info(products);  -- inspect

Try it

SQL Inspect tables, indexes and foreign keys ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Turn on foreign key checks (fails on purpose at the end) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Pragmas as table-valued functions ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
PRAGMA [schema_name.]pragma_name;
PRAGMA [schema_name.]pragma_name = value;
PRAGMA [schema_name.]pragma_name(value);

SELECT * FROM pragma_pragma_name(argument [, schema]);
Part Description
schema_name Optional. main, temp or an attached database. Defaults to main.
pragma_name The setting or query to run.
value A number, a name or a string. Booleans accept ON, OFF, TRUE, FALSE, 1 and 0.

Returns: zero or more rows. The table-valued form (pragma_table_info('t')) returns the same rows, but you can filter, join and sort them like any table, which is the better way to use pragmas in queries.

Commonly used pragmas

Pragma Purpose
table_info(t) One row per column: name, declared type, notnull, default and primary-key position.
table_xinfo(t) Like table_info, plus hidden and generated columns.
index_list(t), index_info(i) Indexes of a table and the columns of one index.
foreign_key_list(t) Foreign keys declared on a table.
foreign_keys Turns enforcement of foreign keys on or off for the connection.
foreign_key_check Lists rows that currently violate a foreign key.
integrity_check Scans the file for corruption and returns ok when it is healthy.
journal_mode Rollback journal or WAL for better read and write concurrency.
user_version A number stored in the file header, handy for schema migrations.
table_list Every table and view in every attached schema.

Dialect differences

Dialect Equivalent
SQLite PRAGMA name [= value].
PostgreSQL SET name = value and SHOW name for settings. Catalog queries use information_schema and pg_catalog (for example \d table in psql).
MySQL SET, SHOW VARIABLES, SHOW COLUMNS FROM t, SHOW CREATE TABLE t, and information_schema.
SQL Server SET options, sp_help, sp_columns and the sys. catalog views.
Oracle ALTER SESSION SET ..., plus the USER_TAB_COLUMNS and USER_CONSTRAINTS views.
SQL
-- PostgreSQL
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'products';

-- MySQL
SHOW COLUMNS FROM products;

Usage notes

  • Foreign keys are off by default in SQLite, unless the library was compiled otherwise. Run PRAGMA foreign_keys = ON right after connecting, outside a transaction, because it does nothing inside one.
  • Per connection or per file? foreign_keys is per connection. journal_mode = WAL and user_version are saved in the file. Check the setting's description before relying on it persisting.
  • A misspelled pragma is silently ignored. PRAGMA foreing_keys = ON returns no error and changes nothing, so read the value back to be sure.
  • Use table-valued pragmas inside queries. SELECT ... FROM pragma_table_info('t') WHERE ... is easy to filter, unlike the statement form.
  • Quote reserved words. The table_info column notnull is a keyword, so write "notnull" when selecting it.
  • PRAGMA integrity_check can take a long time on large files. Run it from maintenance jobs, not on every request.
  • Performance pragmas (cache_size, synchronous, mmap_size) trade safety for speed. Change them one at a time and measure.