Sqlite syntax

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
);