SQL Tutorial · Changing Data and Tables · Lesson 30 of 36

SQL CREATE TABLE

Define tables, data types and constraints.

CREATE TABLE

Example 1Try it yourself

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 '2026-10-12'
);
INSERT INTO reviews (product_id, rating, comment) VALUES (1, 5, 'Great intro'), (5, 4, NULL);
SELECT * FROM reviews;

Output

review_id | product_id | rating | comment | created_at
1 | 1 | 5 | Great intro | 2026-10-12
2 | 5 | 4 | NULL | 2026-10-12

Constraints

Constraint	Ensures
`PRIMARY KEY`	each row is uniquely identified
`NOT NULL`	a value is always present
`UNIQUE`	no two rows share the value
`CHECK (...)`	values satisfy a rule
`DEFAULT x`	a value when none is given
`REFERENCES t(col)`	a foreign key: the value exists in another table

Constraints protect data quality no matter which application writes to the database:

Example 2Try it yourself

CREATE TABLE coupons (code TEXT PRIMARY KEY, percent INTEGER CHECK (percent BETWEEN 1 AND 90));
INSERT INTO coupons VALUES ('WELCOME10', 10);
INSERT INTO coupons VALUES ('TOOMUCH', 95);

Output

Error: CHECK constraint failed: percent BETWEEN 1 AND 90

Data types

Common types: INTEGER/BIGINT, NUMERIC(10,2) for money, VARCHAR(n)/TEXT, BOOLEAN, DATE, TIMESTAMP. SQLite is flexible about types; PostgreSQL and MySQL enforce them strictly.

Test yourself

Which constraint makes sure a rating is between 1 and 5?

UNIQUE
CHECK
DEFAULT
NOT NULL

Show answer

Answer: CHECK