# فصل ۰۳ — کلیدِ خارجی و سطرِ یتیم

<div dir="rtl">

# فصل ۰۳ — کلیدِ خارجی و سطرِ یتیم

## در این فصل چه یاد می‌گیری

از فصلِ ۱ یک عدد روی دستمان مانده: **۳۶ رویداد به کتابی اشاره می‌کنند که در فهرست نیست، و ۱۹ رویداد به عضوی که وجود ندارد.** جمعاً ۵۵ سطرِ یتیم در جدولی که هیچ‌کس عمداً خرابش نکرده.

این فصل نشان می‌دهد چرا هیچ‌کس جلویشان را نگرفت، و جوابش یک خطِ کوچک است: **در `SQLite` اجرای کلیدِ خارجی پیش‌فرض خاموش است.** یعنی می‌توانی `FOREIGN KEY` را در اسکیما بنویسی، پایگاه داده آن را بپذیرد، و بعد یک سطرِ یتیم را با کمالِ میل قبول کند.

همان `INSERT` را دو بار می‌زنیم: **بارِ اول پذیرفته می‌شود، بارِ دوم با پیامِ `FOREIGN KEY constraint failed` رد.** تنها چیزی که بینشان عوض شده یک خط تنظیم است.

![یک زنجیرِ فلزی که یکی از حلقه‌هایش باز مانده و به هیچ حلقهٔ دیگری وصل نیست](/lesson-assets/t4-s03-foreign-keys/prompt/01-image-open-link.png)

آخر این فصل می‌توانی:

- ارجاعِ بینِ دو جدول را در اسکیما اعلام کنی
- بگویی چرا اعلامِ `FOREIGN KEY` به‌تنهایی هیچ چیزی را تضمین نمی‌کند
- سطرهای یتیمِ یک پایگاه دادهٔ موجود را با یک فرمان پیدا کنی
- برای حذفِ سطرِ پدر یکی از سه رفتارِ ممکن را انتخاب کنی و بگویی چرا

## قبل از شروع

**از فصلِ ۲:** کلیدِ اصلی و کلیدِ جانشین. کلیدِ خارجی چیزی جز یک ارجاع به همان کلید نیست. **از ترمِ ۳ فصلِ ۱ و ۲:** `JOIN` و `LEFT JOIN` و الگوی «آنچه نیست».

**تعریف، در یک جمله:** کلیدِ خارجی ستونی است که مقدارش **باید** در ستونِ کلیدِ جدولِ دیگری وجود داشته باشد. نامِ فارسیِ این قانون یکپارچگیِ ارجاعی است، و تمامِ این فصل دربارهٔ فاصلهٔ «باید» با «هست» است.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-4-modelling/session-03-foreign-keys.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `catalog_raw` | ۱٬۲۰۰ | کمتر از یک ثانیه |
| `members_raw` | ۳۰۰ | آنی |
| `loan_events` | ۲۶٬۱۱۹ | حدودِ یک ثانیه |

<div dir="rtl">

## ۱. پرسشی که جوابش «نمی‌دانیم» است

اول ببینیم آن ۵۵ سطر چه شکلی‌اند و به چه چیزی اشاره می‌کنند.

</div>

```python
missing_books = q("""
    SELECT e.catalog_id, COUNT(*) AS events
    FROM loan_events AS e
    WHERE NOT EXISTS (SELECT 1 FROM catalog_raw AS c WHERE c.id = e.catalog_id)
    GROUP BY e.catalog_id
    ORDER BY e.catalog_id
""")
missing_members = q("""
    SELECT e.member_id, COUNT(*) AS events
    FROM loan_events AS e
    WHERE NOT EXISTS (SELECT 1 FROM members_raw AS m WHERE m.id = e.member_id)
    GROUP BY e.member_id
    ORDER BY e.member_id
""")

print("رویدادهایی که کتابشان در فهرست نیست:")
print(missing_books)
print("جمع:", int(missing_books["events"].sum()))
print()
print("رویدادهایی که عضوشان در فهرست نیست:")
print(missing_members)
print("جمع:", int(missing_members["events"].sum()))
```

```text
رویدادهایی که کتابشان در فهرست نیست:
   catalog_id  events
0        1201       3
1        1202       6
2        1203       9
3        1204       3
4        1205       7
5        1206       8
جمع: 36

رویدادهایی که عضوشان در فهرست نیست:
   member_id  events
0        901       8
1        902      11
جمع: 19
```

