Lesson 16 of 17 · Changing Data
CREATE TABLE
Create your own database tables with SQL. Learn data types, primary keys and the NOT NULL, UNIQUE, DEFAULT and CHECK constraints, plus ALTER and DROP TABLE.
- 8 min read
- Beginner
- Reviewed
- How to create a table and choose a data type for each column
- How constraints keep bad data out
- How to link a new table to an existing one
- How to change or remove a table
Designing a table
So far you have used tables that somebody else designed. Now you will design one.
Suppose the school wants learners to review courses. A review needs a rating, an optional comment and a link to the course it is about. CREATE TABLE defines the table: its name, its columns and the type of data each column holds.
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
course_id INTEGER,
rating INTEGER,
comment TEXT
);
INSERT INTO reviews (course_id, rating, comment)
VALUES (1, 5, 'Clear and friendly'),
(1, 4, 'Good pace'),
(2, 5, NULL);
SELECT * FROM reviews;A new table is empty. The INSERT adds three reviews, and the id is filled in automatically because it is the primary key.
Data types
Every column has a data type that says what kind of value belongs in it.
| Type | Holds | Example |
|---|---|---|
INTEGER | Whole numbers | 42 |
REAL | Numbers with decimals | 3.75 |
TEXT | Text of any length | 'Good pace' |
DATE | A calendar date | '2026-06-01' |
BOOLEAN | True or false | 1 or 0 |
These names work in the practice database. Other systems use more specific ones, such as VARCHAR(100) for text with a maximum length, DECIMAL(10, 2) for money and TIMESTAMP for a date with a time. The idea is identical: pick the type that fits the data.
Constraints: rules for good data
The first version of reviews accepts anything: a review with no rating, a rating of 900, a review for a course that does not exist. Constraints are rules written into the table that reject bad data at the door.
| Constraint | Rule |
|---|---|
PRIMARY KEY | Identifies the row. Unique and never missing. |
NOT NULL | A value is required. |
UNIQUE | No two rows may share this value. |
DEFAULT value | Used when no value is given. |
CHECK (condition) | The value must pass a test. |
REFERENCES table(column) | A foreign key pointing to another table. |
Here is a better design.
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
course_id INTEGER NOT NULL REFERENCES courses(id),
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
status TEXT NOT NULL DEFAULT 'published'
);
INSERT INTO reviews (course_id, rating, comment)
VALUES (1, 5, 'Clear and friendly'),
(2, 4, NULL);
SELECT * FROM reviews;Nobody supplied a status, so the default, published, was used for both rows.
Watching a constraint work
Try to add a rating of 9.
CREATE TABLE reviews ( id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL REFERENCES courses(id), rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5) ); INSERT INTO reviews (course_id, rating) VALUES (1, 9);
The insert is refused with a message that the CHECK constraint failed. Change the 9 to a 4 and it works. It is far better for the database to reject a wrong value now than for someone to find it in a report months later.
Using the new table with the old ones
A table you create behaves like any other. Because course_id points to courses, the two can be joined.
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
course_id INTEGER NOT NULL REFERENCES courses(id),
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5)
);
INSERT INTO reviews (course_id, rating)
VALUES (1, 5), (1, 4), (2, 5), (2, 3), (4, 3);
SELECT c.title,
COUNT(r.id) AS reviews,
ROUND(AVG(r.rating), 1) AS average_rating
FROM courses AS c
JOIN reviews AS r ON r.course_id = c.id
GROUP BY c.id, c.title
ORDER BY average_rating DESC;Changing a table later
Requirements change. ALTER TABLE modifies a table that already exists. The most common use is adding a column.
ALTER TABLE courses ADD COLUMN level TEXT DEFAULT 'Beginner'; SELECT title, hours, level FROM courses;
Existing rows get the default value for the new column.
Removing a table
DROP TABLE deletes a table and all the data in it. There is no undo.
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('temporary');
DROP TABLE notes;
SELECT * FROM notes;The last line fails with no such table: notes, which proves the table is gone.
Good habits for table design
- Give every table a primary key called
id. - Name foreign keys after what they point to:
course_id,student_id. - Use
NOT NULLunless a missing value truly makes sense. - Store one fact in one place. If a value can be looked up through a key, do not copy it.
- Use clear, lowercase names with underscores:
order_items, notOrdItm.
Common mistakes
Create a teachers table and add two rows to it. The SELECT in the editor shows the result when both steps work.
-- 1. Create a table called teachers with three columns: -- id INTEGER PRIMARY KEY -- name TEXT, required -- subject TEXT -- 2. Insert two teachers: Ada / Python and Linus / SQL -- Do not change the SELECT. SELECT name, subject FROM teachers ORDER BY id;
Which constraint makes sure a column always has a value?
Summary
CREATE TABLE name (column type, ...);defines a new table.- Each column has a data type such as
INTEGER,TEXTorDATE. - Constraints such as
NOT NULL,UNIQUE,DEFAULTandCHECKkeep bad data out. REFERENCEScreates a foreign key.ALTER TABLEchanges a table, andDROP TABLEremoves it with all its data.
One lesson to go: a project that uses everything.
Finished reading? Tick it off to track your progress.