المفاتيح الأجنبية والعلاقات

Written Explanation

The idea: store the id, not the details

A foreign key (FK) is a column in one table whose values are primary-key values of another table. Instead of repeating a member's details in each loan, the loan stores the member's id.

members(id PK, name, email, ...)
loans(id PK, member_id FK -> members.id, book_id FK -> books.id, loaned_on, due_on, returned_on)

Read member_id FK -> members.id as "the column member_id holds values taken from members.id". Sample data:

Layla's details are stored once, in members. Loans 10 and 11 both say member_id = 1. If her email changes, you edit one row and every loan that points at her is correct automatically. This is the central benefit: each fact is stored in exactly one place.

Naming convention: a foreign key is named after the table it points to, in singular, plus _id: member_id, book_id, author_id. The name tells readers where it leads.

Referential integrity

A pointer is only useful if it points somewhere. The database can guarantee that. PostgreSQL's manual describes a foreign key as a rule that the values in a column must match values appearing in some row of another table, and calls maintaining this referential integrity. In practice:

• If you try to create a loan with member_id = 999 and no such member exists, the database refuses.
• You cannot silently end up with "orphan" rows pointing at nothing.

Without the rule, every program that writes data must remember to check. With it, the database enforces consistency. You will declare foreign keys in the SQL module.

Two details worth knowing:

• A foreign key can point only at a column that is unique, normally the other table's primary key. That is why the previous lesson came first.
• The FK column can contain NULL if "no value" makes sense. The PostgreSQL manual notes that a row whose foreign key column is NULL passes the check. For a loan, member_id should always be present, so you would also mark it required. For an optional relationship (a book with no known publisher), NULL is acceptable.

One-to-many

The most common relationship is one-to-many: one row on one side relates to many rows on the other side. One member has many loans; one author has written many books; one customer places many orders.

Rule: the foreign key lives on the "many" side, and points at the "one" side.

Why? A single cell can hold only one value. A loan has exactly one member, so loans can hold one member_id. A member has many loans, so a members row would need a list of loan ids in one cell, which a column cannot hold (and lists in cells break the rules we meet in lesson L05).

To find where the FK goes, ask two questions about each pair of entities:

1. Can one A relate to many B?
2. Can one B relate to many A?

If A to many B is yes and B to many A is no, the FK goes in B's table. "One author, many books" (in the simplified case where each book has a single author): one author has many books, but each book has one author, so books gets author_id.

authors(id PK, name)
books(id PK, title, isbn, published_year, author_id FK -> authors.id)

If both answers are yes, you have a many-to-many relationship; the next lesson handles it. Note that real books often have several authors, which is exactly why lesson L04 replaces this simple version.

Reading relationship diagrams

Designers draw entities as boxes and relationships as lines; a common notation (crow's foot) draws a fork on the "many" end. In text we can write:

members 1 ---- * loans * ---- 1 books

Read it as "one member has many loans; one book appears in many loans". The 1 and * mark the ends. Notice that the table in the middle holds both foreign keys. Whichever notation you use, include table names, the keys, and the cardinality (the 1 or *) on each end. The ER diagram in the module lab uses such boxes and lines.

What happens when you delete the referenced row?

Suppose member 1 is deleted, but loans point to her. Options exist, and the database lets you choose per foreign key. The PostgreSQL manual lists these actions, and other systems offer similar ones:

Which is right depends on meaning. The manual's guidance: choose cascade when the referencing rows are a component that cannot exist on its own; choose refuse when the two tables describe independent things. A loan record is history the library probably wants to keep, so refusing to delete a member who has loans (or marking the member inactive instead, as our is_active column allows) is a sensible choice. You will set these actions in the SQL module; for now just notice that the decision exists.

One-to-one, briefly

In a one-to-one relationship each row in A relates to at most one row in B and vice versa: for example members and a separate member_photos table. It is rare, because the two tables could usually be one. When needed, the FK goes in one table and is also marked unique.