<div dir="rtl">

**شش شناسهٔ کتاب و دو شناسهٔ عضو، جمعاً ۵۵ رویداد.** داستانشان هم پیچیده نیست: شش عنوان از فهرست حذف شدند و دو حسابِ عضویت پاک شد، و رویدادهایشان همان‌جا ماندند.

حالا هزینه‌اش. یک گزارشِ کاملاً معمولی بنویسیم: «پرامانت‌ترین کتاب‌ها».

</div>

```python
inner = q("""
    SELECT COUNT(*) AS rows_kept
    FROM loan_events AS e
    JOIN catalog_raw AS c ON c.id = e.catalog_id
    WHERE e.kind = ?
""", ("out",))
outer = q("""
    SELECT COUNT(*) AS rows_kept
    FROM loan_events AS e
    LEFT JOIN catalog_raw AS c ON c.id = e.catalog_id
    WHERE e.kind = ?
""", ("out",))

print("امانت‌ها با INNER JOIN:", int(inner.iloc[0, 0]))
print("امانت‌ها با LEFT JOIN :", int(outer.iloc[0, 0]))
print("اختلاف                :", int(outer.iloc[0, 0]) - int(inner.iloc[0, 0]))
```

```text
امانت‌ها با INNER JOIN: 13422
امانت‌ها با LEFT JOIN : 13458
اختلاف                : 36
```

<div dir="rtl">

**سی‌وشش امانت با `INNER JOIN` از گزارش می‌افتند بیرون** — همان درسِ ترمِ ۳، این بار با یک ریشهٔ تازه: سطرها گم نمی‌شوند چون پرسش بد است، **گم می‌شوند چون داده خراب است.**

و بدترین بخشش این است که با `LEFT JOIN` هم نجات پیدا نمی‌کنی. آن ۳۶ سطر می‌مانند، ولی نامِ کتابشان `NULL` است — و چون هر شش کتاب یک نامِ خالی دارند، **در گزارشی که بر اساسِ عنوان گروه‌بندی شده همه‌شان در یک سطر جمع می‌شوند: یک کتابِ بی‌نام با ۳۶ امانت، که در واقع شش کتاب است.** اطلاعات از بین رفته، نه پنهان شده.

## ۲. `FOREIGN KEY` را در اسکیما بنویس

سه کتاب و سه امانت، جوری که با انگشت شمردنی باشد. این بار اتصالِ تازه‌ای به همان فایل باز می‌کنیم — دلیلش را بخشِ ۴ می‌گوید.

</div>

```python
import sqlite3

legacy = sqlite3.connect(DB_PATH)
legacy.executescript("""
    DROP TABLE IF EXISTS demo_loans;
    DROP TABLE IF EXISTS demo_books;
    CREATE TABLE demo_books (
        id    INTEGER PRIMARY KEY,
        title TEXT NOT NULL
    );
    CREATE TABLE demo_loans (
        id      INTEGER PRIMARY KEY,
        book_id INTEGER NOT NULL REFERENCES demo_books(id),
        member  TEXT    NOT NULL
    );
""")
legacy.executemany("INSERT INTO demo_books VALUES (?, ?)",
                   [(1, "الف"), (2, "ب"), (3, "پ")])
legacy.commit()

# PRAGMA را از همان اتصالی می‌پرسیم که جدول‌ها را ساخته است.
print(pd.read_sql_query("PRAGMA foreign_key_list(demo_loans)", legacy))
```

```text
   id  seq       table     from  to  on_update  on_delete match
0   0    0  demo_books  book_id  id  NO ACTION  NO ACTION  NONE
```

<div dir="rtl">

`REFERENCES demo_books(id)` یعنی «هر مقداری که در این ستون می‌نشیند، باید در ستونِ `id` جدولِ `demo_books` وجود داشته باشد». `PRAGMA foreign_key_list` نشان می‌دهد که پایگاه داده این اعلام را **ثبت کرده است**.

**پس تا اینجا همه‌چیز درست به‌نظر می‌رسد.** حالا یک امانت برای کتابی وارد می‌کنیم که وجود ندارد:

</div>

