CREATE TABLE, Data Types and Constraints

You can now read the setup script from lesson 2 line by line. In this lesson you learn how to create tables yourself, with rules that stop bad data from entering. Good tables make later queries simple. Bad tables, with no rules, fill up with blanks, duplicates and nonsense that someone must clean up later.

CREATE TABLE

CREATE TABLE reviews (
  id          INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL,
  reviewer    TEXT    NOT NULL,
  stars       INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
  comment     TEXT,
  created_on  DATE    DEFAULT CURRENT_DATE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Each line inside the brackets is a column, written as a name, a data type, then any rules. Table-level rules such as FOREIGN KEY come after the columns. Columns are separated by commas, and there is no comma after the last line.

Data types

A data type says what kind of value a column holds. The names change a little between engines, but the ideas are the same.

• Whole numbers: INTEGER (also INT, BIGINT). Use for ids, counts and quantities.
• Decimal numbers: DECIMAL(10,2) or NUMERIC in most engines. Use for money, because it does not round oddly. REAL or FLOAT are approximate and not good for money.
• Text: TEXT, or VARCHAR(100) with a length limit in MySQL, PostgreSQL and SQL Server.
• Dates and times: DATE, TIME, TIMESTAMP or DATETIME. SQLite has no special date type, and stores dates as text like 2024-06-02, which is why the format matters.
• True or false: BOOLEAN in PostgreSQL, TINYINT(1) in MySQL, and 0 or 1 in SQLite.

The shop uses a whole number for price to keep examples easy. A real shop with paise, cents or pence would store DECIMAL(10,2), or store the smallest unit as an integer, for example 12050 for 120.50.

Constraints: rules for the data

A constraint is a rule the database checks every time you insert or update a row. If the rule is broken, the statement fails and nothing is stored. Here are the five you will use all the time.

• PRIMARY KEY: unique and never empty. Identifies the row.
• NOT NULL: the column must have a value.
• UNIQUE: no two rows may have the same value in this column, such as an email address.
• DEFAULT x: the value to use when none is given, such as 'active' or today's date.
• CHECK (rule): any condition the value must satisfy, such as age >= 18.

See them work on a small test table.

CREATE TABLE members (
  id     INTEGER PRIMARY KEY,
  email  TEXT NOT NULL UNIQUE,
  age    INTEGER CHECK (age >= 18),
  status TEXT DEFAULT 'active'
);

INSERT INTO members (email, age) VALUES ('a@example.com', 20);
SELECT * FROM members;

id | email         | age | status
1  | a@example.com | 20  | active

The status was not given, so the default active was used. Now try three bad inserts:

INSERT INTO members (email, age) VALUES ('a@example.com', 30);
-- UNIQUE constraint failed: members.email

INSERT INTO members (email, age) VALUES (NULL, 30);
-- NOT NULL constraint failed: members.email

INSERT INTO members (email, age) VALUES ('b@example.com', 10);
-- CHECK constraint failed: age >= 18

Each message names the rule that was broken. This is the database protecting its own data, so that you do not have to rely on every program and every person to remember the rules.

Foreign keys and what they allow

The FOREIGN KEY (product_id) REFERENCES products(id) in the first example says that every review must point to a real product. You can add actions for what happens when the parent row is deleted:

FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE

With CASCADE, deleting a product also deletes its reviews. With the default, the delete is refused while reviews exist. Choose on purpose, and think about what you would want if a person deleted by mistake.

ALTER TABLE: change a table later

ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN phone TO mobile;

Adding a column to a table with data is easy; existing rows get NULL, or the default. Changing or dropping columns is more limited in SQLite than in other engines. In a live company database, ask before altering a table. A change on a big table can lock it for a while, and other programs may depend on the old shape.

DROP TABLE: remove a table completely

DROP TABLE IF EXISTS members;

DROP TABLE deletes the table and all its rows, and there is no undo outside a transaction or a backup. IF EXISTS avoids an error when the table is not there. Make a habit of reading the table name twice.

A simple design checklist

1. One table for each kind of thing. Name it clearly.
2. Give it a primary key.
3. Mark columns NOT NULL when a value is truly always needed.
4. Add UNIQUE for values that must not repeat, such as email or a product code.
5. Add foreign keys for links to other tables.
6. Choose data types that fit: numbers as numbers, dates as dates, money as decimals or whole smallest units.

Your task

1. Create a table called suppliers with an id primary key, a required name, a unique email and a country that defaults to 'India'.
2. Add a column supplier_id to products with ALTER TABLE and give it a value for one product.
3. Try to break each rule on suppliers (a duplicate email, a missing name) and read the messages.
4. Drop the suppliers table when you finish.

How to check it worked

• The country column shows India when you insert a supplier without one.
• The duplicate email insert fails with a UNIQUE error.
• After the drop, SELECT * FROM suppliers; says there is no such table.