Creating Tables: Types, Keys & Constraints
Build your own tables with CREATE TABLE: choose data types, add a primary key, link tables with foreign keys, and protect the data with NOT NULL, UNIQUE, CHECK and DEFAULT. Then change or remove tables with ALTER and DROP.
CREATE TABLE
So far you have read tables someone else made. CREATE TABLE makes a new one: its name, and each column with a data type and optional rules called constraints.
Data types
| Kind of data | Standard / PostgreSQL | SQLite |
|---|---|---|
| whole numbers | INTEGER, BIGINT | INTEGER |
| exact decimals (money) | NUMERIC(10, 2) | stored as REAL or INTEGER cents |
| approximate decimals | REAL, DOUBLE PRECISION | REAL |
| text | TEXT, VARCHAR(100) | TEXT |
| true/false | BOOLEAN | INTEGER 0/1 |
| dates and times | DATE, TIMESTAMP | TEXT in ISO format |
SQLite is relaxed about types (it accepts any type names and will store a word in an INTEGER column); PostgreSQL is strict and will reject wrong values. Write your tables as if for PostgreSQL and they will work in both.
Constraints: rules the database enforces
| Constraint | Means |
|---|---|
PRIMARY KEY | uniquely identifies each row; never NULL; one per table |
NOT NULL | a value is required |
UNIQUE | no two rows may have the same value |
CHECK (…) | a condition every row must meet |
DEFAULT x | the value used when an insert leaves the column out |
REFERENCES other(col) | a foreign key: the value must exist in the other table |
Constraints stop bad data at the door, which is far cheaper than finding it later. A rejected insert gives an error like UNIQUE constraint failed: students.email.
Foreign keys link tables
The foreign key means you cannot enrol a student who does not exist, and (by default) cannot delete a student who still has enrolments. ON DELETE CASCADE would delete their enrolments with them. In SQLite, foreign keys are only enforced after PRAGMA foreign_keys = ON; (the shop database here turns it on).
Changing and removing tables
DROP TABLE cannot be undone. In real projects, changes to tables are written as numbered migration files so every copy of the database changes the same way.
Try it: create and constrain
Run "Create a table" first. Then try the constraint-breaking presets and read the errors.
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Create a table students with student_id (integer primary key), name (text, required) and email (text, unique). Then insert two students: (1, 'Sita', '[email protected]') and (2, 'Leo', '[email protected]').
Add a column discontinued (integer, default 0) to products, then set it to 1 for products in the Stationery category.
Summary
Key words
- CREATE TABLE
- defines a new table
- data type
- the kind of value a column holds
- constraint
- a rule the database enforces
- primary key
- unique, non-NULL identifier of a row
- foreign key
- a column that must match a key in another table
- composite key
- a key made of several columns
- ALTER / DROP
- change / delete a table
Common mistakes
- Storing money as REAL in a serious system: use NUMERIC or whole cents.
- Storing dates as "14/06/2025" text: use DATE or ISO text.
- Forgetting NOT NULL on columns that must have a value.
- No primary key, so duplicate rows creep in.
- DROP TABLE on the wrong table: there is no undo.
Quiz
1. Which constraint makes sure no two students share an email?
2. What does a foreign key do?
3. Which type is right for money in PostgreSQL?
4. A table may have how many primary keys?
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- SQL As Understood By SQLite ↗ the official SQLite language reference
- Use The Index, Luke ↗ a free guide to SQL indexing and performance