```python
legacy.execute("INSERT INTO demo_loans VALUES (?, ?, ?)", (1, 99, "مینا"))
legacy.commit()

print("سطرهای demo_loans:")
print(q("SELECT * FROM demo_loans"))
print()
print("و کتابی با شناسهٔ ۹۹ در demo_books هست؟",
      int(q("SELECT COUNT(*) AS n FROM demo_books WHERE id = ?", (99,)).iloc[0, 0]))
```

```text
سطرهای demo_loans:
   id  book_id member
0   1       99   مینا

و کتابی با شناسهٔ ۹۹ در demo_books هست؟ 0
```

<div dir="rtl">

**قبول شد. هیچ خطایی، هیچ هشداری — با اینکه قید سرِ جایش نوشته شده بود و پایگاه داده هم ثبتش کرده بود.**

اگر تا امروز فکر می‌کردی نوشتنِ `FOREIGN KEY` یعنی امن شدن، همین‌جا آن فرض را زمین بگذار.

## ۳. خطی که از فصلِ اولِ ترمِ ۱ آنجا بوده

</div>

```python
print("PRAGMA foreign_keys — اتصالی که همین حالا باز کردیم :",
      legacy.execute("PRAGMA foreign_keys").fetchone()[0])
print("PRAGMA foreign_keys — اتصالِ سلولِ راه‌اندازیِ دوره  :",
      con.execute("PRAGMA foreign_keys").fetchone()[0])
```

```text
PRAGMA foreign_keys — اتصالی که همین حالا باز کردیم : 0
PRAGMA foreign_keys — اتصالِ سلولِ راه‌اندازیِ دوره  : 1
```

<div dir="rtl">

**صفر و یک.**

سلولِ راه‌اندازیِ این دوره، از اولین فصلِ ترمِ ۱ تا همین‌جا، یک خط داشته که هیچ‌وقت به آن دقت نکرده‌ای:

</div>

```sql
PRAGMA foreign_keys = ON
```

<div dir="rtl">

بدونِ آن خط، `SQLite` قیدهای کلیدِ خارجی را **می‌خواند، ذخیره می‌کند، و اجرا نمی‌کند.** دلیلش تاریخی و مستند است: این قابلیت سال‌ها بعد از خودِ `SQLite` اضافه شد و روشن کردنش به‌شکلِ پیش‌فرض، برنامه‌های موجود را می‌شکست. پس تصمیم گرفتند خاموش بماند و **همان تصمیم تا امروز پابرجاست.**

حالا روشنش می‌کنیم و **دقیقاً همان `INSERT` را دوباره می‌زنیم:**

</div>

```python
legacy.commit()                      # PRAGMA وسطِ یک تراکنشِ باز بی‌صدا کاری نمی‌کند
legacy.execute("PRAGMA foreign_keys = ON")
print("حالا:", legacy.execute("PRAGMA foreign_keys").fetchone()[0])
print()
try:
    legacy.execute("INSERT INTO demo_loans VALUES (?, ?, ?)", (2, 98, "سهند"))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
حالا: 1

sqlite3.IntegrityError
FOREIGN KEY constraint failed
```

<div dir="rtl">

**همان دستور، همان جدول، همان اسکیما — این بار رد شد.**

**و آن `commit` قبل از `PRAGMA` تشریفات نیست.** اگر تراکنشی باز باشد، `SQLite` این تنظیم را بی‌صدا نادیده می‌گیرد: نه خطا می‌دهد و نه اثری. یعنی می‌توانی خطش را بنویسی، مطمئن باشی که روشن است، و نباشد. **همیشه بعد از تنظیمش، مقدارش را بخوان و ببین** — دقیقاً کاری که خطِ اولِ همین سلول کرد.

> ✅ **چک کن:** خطِ اول باید `1` چاپ کند، نه چیزِ دیگری. اگر `0` گرفتی، `commit` را جا انداخته‌ای و `INSERT` بعدی هم بدونِ اعتراض قبول می‌شود — که بدترین حالت است، چون فکر می‌کنی محافظت داری و نداری.

## ۴. قید به اتصال بند است، نه به فایل

این مهم‌ترین جملهٔ فصل است و از آن دو عدد بالا مستقیم بیرون می‌آید. **`PRAGMA foreign_keys` یک تنظیمِ اتصال است، نه یک ویژگیِ پایگاه داده.** همین حالا دو اتصال به **یک فایل** داریم که یکی قید را اجرا می‌کند و دیگری نه.

