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
  1. CREATE TABLE
  2. Common data types
  3. Constraints
  4. Auto-incrementing ids
  5. The shop database
  6. Sample data
  7. Changing a table later
  8. Check yourself

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 as CHECK (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

  1. Try inserting a product with price = -10. What happens, and which constraint stopped it?
  2. Try inserting an order with customer_id = 99. What happens? (In SQLite, run PRAGMA foreign_keys = ON; first; it is off by default.)
  3. Why does order_items not need its own id column?

More from Databases and SQL