Creating tables and relationships

In plain English

A table stores rows of related data, and each row is identified by a primary key, a column whose value is unique in that table. A foreign key is a column that points to a primary key in another table, which is how the database enforces a relationship between two tables. A one-to-many relationship means one row in a parent table can be related to many rows in a child table, like one course having many lessons. ON DELETE decides what happens to child rows when their parent row is removed: cascade deletes them too, restrict blocks the delete, and set null empties the link. CREATE TABLE is the statement you use to build a table with its columns, types and keys.

Step by step

1. 1Write create table with the table name and its columns.
2. 2Choose a primary key column, often an identity integer.
3. 3Add a foreign key column in the child table that references the parent key.
4. 4Pick an on delete behavior for when the parent row goes away.
5. 5Insert a parent row and several child rows to see the relationship.

create table courses (
  id integer generated always as identity primary key,
  slug text not null unique,
  title text not null
);

create table lessons (
  id integer generated always as identity primary key,
  course_id integer not null references courses(id) on delete cascade,
  title text not null,
  minutes integer not null
);

Common mistakes

• Forgetting a primary key makes rows hard to identify and lets duplicate data sneak in.
• Choosing the wrong on delete action can delete rows you meant to keep, or block a delete you expected to work.
• Storing the parent name in every child row instead of using a foreign key duplicates data and breaks when the name changes.
• Using a foreign key that points at a column that is not the parent primary key can allow dangling references if that column allows duplicates.