Lesson 21 of 26 · see the practice database

Creating tables

Someone had to make the tables you have been querying. CREATE TABLE defines a new table: its name, its columns, the kind of data each column holds and the rules the data must obey. Good table design prevents bad data from getting in, which is far easier than cleaning it up afterwards.

A first table

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  phone TEXT
);

Each line inside the brackets defines one column: its name, its type, and any rules. Commas separate the columns. A new table is empty.

INSERT INTO suppliers (name, city, phone)
VALUES ('Punjab Grain Traders', 'Ludhiana', '98140 00000');

SELECT * FROM suppliers;

Data types

A type says what kind of value a column may hold. These are the common ones.

VARCHAR(100) means text of up to 100 characters. DECIMAL(10,2) means a number with up to 10 digits in all, 2 of them after the decimal point.

Some advice on choosing:

• Money goes in DECIMAL, never in FLOAT. Floating-point numbers cannot store values such as 0.10 exactly, and the tiny errors add up. Our practice tables keep prices as whole rupees for simplicity.
• Phone numbers, PIN codes and account numbers are text, not numbers. You never add them up, they can start with zero, and they may contain spaces or a plus sign.
• Dates go in a date type where the database has one, not in text.

SQLite is unusually relaxed about types. It has only a few storage kinds (integer, real, text, blob), has no separate date type, and by default will store text in a column declared as a number. That is why our practice tables keep dates as text. MySQL, PostgreSQL, SQL Server and Oracle are strict: a column declared INTEGER holds only whole numbers.

Constraints: rules on the data

A constraint is a rule that every row must satisfy. The database refuses any insert or update that would break one.

CREATE TABLE coupons (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  percent_off INTEGER NOT NULL CHECK (percent_off BETWEEN 1 AND 90),
  active INTEGER NOT NULL DEFAULT 1,
  created TEXT NOT NULL DEFAULT '2026-11-12'
);

See them at work. A plain insert uses the defaults:

INSERT INTO coupons (code, percent_off) VALUES ('DIWALI10', 10);

SELECT * FROM coupons;

The same code again breaks UNIQUE:

INSERT INTO coupons (code, percent_off) VALUES ('DIWALI10', 20);

Error: UNIQUE constraint failed: coupons.code

A discount of 150 percent breaks the CHECK:

INSERT INTO coupons (code, percent_off) VALUES ('MISTAKE', 150);

Error: CHECK constraint failed: percent_off BETWEEN 1 AND 90

A missing code breaks NOT NULL:

INSERT INTO coupons (percent_off) VALUES (5);

Error: NOT NULL constraint failed: coupons.code

Each refusal is a bug caught before it reached the data.

The primary key

Every table should have one. It is the column, or occasionally the pair of columns, that identifies each row. The usual choice is an id number that the database hands out, as covered in the lesson on INSERT. Avoid using something from the real world, such as a phone number or an email address, as the primary key. Those change, and a primary key should never have to.

Foreign keys: linking tables

A foreign key says "the value in this column must be the primary key of a row in that table". It is what keeps an order from pointing to a customer who does not exist.

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  stars INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
  comment TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO reviews (product_id, customer_id, stars, comment)
VALUES (13, 1, 5, 'Keeps water cold all day');

SELECT p.name, c.name AS customer, r.stars, r.comment
FROM reviews r
JOIN products p  ON r.product_id = p.id
JOIN customers c ON r.customer_id = c.id;

The foreign keys are exactly the columns used in the ON of the joins. Table design and querying are two sides of the same idea.

As the last lesson said, SQLite checks foreign keys only when a program switches the check on, so in the practice box a review for product 999 is accepted. The other major databases would refuse it.

A key made of two columns

In a junction table, the natural key is the pair of ids. Written this way, the same product cannot be put into the same order twice.

CREATE TABLE wishlist (
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  added TEXT NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

If the table already exists

Creating a table whose name is taken is an error.

CREATE TABLE suppliers (id INTEGER);

Error: table suppliers already exists

CREATE TABLE IF NOT EXISTS makes the table only if it is not there, and otherwise does nothing. Scripts that may run more than once use it. SQLite, MySQL and PostgreSQL support it.

CREATE TABLE IF NOT EXISTS suppliers (id INTEGER);

A table from a query

CREATE TABLE ... AS SELECT makes a new table from the result of a query and fills it in one step. It is handy for snapshots and for trying things on a copy.

CREATE TABLE home_products AS
SELECT id, name, price FROM products WHERE category = 'Home';

SELECT * FROM home_products;

The copy gets the columns and the data but not the constraints. Its id is no longer a primary key. SQL Server writes this as SELECT ... INTO new_table FROM ....

Seeing what tables exist

Each database has its own way to list tables. In SQLite, a built-in table called sqlite_master holds the list.

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

MySQL uses SHOW TABLES, and MySQL, PostgreSQL and SQL Server share a standard set of views called information_schema.

Design advice in brief

1. One table for each kind of thing: customers, products, orders. Do not mix them.
2. Store each fact once. If the same city name has to be typed into many rows of many tables, the design can be improved.
3. Give every table a primary key.
4. Link tables with foreign keys.
5. Do not store what can be worked out. An order's total is the sum of its lines.
6. Say NOT NULL wherever a value must exist. Allow NULL only where "unknown" is a real possibility.
7. Use clear, consistent names in small letters with underscores: order_date, not OrdDt.

The formal name for the first three points is normalisation, and the interview questions page says more about it.

Try it yourself

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);

CREATE TABLE payments (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), amount INTEGER NOT NULL CHECK (amount > 0), paid_on TEXT NOT NULL);

CREATE TABLE cheap_products AS SELECT name, price FROM products WHERE price < 150;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.