SQLite: Create Table

Offical docs: https://sqlite.org/lang_createtable.html

Basic

Syntax

By default datatypes are not enforced you can add 'STRICT' to have the datatype be enforced.

CREATE TABLE table_name (
    column1 datatype constraint, 
    column2 datatype constraint constraint constraint, 
    column3 datatype constraint constraint,
)STRICT;

Example

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Reference

Datatypes

Offical docs: https://www.sqlite.org/datatype3.html

INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8

CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB

BLOB

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME

Constraints

PRIMARY KEY
UNIQUE
NOT NULL
CHECK
DEFAULT
FOREIGN KEY
AUTOINCREMENT
REFERENCES
CASCADE
ON UPDATE
ON DELETE