Sqlite syntax
From wikinotes
Documentation
official docs https://sqlite.org/docs.html datatypes https://sqlite.org/datatype3.html core funcs https://sqlite.org/lang_corefunc.html aggregate funcs https://sqlite.org/lang_aggfunc.html date funcs https://sqlite.org/lang_datefunc.html window funcs https://sqlite.org/windowfunctions.html foreign keys https://sqlite.org/foreignkeys.html PRAGMA https://sqlite.org/pragma.html
Introspection
# All Tables SELECT name FROM sqlite_master WHERE type = "table"PRAGMA table_info(table_name) # Columns in Table PRAGMA index_list(table_name) # Indexes on Table PRAGMA foreign_key_list(table_name) # Foreign Keys on Table
Primary Keys
# single primary key CREATE TABLE something ( column1 PRIMARY KEY NOT NULL, column2, column3, );# composite primary key CREATE TABLE something ( column1, column2, column3, PRIMARY KEY (column1, column2) );
Foreign Keys
PRAGMA foreign_keys; # returns 1 if enabled PRAGMA foreign_keys = on; # enable foreign keys# EXAMPLE CREATE TABLE users ( id INTEGER PRIMARY KEY NOT NULL, name TEXT, ); CREATE TABLE project_users ( user_id INTEGER NOT NULL, project_id INTEGER NOT NULL, # define foreign key constraint, row deleted when parent deleted FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE );