Create Table

Code

-- Create tables with primary and foreign keys
CREATE TABLE users (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT    NOT NULL UNIQUE,
  email    TEXT    NOT NULL,
  created  TEXT    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  title      TEXT    NOT NULL,
  body       TEXT,
  published  INTEGER DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Enable foreign keys (off by default in CLI)
PRAGMA foreign_keys = ON;

-- Inspect schema
.schema users
.schema
.tables

Explanation

SQLite stores schemas in SQL with INTEGER PRIMARY KEY serving as a rowid alias. Foreign key enforcement is OFF by default and must be enabled per connection with PRAGMA foreign_keys. ON DELETE CASCADE propagates deletions to child rows automatically.

More SQLite Snippets