CREATE TABLE and constraints · SQL

Creating a table

SQL

DROP TABLE IF EXISTS reviews;

CREATE TABLE reviews (
  review_id   INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL REFERENCES products (product_id),
  rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment     TEXT,
  created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO reviews (product_id, rating, comment) VALUES
  (1, 5, 'Satisfying to type on'),
  (6, 4, 'Great sound, slightly tight fit'),
  (7, 2, 'Out of stock for weeks');

SELECT * FROM reviews;

Each column has a name, a type and optional rules. DROP TABLE IF EXISTS at the top lets you run the example again without a "table already exists" error.

Column types

Kind of data	SQLite	PostgreSQL, MySQL, SQL Server
whole numbers	`INTEGER`	`INT`, `BIGINT`
exact decimals, like money	`NUMERIC`	`DECIMAL(10, 2)`
approximate decimals	`REAL`	`REAL`, `DOUBLE`, `FLOAT`
text	`TEXT`	`VARCHAR(100)`, `TEXT`
dates and times	`TEXT` in ISO format	`DATE`, `TIMESTAMP`, `DATETIME`
true or false	`INTEGER` (0 or 1)	`BOOLEAN`, `BIT`

SQLite is relaxed about types: a column's type is a preference, not a rule (since version 3.37, STRICT tables enforce them). Most other databases enforce types strictly, so choose with care.

Constraints

Constraint	Guarantees
`PRIMARY KEY`	every row has a unique id
`NOT NULL`	the column always has a value
`UNIQUE`	no two rows share the value
`CHECK (...)`	every value passes a test
`DEFAULT ...`	a value is used when none is given
`REFERENCES t (col)`	the value exists in another table (a foreign key)

Constraints are rules the database enforces on every insert and update, whichever program the data comes from. Watch CHECK turn bad data away:

SQL

DROP TABLE IF EXISTS badges;
CREATE TABLE badges (
  code   TEXT PRIMARY KEY,
  name   TEXT NOT NULL UNIQUE,
  points INTEGER NOT NULL CHECK (points > 0)
);
INSERT INTO badges VALUES ('first-run', 'First run', 10);
INSERT INTO badges VALUES ('streak-7', 'Week streak', -5);

Press Edit, change -5 to 50 and run it again. Then try giving the second badge the name 'First run', and UNIQUE refuses it.

A table from a query

SQL

DROP TABLE IF EXISTS indian_customers;

CREATE TABLE indian_customers AS
SELECT customer_id, name, city FROM customers WHERE country = 'India';

SELECT * FROM indian_customers;

CREATE TABLE ... AS SELECT copies the columns and data, but not the constraints.

Changing a table: ALTER TABLE

SQL

BEGIN;
ALTER TABLE customers ADD COLUMN email TEXT;
UPDATE customers
SET email = LOWER(SUBSTR(name, 1, INSTR(name, ' ') - 1)) || '@example.com';
SELECT name, email FROM customers LIMIT 4;
ROLLBACK;

The ROLLBACK means you can run this as often as you like: in SQLite, even ALTER TABLE can be undone inside a transaction. ALTER TABLE can also RENAME COLUMN, DROP COLUMN, and RENAME TO a new table name.

Removing a table

DROP TABLE reviews; deletes the table and every row in it, at once and for good. There's no "are you sure?". Double-check the name before you press Run.