Creating a schema: CREATE TABLE and constraints

Design your own tables with suitable types and NOT NULL, UNIQUE and PRIMARY KEY constraints that protect your data.

8 min · 3 questions

Up to now you were querying ready-made tables; a professional developer designs them. A good schema rejects invalid data at insertion time: it is your first line of defence, before any application code. In sqliteonline.com or sqlite3, create this table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL
    DEFAULT (datetime('now'))
);

SQLite knows only five storage types: INTEGER, REAL, TEXT, BLOB and NULL. Dates are stored there as TEXT in ISO format (2026-07-04). Elsewhere (PostgreSQL, MySQL), you would instead declare VARCHAR(255), TIMESTAMP or BOOLEAN: the principle stays the same, only the type names change. Now test your constraints:

INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice');

INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice2');

The primary key identifies each row: never NULL, never duplicated, only one per table. In SQLite, INTEGER PRIMARY KEY has a valuable feature: the column auto-increments when you omit its value, as in the insertions above. On PostgreSQL you would write GENERATED ALWAYS AS IDENTITY, on MySQL AUTO_INCREMENT. Finally, add a business rule with CHECK:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  sku TEXT NOT NULL UNIQUE,
  price REAL NOT NULL CHECK (price >= 0)
);

INSERT INTO products (name, sku, price)
VALUES ('Keyboard', 'KB-01', -5);

Knowledge check

Make sure you remember the key points of this lesson.

1. Which constraint guarantees that no duplicate appears in the email column?
   • NOT NULL
   • UNIQUE
   • CHECK
   • DEFAULT
2. What is the difference between PRIMARY KEY and UNIQUE?
   • None, they are synonyms
   • The primary key identifies the row: only one per table, never NULL
   • UNIQUE is stricter than PRIMARY KEY
3. In SQLite, what happens if you insert a row without a value for an INTEGER PRIMARY KEY column?
   • A NOT NULL error is raised
   • SQLite automatically generates a unique integer
   • The column stays NULL