Designing & Creating Tables

CREATE TABLE

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
SELECT * FROM users;

Constraints protect your data

Constraint	Meaning
PRIMARY KEY	unique id for each row
NOT NULL	a value is required
UNIQUE	no two rows can share this value (great for emails)
DEFAULT	value to use if none is given
REFERENCES other(id)	must point to a real row (a foreign key)

Try breaking a rule. The database refuses:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
INSERT INTO users (email) VALUES ('a@b.co');
INSERT INTO users (email) VALUES ('a@b.co');

That error is a good thing. Constraints catch bugs that would otherwise silently corrupt your data.

Design rule: one fact, one place

If the same information appears in many rows (like an author's name on every book), move it into its own table and point to it with an id. This is called normalization, and it's the core idea of relational database design.

This site's own database follows these rules: users, sessions, memberships and progress are separate tables linked by user_id.