Lesson 2 of 8 · 6 min
Creating tables
CREATE TABLE, choosing data types, constraints that keep data valid, and building the shop database used for the rest of the course.
On this page
Before you can store data, you define the table: its columns, their types, and the rules the data must follow.
CREATE TABLE
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
city VARCHAR(100),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Each line names a column, gives it a type, and adds optional constraints.
Common data types
| Type | Holds | Example |
|---|---|---|
INTEGER / BIGINT |
Whole numbers | 42 |
NUMERIC(10, 2) / DECIMAL |
Exact decimals, for money | 1499.00 |
REAL / DOUBLE PRECISION |
Approximate decimals, for measurements | 3.14159 |
VARCHAR(n) / TEXT |
Text | 'Asha' |
BOOLEAN |
true or false | TRUE |
DATE |
A calendar date | '2026-11-22' |
TIMESTAMP |
A date and time | '2026-11-22 14:30:00' |
Never store money in REAL or FLOAT. They are approximate, so 0.1 + 0.2 is not exactly 0.3. Use NUMERIC, or store whole paise or cents in an INTEGER.
Constraints
Constraints are rules the database enforces on every write. They are your last line of defence against bad data.
PRIMARY KEY: unique and never null. One per table.NOT NULL: the column must have a value.UNIQUE: no two rows can share this value (for example, an email).DEFAULT: the value to use when none is given.CHECK: any condition, such asCHECK (price >= 0).REFERENCES: a foreign key to another table.
Auto-incrementing ids
You usually want the database to pick the next id for you. The syntax varies:
-- PostgreSQL
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY
-- SQLite
id INTEGER PRIMARY KEY -- auto-increments automatically
The shop database
Here is the full schema used for the rest of the course. Run it once in your database. (The id columns use SQLite style; adjust for PostgreSQL or MySQL as shown above.)
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
city VARCHAR(100)
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
ordered_at DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id),
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);
order_items has a composite primary key: the pair (order_id, product_id) must be unique, so the same product can't appear twice in one order.
Sample data
INSERT INTO customers (id, name, email, city) VALUES
(1, 'Asha Rao', 'asha@example.com', 'Bengaluru'),
(2, 'Imran Shaikh', 'imran@example.com', 'Pune'),
(3, 'Neha Gupta', 'neha@example.com', 'Bengaluru'),
(4, 'Rahul Mehta', 'rahul@example.com', NULL);
INSERT INTO products (id, name, category, price) VALUES
(1, 'Mechanical keyboard', 'Accessories', 4999.00),
(2, 'USB-C hub', 'Accessories', 1999.00),
(3, '27-inch monitor', 'Displays', 18999.00),
(4, 'Laptop stand', 'Accessories', 1499.00),
(5, 'Webcam', 'Video', 3499.00);
INSERT INTO orders (id, customer_id, ordered_at, status) VALUES
(101, 1, '2026-11-01', 'shipped'),
(102, 3, '2026-11-03', 'pending'),
(103, 1, '2026-11-05', 'pending'),
(104, 2, '2026-11-06', 'cancelled');
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(101, 1, 1), (101, 4, 1),
(102, 3, 2),
(103, 2, 1), (103, 5, 1),
(104, 1, 1);
Changing a table later
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers DROP COLUMN phone;
DROP TABLE order_items; -- deletes the table and all its data. No undo.
Check yourself
- Try inserting a product with
price = -10. What happens, and which constraint stopped it? - Try inserting an order with
customer_id = 99. What happens? (In SQLite, runPRAGMA foreign_keys = ON;first; it is off by default.) - Why does
order_itemsnot need its ownidcolumn?
More from Databases and SQL
- Lesson 4 · 5 minFiltering and sortingWHERE with comparison and logical operators, IN, BETWEEN, LIKE, handling NULL, sorting on several columns, and paging through results.
- Lesson 5 · 6 minJoining tablesINNER JOIN, LEFT JOIN, joining three or more tables, self joins, and the mistakes that quietly multiply or drop rows.
- Lesson 6 · 5 minGrouping and aggregatesCOUNT, SUM, AVG, MIN and MAX, GROUP BY and HAVING, subqueries, common table expressions, and a first look at window functions.