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.
PRAGMA foreign_keys; -- read: 0 or 1 PRAGMA foreign_keys = ON; -- write PRAGMA table_info(products); -- inspect
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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 = ONright after connecting, outside a transaction, because it does nothing inside one. - Per connection or per file?
foreign_keysis per connection.journal_mode = WALanduser_versionare saved in the file. Check the setting's description before relying on it persisting. - A misspelled pragma is silently ignored.
PRAGMA foreing_keys = ONreturns 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_infocolumnnotnullis a keyword, so write"notnull"when selecting it. PRAGMA integrity_checkcan 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.