</div>

```python
legacy.rollback()                    # تراکنشِ ناتمامِ INSERT ردشدهٔ بخش قبل را ببند
try:
    con.execute("INSERT INTO demo_loans VALUES (?, ?, ?)", (3, 97, "کاوه"))
except Exception as error:
    print("اتصالِ دوره  :", type(error).__name__, "|", error)
con.rollback()

legacy.execute("PRAGMA foreign_keys = OFF")
legacy.execute("INSERT INTO demo_loans VALUES (?, ?, ?)", (4, 96, "پریسا"))
legacy.commit()
print("اتصالِ legacy:", "قبول شد")
print()
print("سطرهای جدول:", int(q("SELECT COUNT(*) AS n FROM demo_loans").iloc[0, 0]))
print(q("SELECT * FROM demo_loans ORDER BY id"))
```

```text
اتصالِ دوره  : IntegrityError | FOREIGN KEY constraint failed
اتصالِ legacy: قبول شد

سطرهای جدول: 2
   id  book_id member
0   1       99   مینا
1   4       96  پریسا
```

<div dir="rtl">

**یک فایل، دو اتصال، دو قانونِ متفاوت — و جدول حالا دو سطرِ یتیم دارد که یکی‌شان همین الان ساخته شد.**

پیامدِ عملی‌اش را جدی بگیر: **اگر برنامهٔ تو این خط را دارد و اسکریپتِ واردکردنِ داده ندارد، اسکریپت می‌تواند دادهٔ خرابی بسازد که برنامه هرگز اجازه‌اش را نمی‌داد.** و این یکی از دو راهی است که ۵۵ سطرِ بخشِ ۱ می‌توانستند از آن بیایند. راهِ دوم را خودِ جدول‌های ما نشان می‌دهند: `loan_events` اصلاً قیدی اعلام نکرده، پس حتی اتصالِ پرچم‌روشن هم جلویشان را نمی‌گرفت.

**و نکتهٔ دومی که همان‌قدر مهم است: روشن کردنِ قید، گذشته را پاک نمی‌کند.** آن سطری که با `id = 1` وارد شد و به کتابِ ۹۹ اشاره می‌کند، هنوز سرِ جایش است. قید فقط دربارهٔ نوشتنِ **بعدی** حرف می‌زند.

## ۵. `PRAGMA foreign_key_check`: پیدا کردنِ خرابیِ موجود

پس با دادهٔ خرابی که از قبل هست چه کنیم؟ `SQLite` یک فرمان دارد که **کلِ پایگاه داده را می‌گردد و هر سطری را که قیدش را نقض می‌کند فهرست می‌کند** — بدونِ اینکه چیزی را عوض کند.

</div>

```python
print(pd.read_sql_query("PRAGMA foreign_key_check(demo_loans)", legacy))
```

```text
        table  rowid      parent  fkid
0  demo_loans      1  demo_books     0
1  demo_loans      4  demo_books     0
```

<div dir="rtl">

چهار ستون: نامِ جدولِ خراب، `rowid` سطرِ خراب، جدولی که باید به آن اشاره می‌کرد، و شمارهٔ قید.

حالا همان کار روی دادهٔ واقعی. **جدول‌های `catalog_raw` و `loan_events` هیچ قیدی ندارند، پس اول باید اسکیمای درست را بسازیم و داده را داخلش بریزیم — دقیقاً همان کاری که سیستمِ قبلی کرده بود، با پرچمِ خاموش.**

</div>

```python
legacy.execute("PRAGMA foreign_keys = OFF")
legacy.executescript("""
    DROP TABLE IF EXISTS events_ref;
    DROP TABLE IF EXISTS books_ref;
    DROP TABLE IF EXISTS members_ref;
    CREATE TABLE books_ref   (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
    CREATE TABLE members_ref (id INTEGER PRIMARY KEY, name  TEXT NOT NULL);
    CREATE TABLE events_ref (
        id          INTEGER PRIMARY KEY,
        catalog_id  INTEGER NOT NULL REFERENCES books_ref(id),
        member_id   INTEGER NOT NULL REFERENCES members_ref(id),
        happened_at TEXT    NOT NULL,
        kind        TEXT    NOT NULL
    );
    INSERT INTO books_ref   SELECT id, title FROM catalog_raw;
    INSERT INTO members_ref SELECT id, name  FROM members_raw;
    INSERT INTO events_ref  SELECT id, catalog_id, member_id, happened_at, kind
                            FROM loan_events;
""")
legacy.commit()

broken = pd.read_sql_query("PRAGMA foreign_key_check(events_ref)", legacy)
print("سطرهای وارد شده:", int(q("SELECT COUNT(*) AS n FROM events_ref").iloc[0, 0]))
print("سطرهایی که قید را نقض می‌کنند:", len(broken))
print()
print(broken.groupby("parent").size().to_string())
print()
print(broken.head(3))
```

