A table that refuses bad data
Somebody types "tomorrow" in a column meant for dates. Somebody leaves a name empty. Somebody uses the same id twice.
A well set up table can refuse all three, before they spoil a report.
Key words
| Word | What it means | Example |
|---|---|---|
| Constraint | a rule the table enforces on its data | NOT NULL |
| NULL | a missing value | |
| Primary key | a column whose value is different in every row | |
| Data type | the kind of value a column holds | |
| Schema | the plan of a set of tables: columns and types | |
| Dataset | a collection of related data about one subject | |
| Default | the value used when none is given |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can write CREATE TABLE to set up a dataset from a design.
Starter
Have a go
Starter
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
CREATE TABLE turns a design into a table
A design says each column, its type and its rules. CREATE TABLE builds it.
List each column with its type. Then add constraints: PRIMARY KEY, NOT NULL (no missing value), UNIQUE, CHECK (a test the value must pass) and DEFAULT.
Try it: build and fill a club table
create table club_sessions (
session_id integer primary key,
club varchar not null,
session_date date not null,
pupils integer check (pupils >= 0),
room varchar default 'Hall'
);
insert into club_sessions (session_id, club, session_date, pupils)
values (1, 'chess', '2026-04-03', 14), (2, 'choir', '2026-04-04', 22);
select * from club_sessions;
-- Try it: add a third session with 9 pupils.Check
Have a go
Check
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.