Real data is connected. A task belongs to a user; a task can have many tags. Modeling these links correctly is the heart of relational design.
A foreign key is a column that points at another table's primary key.
1CREATE TABLE tasks (
2 id BIGSERIAL PRIMARY KEY,
3 title VARCHAR(200) NOT NULL,
4 owner_id BIGINT NOT NULL REFERENCES users(id)
5);owner_id references users.id. The database now enforces that every task points at a real user — you cannot insert a task with a non-existent owner.
The most common relationship. One user has many tasks.
users tasks
┌────┐ ┌────┬──────────┐
│ id │◀──────────────│ id │ owner_id │
└────┘ └────┴──────────┘
The "many" side (tasks) holds the foreign key. There is no task_id on users.
A task can have many tags; a tag can apply to many tasks. This needs a join table.
1CREATE TABLE tags (
2 id BIGSERIAL PRIMARY KEY,
3 name VARCHAR(50) UNIQUE NOT NULL
4);
5
6CREATE TABLE task_tags (
7 task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
8 tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
9 PRIMARY KEY (task_id, tag_id)
10);The composite primary key (task_id, tag_id) prevents the same tag being attached twice.
What happens to children when a parent is deleted?
| Clause | Effect |
|---|---|
ON DELETE CASCADE | Delete the children too |
ON DELETE SET NULL | Orphan them (FK becomes NULL) |
ON DELETE RESTRICT | Block the delete if children exist |
Choosing the right behavior is a Delete design decision — pick deliberately, because the default (NO ACTION) will surprise you.