```text
سطرهای وارد شده: 26119
سطرهایی که قید را نقض می‌کنند: 55

parent
books_ref      36
members_ref    19

        table  rowid     parent  fkid
0  events_ref    320  books_ref     1
1  events_ref    657  books_ref     1
2  events_ref    942  books_ref     1
```

<div dir="rtl">

**بیست‌وشش هزار و صد و نوزده سطر بدونِ هیچ اعتراضی وارد شدند، و ۵۵تایشان قید را نقض می‌کنند.**

و شمارشِ دوم همان‌جاست: ۳۶ سطر به `books_ref` اشاره می‌کنند و ۱۹ سطر به `members_ref` — **دقیقاً همان دو عددِ بخشِ ۱، این بار از راهِ کاملاً متفاوتی.** آنجا با `NOT EXISTS` شمردیم، اینجا خودِ موتور شمرد.

**این فرمان اولین کاری است که روی هر پایگاه دادهٔ ناشناسی باید بزنی**، درست مثلِ `PRAGMA table_info` در ترمِ ۱. چند ثانیه وقت می‌گیرد و به تو می‌گوید داده‌ات چقدر به قوانینِ خودش وفادار بوده.

## ۶. `ON DELETE`: وقتی سطرِ پدر حذف می‌شود

قید جلوی نوشتنِ فرزندِ بی‌پدر را می‌گیرد. ولی اگر پدر **بعداً** حذف شود چه؟ سه رفتار ممکن است و انتخابش با توست.

</div>

```python
legacy.execute("PRAGMA foreign_keys = OFF")
legacy.executescript("""
    DROP TABLE IF EXISTS strict_loans;  DROP TABLE IF EXISTS strict_books;
    DROP TABLE IF EXISTS cascade_loans; DROP TABLE IF EXISTS cascade_books;
    DROP TABLE IF EXISTS null_loans;    DROP TABLE IF EXISTS null_books;
    CREATE TABLE strict_books  (id INTEGER PRIMARY KEY, title TEXT);
    CREATE TABLE cascade_books (id INTEGER PRIMARY KEY, title TEXT);
    CREATE TABLE null_books    (id INTEGER PRIMARY KEY, title TEXT);
    CREATE TABLE strict_loans (
        id INTEGER PRIMARY KEY,
        book_id INTEGER REFERENCES strict_books(id)
    );
    CREATE TABLE cascade_loans (
        id INTEGER PRIMARY KEY,
        book_id INTEGER REFERENCES cascade_books(id) ON DELETE CASCADE
    );
    CREATE TABLE null_loans (
        id INTEGER PRIMARY KEY,
        book_id INTEGER REFERENCES null_books(id) ON DELETE SET NULL
    );
""")
legacy.commit()
legacy.execute("PRAGMA foreign_keys = ON")
for books, loans in (("strict_books", "strict_loans"),
                     ("cascade_books", "cascade_loans"),
                     ("null_books", "null_loans")):
    # نام جدول به متن پرسش چسبیده، نه مقدار — همان مرز ترم ۱ فصل ۷.
    legacy.executemany(f"INSERT INTO {books} VALUES (?, ?)", [(1, "الف"), (2, "ب")])
    legacy.executemany(f"INSERT INTO {loans} VALUES (?, ?)", [(1, 1), (2, 1), (3, 2)])
legacy.commit()

try:
    legacy.execute("DELETE FROM strict_books WHERE id = ?", (1,))
except Exception as error:
    print("پیش‌فرض (بدون ON DELETE):", type(error).__name__, "|", error)
legacy.rollback()
legacy.execute("DELETE FROM cascade_books WHERE id = ?", (1,))
legacy.execute("DELETE FROM null_books WHERE id = ?", (1,))
legacy.commit()
print("ON DELETE CASCADE  → سطرهای امانت:",
      legacy.execute("SELECT id, book_id FROM cascade_loans").fetchall())
print("ON DELETE SET NULL → سطرهای امانت:",
      legacy.execute("SELECT id, book_id FROM null_loans").fetchall())
```

