Basic Example

This example shows how to create a users table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

The resulting table has two columns:

  • id (which is the table’s primary key)
  • email

How to Create Non-Nullable Columns

You can create non-nullable columns by adding a NOT NULL constraint:

CREATE TABLE users (
   id INTEGER PRIMARY KEY NOT NULL,
   email TEXT NOT NULL
);

How to Create Columns With Default Values

CREATE TABLE users (
   id INTEGER PRIMARY KEY NOT NULL,
   email TEXT NOT NULL,
   credits_used INTEGER DEFAULT 0,
   subscribed_to_newsletter BOOLEAN NOT NULL DEFAULT 0
);

Here the following columns specify default values:

  • credits_used with default value 0
  • subscribed_to_newsletter with default value 0 (SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true)). This column is furthermore defined to be non-nullable.

Learn More

Get started with DB Pilot

Download DB Pilot, the database GUI with a twist.
Speed up your SQL workflows today.