SQL & Databases
Topic 10 · Building databases · ⏱ about 3 h

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.

Show:
In this topic
Beginner

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.

CREATE TABLE students ( student_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, born DATE, credits INTEGER NOT NULL DEFAULT 0 CHECK (credits >= 0) );
Beginner

Data types

Kind of dataStandard / PostgreSQLSQLite
whole numbersINTEGER, BIGINTINTEGER
exact decimals (money)NUMERIC(10, 2)stored as REAL or INTEGER cents
approximate decimalsREAL, DOUBLE PRECISIONREAL
textTEXT, VARCHAR(100)TEXT
true/falseBOOLEANINTEGER 0/1
dates and timesDATE, TIMESTAMPTEXT 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.

Beginner

Constraints: rules the database enforces

ConstraintMeans
PRIMARY KEYuniquely identifies each row; never NULL; one per table
NOT NULLa value is required
UNIQUEno two rows may have the same value
CHECK (…)a condition every row must meet
DEFAULT xthe 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.

Beginner

Foreign keys link tables

CREATE TABLE enrolments ( student_id INTEGER NOT NULL REFERENCES students(student_id), course TEXT NOT NULL, grade INTEGER CHECK (grade BETWEEN 0 AND 100), PRIMARY KEY (student_id, course) -- a key made of two columns );

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).

Beginner

Changing and removing tables

ALTER TABLE students ADD COLUMN phone TEXT; -- add a column ALTER TABLE students RENAME COLUMN born TO birth_date; DROP TABLE IF EXISTS old_students; -- delete a table and all its data

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

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.

Beginner

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)

Exercise 1. A students table

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]').

Exercise 2. Add a column

Add a column discontinued (integer, default 0) to products, then set it to 1 for products in the Stationery category.

Beginner

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.
Check yourself

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?

Go deeper

📚 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.

Ready