```text
پیش‌فرض (بدون ON DELETE): IntegrityError | FOREIGN KEY constraint failed
ON DELETE CASCADE  → سطرهای امانت: [(3, 2)]
ON DELETE SET NULL → سطرهای امانت: [(1, None), (2, None), (3, 2)]
```

<div dir="rtl">

**سه رفتار، سه تصمیمِ متفاوت دربارهٔ همان حذف:**

1. **پیش‌فرض** حذف را رد می‌کند. محافظه‌کارترین حالت و در بیشترِ موارد درست‌ترین: اگر کتابی امانت‌های ثبت‌شده دارد، حذفش یعنی پاک کردنِ تاریخ.
2. **`CASCADE`** فرزندها را هم پاک می‌کند. دو سطر رفتند و هیچ خطایی نداد. **خطرناک است و جایش فقط داده‌ای است که بدونِ پدر هیچ معنایی ندارد** — مثلِ برچسب‌های یک کتاب، نه امانت‌های آن.
3. **`SET NULL`** ارجاع را خالی می‌کند و سطر را نگه می‌دارد. سطر می‌ماند ولی حالا `NULL` است و **همان تلهٔ ترمِ ۱ برمی‌گردد**: هر شرطی روی آن ستون این دو سطر را کنار می‌گذارد. برای این کار ستون باید `NULL` بپذیرد، پس `NOT NULL` و `SET NULL` با هم جمع نمی‌شوند.

**و توجه کن که برای دیدنِ هر سهٔ این‌ها باید پرچم روشن باشد.** با پرچمِ خاموش، هر سه جدول یک‌جور رفتار می‌کنند: حذف انجام می‌شود و فرزندها یتیم می‌مانند.

## ۷. با ۵۵ سطرِ یتیم چه کنیم

پیدا کردنشان کارِ آسان بود. تصمیم دربارهٔ آن‌ها کارِ سخت است، و **هیچ جوابِ فنی‌ای ندارد** — سه گزینه هست و انتخابش به معنیِ داده بستگی دارد:

1. **حذفشان کن.** ساده‌ترین راه و پرهزینه‌ترینش: ۵۵ امانتِ واقعاً اتفاق‌افتاده از آمار حذف می‌شوند و هر عددی که تا دیروز گزارش کرده‌ای عوض می‌شود.
2. **پدرِ گمشده را بساز.** یک سطر با نامِ «حذف‌شده» در `books_ref` و `members_ref` بگذار و یتیم‌ها را به آن وصل کن. رویدادها می‌مانند و **گزارش صادقانه می‌گوید که این ۵۵تا کتابِ نامعلوم دارند.**
3. **نگهشان دار و قید را اعمال نکن.** بدترین گزینه است چون تصمیمی نیست، **به تعویق انداختنِ تصمیم است** — و فردا ۵۵ می‌شود ۵۵۰.

**در هر سه حالت، کاری که همین امروز باید بکنی یکی است: قید را اعلام کن و پرچم را روشن کن، تا سطرِ یتیمِ تازه‌ای ساخته نشود.** پاک کردنِ گذشته می‌تواند صبر کند؛ متوقف کردنِ خون‌ریزی نمی‌تواند.

> 🔧 **اگر کار نکرد:** رایج‌ترین خطای این فصل وقتی است که جدولِ مرجع را قبل از فرزندش حذف کنی. با پرچمِ روشن، `SQLite` جلویت را می‌گیرد:

</div>

```python
try:
    legacy.execute("DROP TABLE strict_books")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
legacy.rollback()
```

```text
sqlite3.IntegrityError
FOREIGN KEY constraint failed
```

<div dir="rtl">

