در این فصل چه یاد میگیری#
فصلِ قبل با یک هشدار تمام شد: یکتاییِ مشاهدهشده یکتاییِ تضمینشده نیست. این فصل تفاوتش را با عدد نشان میدهد.
در فهرستِ ۱٬۲۰۰ سطریِ ما، «عنوان و نویسنده» ۴۵ بار تکرار میشود و اگر شعبه را هم اضافه کنی باز ۶ بار تکرار میشود. یعنی هیچ ترکیبی از ستونهای معنادارِ این جدول، سطرها را از هم جدا نمیکند. در جدولِ اعضا هم همین است: ۳۰۰ عضو و ۲۸۸ شمارهٔ تلفن.
و بعد یک رفتار که خطرناکتر از هر دوی اینهاست و در SQLite پیشفرض است: شمارهٔ بزرگترین سطر، اگر حذف شود، به سطرِ بعدی داده میشود. شمارهٔ ۳ که مالِ کتابِ «پ» بود، بعد از حذفش مالِ کتابِ «ت» میشود — با همان عدد، در همان ستون، و بدونِ هیچ خطایی.

آخر این فصل میتوانی:
- بگویی کلیدِ اصلی چه چیزی را تضمین میکند و چه چیزی را نه
rowidرا ببینی و بگویی کِی همان کلیدِ توست و کِی نیست- کلیدِ طبیعی و کلیدِ جانشین را با هزینههایشان از هم جدا کنی
- ثابت کنی یک ترکیبِ ستونی که بهنظر یکتاست، در دادهٔ واقعی نیست
قبل از شروع#
از فصلِ ۱: دانهبندی، و اینکه دانهبندیِ loan_events «یک رویداد» است. از ترمِ ۱ فصلِ ۷: CREATE TABLE و PRIMARY KEY بهشکلِ کوتاه — بدهیاش همینجا پرداخت میشود. از ترمِ ۱ فصلِ ۶: NULL یعنی «نمیدانیم»، که در بخشِ ۶ دوباره سراغمان میآید.
تعریف، در یک جمله: کلید مجموعهای از ستونهاست که پایگاه داده تضمین میکند در هیچ دو سطری تکرار نمیشود. کلمهٔ مهم «تضمین میکند» است، نه «تکرار نمیشود».
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
catalog_raw |
۱٬۲۰۰ | کمتر از یک ثانیه |
members_raw |
۳۰۰ | آنی |
loan_events |
۲۶٬۱۱۹ | حدودِ یک ثانیه |
۱. کوچکترین کلیدِ ممکن#
دو کتاب و یک ستونِ شناسه. PRIMARY KEY به SQLite میگوید این ستون نباید تکرار شود.
con.execute("DROP TABLE IF EXISTS tiny_catalog")
con.execute("""
CREATE TABLE tiny_catalog (
isbn TEXT PRIMARY KEY,
title TEXT NOT NULL
)
""")
con.executemany("INSERT INTO tiny_catalog VALUES (?, ?)",
[("978-1", "الف"), ("978-2", "ب")])
con.commit()
print(q("SELECT * FROM tiny_catalog"))
print()
try:
con.execute("INSERT INTO tiny_catalog VALUES (?, ?)", ("978-1", "پ"))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
isbn title
0 978-1 الف
1 978-2 ب
sqlite3.IntegrityError
UNIQUE constraint failed: tiny_catalog.isbn
کتابِ سوم وارد نشد و پیامِ خطا دقیقاً میگوید کدام جدول و کدام ستون.
این همان چیزی است که قاعدهٔ سختِ این ترم میگوید: قانون در اسکیما نوشته شد، نه در کد. هیچجای برنامهٔ ما if نداشت که بپرسد این شناسه قبلاً هست یا نه؛ پایگاه داده خودش جلویش را گرفت، و هر برنامهای که به این جدول وصل شود همین جلوگیری را میگیرد — حتی برنامهای که سالِ بعد کسِ دیگری مینویسد.
۲. rowid: ستونی که همیشه هست#
در SQLite هر جدولِ معمولی یک ستونِ پنهان دارد به نامِ rowid که خودِ موتور پرش میکند. حتی اگر هیچ کلیدی اعلام نکرده باشی.
con.execute("DROP TABLE IF EXISTS tiny_notes")
con.execute("CREATE TABLE tiny_notes (body TEXT)")
con.executemany("INSERT INTO tiny_notes VALUES (?)", [("یک",), ("دو",)])
con.commit()
print(q("SELECT rowid, body FROM tiny_notes"))
print()
print("و آنچه PRAGMA اعلام میکند:")
print(q("PRAGMA table_info(tiny_notes)"))
print()
print("در catalog_raw، id و rowid یکیاند؟")
print(q("SELECT id, rowid AS row_id, title FROM catalog_raw ORDER BY id LIMIT 3"))
rowid body
0 1 یک
1 2 دو
و آنچه PRAGMA اعلام میکند:
cid name type notnull dflt_value pk
0 0 body TEXT 0 None 0
در catalog_raw، id و rowid یکیاند؟
id row_id title
0 1 1 روزهای پل
1 2 2 مه در باران
2 3 3 روزهای خاک
ستونِ rowid در PRAGMA table_info اصلاً دیده نمیشود، ولی وجود دارد و میشود ازش پرسید.
و در catalog_raw عددهای دو ستون یکیاند. این تصادف نیست: وقتی ستونی را INTEGER PRIMARY KEY اعلام میکنی، SQLite آن را نامِ دیگری برای همان rowid میگیرد و ستونِ جداگانهای نمیسازد. به همین دلیل است که در ترمِ ۱ وقتی id را ندادیم، خودش عدد گذاشت.
اگر ستون را INTEGER ننویسی این اتفاق نمیافتد. یک TEXT PRIMARY KEY یا حتی یک INT PRIMARY KEY ستونِ معمولی است و rowid جدا از آن به کارش ادامه میدهد. تفاوت را در بخشِ ۶ میبینی که چقدر گران است.
۳. شمارهای که برمیگردد#
حالا رفتاری که هیچکس انتظارش را ندارد و در SQLite پیشفرض است.
con.execute("DROP TABLE IF EXISTS reused")
con.execute("DROP TABLE IF EXISTS never_reused")
con.execute("CREATE TABLE reused (id INTEGER PRIMARY KEY, label TEXT)")
con.execute("""
CREATE TABLE never_reused (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT)
""")
con.executemany("INSERT INTO reused (label) VALUES (?)", [("الف",), ("ب",), ("پ",)])
con.executemany("INSERT INTO never_reused (label) VALUES (?)", [("الف",), ("ب",), ("پ",)])
print("قبل از حذف:", con.execute("SELECT id, label FROM reused").fetchall())
# DELETE FROM سطرهای برآوردهکنندهٔ شرط را برمیدارد؛ فعلاً همینقدر بدان، ت۵ف۵ کاملش میکند
con.execute("DELETE FROM reused WHERE label = ?", ("پ",))
con.execute("DELETE FROM never_reused WHERE label = ?", ("پ",))
con.execute("INSERT INTO reused (label) VALUES (?)", ("ت",))
con.execute("INSERT INTO never_reused (label) VALUES (?)", ("ت",))
con.commit()
print("بدون AUTOINCREMENT:", con.execute("SELECT id, label FROM reused").fetchall())
print("با AUTOINCREMENT :", con.execute("SELECT id, label FROM never_reused").fetchall())
قبل از حذف: [(1, 'الف'), (2, 'ب'), (3, 'پ')]
بدون AUTOINCREMENT: [(1, 'الف'), (2, 'ب'), (3, 'ت')]
با AUTOINCREMENT : [(1, 'الف'), (2, 'ب'), (4, 'ت')]
شمارهٔ ۳ که مالِ «پ» بود، حالا مالِ «ت» است.
تصور کن آن شماره روی رسیدِ امانتِ یک عضو چاپ شده باشد، یا در گزارشی که ماهِ پیش فرستادی نوشته شده باشد. آن رسید هنوز عددِ ۳ را دارد و حالا به کتابِ دیگری اشاره میکند. هیچ خطایی، هیچ هشداری — چون از نظرِ پایگاه داده هیچ اتفاقِ بدی نیفتاده: شمارهٔ ۳ خالی بود و به سطرِ تازه داده شد. و یک دقت: SQLite برای سطرِ تازه یکی بیشتر از بزرگترین شمارهٔ موجود را برمیدارد. پس اگر بهجای «پ» سطرِ اول را حذف کنی، شمارهٔ ۱ خالی میماند و سطرِ تازه ۴ میگیرد. تله دقیقاً وقتی کار میکند که بزرگترین شماره حذف شده باشد.
کلمهٔ AUTOINCREMENT جلویش را میگیرد: SQLite بزرگترین شمارهٔ استفادهشده را در یک جدولِ داخلی نگه میدارد و هرگز عقب نمیرود. هزینهاش یک جدولِ داخلیِ اضافه و کمی کندیِ نوشتن است، و در برابرِ شناسهای که بیرون از پایگاه داده رفته، معاملهٔ خوبی است.
✅ چک کن: در جدولِ اول باید سه سطر با شمارههای ۱ و ۲ و ۳ ببینی، و در دومی سه سطر با شمارههای ۱ و ۲ و ۴. اگر هر دو یکی درآمدند،
AUTOINCREMENTرا جا انداختهای — و توجه کن کهSQLiteبدونِINTEGER PRIMARY KEYاین کلمه را اصلاً قبول نمیکند.
۴. کلیدِ طبیعی: چیزی که در دنیای بیرون یکتاست#
کلیدِ طبیعی ستونی است که در دنیای واقعی معنا دارد و ادعا میشود یکتاست: شابک، کدِ ملی، شمارهٔ دانشجویی. جذابیتش این است که چیزِ اضافهای اختراع نمیکنی.
بیایید روی فهرستِ خودمان امتحانش کنیم.
rows = int(q("SELECT COUNT(*) AS n FROM catalog_raw").iloc[0, 0])
print("سطرهای فهرست :", rows)
for columns in ("title", "title, author", "title, author, branch"):
# نام ستون به متن پرسش چسبیده، نه مقدار — همان مرزی که ترم ۱ فصل ۷ کشید.
n = int(q(f"SELECT COUNT(*) AS n FROM (SELECT DISTINCT {columns} FROM catalog_raw)").iloc[0, 0])
print(f" {columns:<22} {n:>5} ترکیب یکتا | {rows - n:>3} سطر تکراری")
print()
print(q("""
SELECT title, author, branch, COUNT(*) AS rows
FROM catalog_raw
GROUP BY title, author, branch
HAVING rows > 1
ORDER BY title
"""))
سطرهای فهرست : 1200
title 395 ترکیب یکتا | 805 سطر تکراری
title, author 1155 ترکیب یکتا | 45 سطر تکراری
title, author, branch 1194 ترکیب یکتا | 6 سطر تکراری
title author branch rows
0 آتش در باران شیرین سلیمی نسیم 2
1 بازگشت به آتش بابک بیات پویا 2
2 بازگشت به کوچه زهرا دهقان بهار 2
3 خاک در باران فرهاد دهقان دانش 2
4 در جستوجوی رود مریم فرهنگی بهار 2
5 روزهای سنگ مهدی کاشانی نسیم 2
هیچکدام کلید نیستند، و شش سطرِ آخر با چشم قابلِ بررسیاند.
آن شش جفت چند معنیِ متفاوت میتوانند داشته باشند: کتابخانه واقعاً دو نسخه از همان کتاب در همان شعبه دارد، یک نفر یک کتاب را دو بار ثبت کرده، یا اینها دو کتابِ متفاوت با یک ناماند. این سه ستون بینشان تفاوتی نمیگذارند. ستونهای دیگر سرنخ میدهند، چون در هر شش جفت سالِ چاپ و دسته فرق دارد، ولی حکمِ قطعی نمیدهند — و این دقیقاً همان چیزی است که یک کلید، اگر از اول اعلام شده بود، مجبورت میکرد تصمیم بگیری.
همان کار روی جدولِ اعضا، این بار روی ستونی که همه فکر میکنند کلید است:
print("اعضا:", int(q("SELECT COUNT(*) AS n FROM members_raw").iloc[0, 0]),
"| شمارههای متمایز:",
int(q("SELECT COUNT(DISTINCT phone) AS n FROM members_raw").iloc[0, 0]))
print()
print(q("""
SELECT phone, COUNT(*) AS members
FROM members_raw
GROUP BY phone
HAVING members > 1
ORDER BY members DESC, phone
"""))
اعضا: 300 | شمارههای متمایز: 288
phone members
0 09126600005 4
1 09127567899 4
2 09122394098 3
3 09127012431 3
4 09127435833 3
پنج شماره، بینِ ۱۷ نفر. توضیحش هم پیچیده نیست: خانوادهها یک شماره میدهند.
درسِ عملی: کلیدِ طبیعی وقتی جواب میدهد که یک نهادِ بیرونی یکتاییِ آن را تضمین کند و تو بتوانی به آن تضمین تکیه کنی. شمارهٔ تلفن چنین تضمینی ندارد. نامِ کتاب هم ندارد. حتی شابک — که ظاهراً استاندارد است — برای چاپهای مختلف عوض میشود و در کتابهای قدیمی اصلاً وجود ندارد.
۵. کلیدِ جانشین: عددی که هیچ معنایی ندارد#
کلیدِ جانشین یک عددِ بیمعنا است که فقط برای شناسایی ساخته میشود — همان idی که در همهٔ جدولهای این دوره دیدهای. سه چیز را میخری: هرگز تکرار نمیشود، هرگز عوض نمیشود، و به هیچ واقعیتی در دنیای بیرون بند نیست.
آن جملهٔ سوم مهمترینش است. اگر کلیدت شمارهٔ تلفن باشد و کسی شمارهاش را عوض کند، باید همان مقدار را در هر جدولی که به آن اشاره کرده هم عوض کنی — و اولین جایی که یادت برود، ارجاع میشکند. یک عددِ بیمعنا هیچوقت عوض نمیشود چون هیچوقت غلط نمیشود.
ولی کلیدِ جانشین یک چیز را نمیخرد و اینجا اکثرِ آدمها اشتباه میکنند: جلوی سطرِ تکراری را نمیگیرد. دو ثبت از یک کتاب، دو id متفاوت میگیرند و پایگاه داده کاملاً راضی است. پس کلیدِ جانشین جایگزینِ کلیدِ طبیعی نیست؛ کنارش مینشیند — شناسه برای ارجاع، و یک UNIQUE روی ستونهای معنادار برای جلوگیری از تکرار.
con.execute("DROP TABLE IF EXISTS checkouts")
con.execute("""
CREATE TABLE checkouts (
id INTEGER PRIMARY KEY,
catalog_id INTEGER NOT NULL,
member_id INTEGER NOT NULL,
happened_at TEXT NOT NULL,
UNIQUE (catalog_id, member_id, happened_at)
)
""")
con.execute("""
INSERT INTO checkouts (catalog_id, member_id, happened_at)
SELECT catalog_id, member_id, happened_at FROM loan_events WHERE kind = ?
""", ("out",))
con.commit()
print("امانتهای منتقلشده:", int(q("SELECT COUNT(*) AS n FROM checkouts").iloc[0, 0]))
print()
try:
con.execute("""
INSERT INTO checkouts (catalog_id, member_id, happened_at)
SELECT catalog_id, member_id, happened_at FROM checkouts WHERE id = ?
""", (1,))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
امانتهای منتقلشده: 13458
sqlite3.IntegrityError
UNIQUE constraint failed: checkouts.catalog_id, checkouts.member_id, checkouts.happened_at
۱۳٬۴۵۸ سطر بدونِ هیچ اعتراضی وارد شدند — یعنی همان یکتاییِ سهستونیِ فصلِ ۱ واقعاً برقرار بود. و بلافاصله بعدش، تلاش برای وارد کردنِ دوبارهٔ یک سطر رد شد.
اسمِ UNIQUE (catalog_id, member_id, happened_at) کلیدِ مرکب است: یکتایی روی ترکیبِ چند ستون، نه روی هرکدامشان جدا. هر سه ستون بهتنهایی پر از تکرارند و این دقیقاً درست است.
و حالا فرقِ فصلِ ۱ با اینجا را ببین. آنجا شمردیم و دیدیم این سه ستون در این داده یکتا هستند. اینجا اعلامش کردیم، و از این لحظه به بعد پایگاه داده نمیگذارد خلافش وارد شود. جملهٔ اول یک مشاهده بود، جملهٔ دوم یک تضمین است.
۶. SQLite دو استثنا دارد که هر دو گراناند#
con.execute("DROP TABLE IF EXISTS soft_key")
con.execute("CREATE TABLE soft_key (code TEXT PRIMARY KEY, label TEXT)")
con.executemany("INSERT INTO soft_key VALUES (?, ?)", [(None, "الف"), (None, "ب")])
con.commit()
print("سطرهای جدولی که کلید اصلیاش TEXT است:",
int(q("SELECT COUNT(*) AS n FROM soft_key").iloc[0, 0]))
print(q("SELECT rowid, code, label FROM soft_key"))
سطرهای جدولی که کلید اصلیاش TEXT است: 2
rowid code label
0 1 None الف
1 2 None ب
دو سطر، هر دو با کلیدِ اصلیِ خالی، و هیچ خطایی.
این باگی قدیمی است که مستنداتِ خودِ SQLite هم میگویدش و عمداً نگهش داشتهاند: نسخههای اولیه این را اجازه میدادند و عوض کردنش برنامههای موجود را میشکست. نتیجهاش برای تو این است: یک PRIMARY KEY که INTEGER نیست، بهتنهایی خالی نبودن را تضمین نمیکند — و اگر رویش حساب کرده باشی، جدولت میتواند دو سطرِ بیشناسه داشته باشد. راهِحل یک کلمه است: NOT NULL را هم صریح بنویس. فصلِ ۶ همین را جدی میگیرد.
استثنای دوم همان مکانیزم است در جای دیگر: در یک ستونِ UNIQUE، چند NULL مجازند.
con.execute("DROP TABLE IF EXISTS members_keyed")
con.execute("""
CREATE TABLE members_keyed (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
national_id TEXT UNIQUE
)
""")
con.execute("""
INSERT INTO members_keyed (id, name, national_id)
SELECT id, name, national_id FROM members_raw
""")
con.commit()
print("اعضای وارد شده :", int(q("SELECT COUNT(*) AS n FROM members_keyed").iloc[0, 0]))
print("از اینها بدون کد ملی :",
int(q("SELECT COUNT(*) AS n FROM members_keyed WHERE national_id IS NULL").iloc[0, 0]))
print()
try:
con.execute("INSERT INTO members_keyed (id, name, national_id) VALUES (?, ?, ?)",
(901, "عضو آزمایشی", None))
con.execute("INSERT INTO members_keyed (id, name, national_id) VALUES (?, ?, ?)",
(902, "عضو آزمایشی", None))
print("دو عضو تازه با کد ملیِ خالی هم وارد شدند — بدون هیچ اعتراضی.")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
اعضای وارد شده : 300
از اینها بدون کد ملی : 33
دو عضو تازه با کد ملیِ خالی هم وارد شدند — بدون هیچ اعتراضی.
سیصد عضو با ۳۳ کد ملیِ خالی وارد شدند، و دوتای دیگر هم بعدش.
منطقش همان منطقِ سهارزشیِ ترمِ ۱ است: UNIQUE میگوید هیچ دو مقداری برابر نباشند، و NULL = NULL هرگز درست نمیشود. پس دو NULL هیچوقت «برابر» تشخیص داده نمیشوند و هر دو میمانند.
پیامدِ عملیاش را دستِکم نگیر: یک UNIQUE روی ستونی که NULL میپذیرد، برای سطرهای خالی هیچ محافظتی ندارد. اگر میخواهی هر عضو دقیقاً یک کد ملی داشته باشد، UNIQUE تنها نصفِ کار است؛ نصفِ دیگرش NOT NULL است.
🔧 اگر کار نکرد: اگر همان کد ملی را دو بار بدهی، این بار واقعاً خطا میگیری — و پیام دقیقاً میگوید کدام ستون:
existing = q("SELECT national_id FROM members_keyed WHERE national_id IS NOT NULL LIMIT 1")
try:
con.execute("INSERT INTO members_keyed (id, name, national_id) VALUES (?, ?, ?)",
(903, "عضو آزمایشی", existing.iloc[0, 0]))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
sqlite3.IntegrityError
UNIQUE constraint failed: members_keyed.national_id
خطای واقعیِ این فصل ولی همان بخشِ ۳ بود که هیچ پیامی نداشت — شمارهٔ ۳ که بیصدا به کتابِ دیگری منتقل شد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در
catalog_rawیک سطر یک ثبت در فهرست است، نه یک کتاب — و همین است که میگذارد شش جفتِ همسان کنارِ هم بنشینند. با کدام شمارشِ دوم سنجیدی؟ با سهتا: تعدادِ ترکیبهای یکتا در برابرِ ۱٬۲۰۰ برای سه مجموعهٔ ستون، جدولِ شش سطریِ تکراریها که با چشم شمردنی است، و ۱۳٬۴۵۸ سطری که باUNIQUEسهستونی بدونِ اعتراض وارد شدند. چه کسی از قلم افتاد؟ ۳۳ عضوی که کد ملی ندارند و هر قیدِUNIQUEی رویشان بیاثر است — و همچنین آن ۴۷ عضوی که از فصلِ ۱ میدانیم در دفترِ رویداد نیستند.
🤖 از دستیارت بپرس: «تفاوتِ کلیدِ طبیعی و کلیدِ جانشین چیست و هرکدام چه هزینهای دارند؟» بعد این را بپرس: «اگر جدولم کلیدِ جانشین دارد، آیا هنوز به یک
UNIQUEروی ستونهای معنادار نیاز دارم؟» — جوابِ درست بله است و دلیلش این است که کلیدِ جانشین فقط سطرها را از هم جدا میکند، جلوی ثبتِ دوبارهٔ یک چیز را نمیگیرد. اگر جواب گفت «نه،idکافی است»، با جدولِ بخشِ ۴ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| primary key | پرایمری کی | کلیدِ اصلی؛ ستون یا ستونهایی که یک سطر را یکتا میکنند |
| rowid | روآیدی | شمارهٔ داخلی و پنهانی که SQLite به هر سطر میدهد |
| natural key | نچرال کی | کلیدی که در دنیای بیرون هم معنا دارد |
| surrogate key | سروگیت کی | کلیدِ جانشین؛ شمارهای که فقط برای شناسایی ساخته شده |
| composite key | کامپوزیت کی | کلیدِ مرکب؛ یکتایی روی ترکیبِ چند ستون |
| AUTOINCREMENT | اتواینکریمنت | تضمینِ اینکه شمارهٔ حذفشده دوباره استفاده نشود |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
حالا هر جدولی میتواند بگوید کدام سطر کدام است. ولی فصلِ ۱ یک عددِ حلنشده روی دستمان گذاشت: ۳۶ رویداد به کتابی اشاره میکنند که در فهرست نیست، و ۱۹ رویداد به عضوی که وجود ندارد.
فصلِ بعد نشان میدهد چرا هیچکس جلویشان را نگرفت. سرنخش یک خط است که در سلولِ راهاندازیِ همین دوره — از فصلِ اولِ ترمِ ۱ — نشسته و تا امروز به آن دقت نکردهای. و بعد همان سطرِ یتیم را دو بار وارد میکنیم: یک بار پذیرفته میشود و یک بار با پیامِ FOREIGN KEY constraint failed رد.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.