**همین دلیل است که در `executescript`های بالا همیشه اول جدولِ فرزند را `DROP` کردیم و بعد پدر را.** ترتیبِ حذف عکسِ ترتیبِ ساخت است.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در `events_ref` یک سطر یک رویداد است، و در خروجیِ `foreign_key_check` یک سطر یعنی **یک نقضِ قید** — نه یک سطرِ خراب، چون یک سطر می‌تواند دو قید را با هم نقض کند. **با کدام شمارشِ دوم سنجیدی؟** با دو مسیرِ کاملاً مستقل: `NOT EXISTS` در بخشِ ۱ که ۳۶ و ۱۹ داد، و `PRAGMA foreign_key_check` در بخشِ ۵ که همان دو عدد را داد. **چه کسی از قلم افتاد؟** ۳۶ امانت که در هر `INNER JOIN`ی به فهرست حذف می‌شوند — و از آن مهم‌تر، **خودِ آن شش کتاب و دو عضو، که دیگر هیچ‌جا نامی ندارند.**

> 🤖 **از دستیارت بپرس:** «چرا `SQLite` اجرای کلیدِ خارجی را پیش‌فرض خاموش نگه داشته؟» بعد این را بپرس: **«اگر `PRAGMA foreign_keys = ON` را وسطِ یک تراکنشِ باز بزنم چه اتفاقی می‌افتد؟»** — جوابِ درست می‌گوید بی‌صدا نادیده گرفته می‌شود. اگر جواب گفت «خطا می‌دهد»، خودت با بخشِ ۳ ردش کن.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| foreign key | فارن کی | کلیدِ خارجی؛ ستونی که به کلیدِ جدولِ دیگری اشاره می‌کند |
| referential integrity | رفرنشیال اینتگریتی | یکپارچگیِ ارجاعی؛ اینکه هر ارجاع واقعاً مقصدی داشته باشد |
| orphan row | ارفن رو | سطرِ یتیم؛ سطری که به چیزی اشاره می‌کند که وجود ندارد |
| ON DELETE CASCADE | آن دیلیت کسکید | با حذفِ پدر، فرزندها هم حذف شوند |
| PRAGMA | پراگما | فرمانِ مخصوصِ `SQLite` برای پرسیدن یا تنظیمِ خودِ موتور |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا نوشتنِ `FOREIGN KEY` در اسکیما به‌تنهایی کافی نیست؟

**تمرینِ اجباری:** گزینهٔ دومِ بخشِ ۷ را پیاده کن. دو سطرِ «حذف‌شده» به `books_ref` و `members_ref` اضافه کن، همهٔ سطرهای یتیمِ `events_ref` را با `UPDATE` به آن‌ها وصل کن، و بعد `PRAGMA foreign_key_check` را دوباره بزن. **سه عدد چاپ کن: تعدادِ نقض‌ها قبل، تعدادِ سطرهایی که به‌روزرسانی شدند، و تعدادِ نقض‌ها بعد.** این سه عدد باید با هم رابطهٔ روشنی داشته باشند — بنویس چه رابطه‌ای.

**تمرینِ ستاره‌دار ⭐:** جدولی بساز که به **خودش** ارجاع بدهد — مثلاً `sections` با ستونِ `parent_id` که به `sections(id)` اشاره می‌کند. چند سطر داخلش بگذار و امتحان کن که آیا می‌توانی سطری وارد کنی که `parent_id`ش به سطری اشاره کند که **هنوز وارد نشده**. بعد سؤالِ سخت را جواب بده: **این ترتیب چه محدودیتی روی بارگذاریِ داده می‌گذارد، و `PRAGMA defer_foreign_keys` چطور حلش می‌کند؟** جوابت باید بگوید قید در چه لحظه‌ای بررسی می‌شود.

## در فصل بعد

حالا جدول‌ها می‌توانند به هم اشاره کنند و پایگاه داده از ارجاع‌ها محافظت می‌کند. ولی فایلِ صادراتیِ ما هنوز یک جدولِ پهنِ واحد است که همه‌چیز را در خودش ریخته.

فصلِ بعد شکستنش را با عدد توجیه می‌کند. **و با بدترین ستونِ آن فایل شروع می‌کند: ستونِ `tags` که چند برچسب را با ویرگول در یک خانه نگه داشته.** پرسشِ «چند کتابِ برچسبِ شعر داریم؟» روی این ستون **۴۱۸** جواب می‌دهد. عددِ درست **۲۱۳** است.

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t4/s03-foreign-keys (یادستان, Yaadestan)
