وقتی روی یک پروژه بزرگ SQL کار میکنید، باید دقت و سازگاری دادهها را در تمام جداولی که کلید خارجی دارند حفظ کنید. کلید خارجی یک ستون یا گروهی از ستونها در یک جدول پایگاه داده رابطهای است که پیوندی بین دادهها در دو جدول برقرار میکند. در این حالت، این همان جایی است که یکپارچگی ارجاعی اهمیت پیدا میکند. برای مثال، میتوانید جدولی با نام employees داشته باشید که ستونی به نام job_title_id دارد که به جدول lookup با نام job_titles ارجاع میدهد.
مثال دیگری را در یک پایگاه داده فروشگاه اینترنتی میتوان دید که ممکن است ستونی به نام category_id در جدول products ایجاد کنید که به جدول parent با نام products_categories لینک دارد.
یکپارچگی ارجاعی تضمین میکند که تمامی مراجع داده معتبر هستند و از ورود دادههای ناسازگار یا سوابق یتیم جلوگیری میکند. یکپارچگی ارجاعی همچنین برای جلوگیری از ورود دادههای نامعتبر در محیطهای چندکاربره مفید است.
در این راهنما، شما کلیدهای خارجی را برای اعمال یکپارچگی ارجاعی در پایگاه دادهتان میآموزید. اگرچه این راهنما روی پایگاه داده MySQL تست شده است، با چند تغییر جزئی در دستورات، میتوان آن را روی سایر پایگاههای داده مبتنی بر SQL نیز اعمال کرد.
برای تکمیل این آموزش، موارد زیر لازم است:
- یک سرور اوبونتو 20.04 با یک کاربر غیر روت و فعال بودن فایروال.
- سرور پایگاه داده MySQL.
در این مرحله، یک پایگاه داده نمونه ایجاد کرده و چند جدول میسازید و مقداری داده نمونه وارد میکنید که در طول راهنما از آنها برای کار با کلیدهای خارجی استفاده خواهید کرد.
ابتدا به سرور به عنوان یک کاربر غیر روت متصل شوید. سپس دستور زیر را برای ورود به سرور MySQL اجرا کنید. به جای example_user، نام دقیق حساب کاربری غیر روت خود را جایگزین کنید.
هنگامی که خواسته شد، رمز عبور حساب کاربری غیر روت MySQL را وارد کنید و ENTER بزنید. سپس فرمان زیر را برای ایجاد پایگاه داده نمونه company_db صادر کنید:
خروجی زیر را تأیید کنید تا مطمئن شوید پایگاه داده بدون خطا ایجاد شده است.
زمانی که پایگاه داده با موفقیت ساخته شد، با دستور USE به پایگاه داده company_db بروید:
باید پیام تأیید زیر را ببینید که نشان میدهد به درستی به company_db منتقل شدهاید:
اکنون جدول job_titles را با استفاده از دستور CREATE TABLE بسازید. این جدول به عنوان جدول lookup برای تمامی عناوین شغلی موجود در پایگاه داده شما عمل میکند. ستون job_title_id به عنوان کلید اصلی است که هر عنوان شغلی را به صورت یکتا شناسایی میکند و از نوع BIGINT است که تا ۲^۶۳-۱ رکورد را پشتیبانی میکند. از کلید AUTO_INCREMENT استفاده شده است تا MySQL به صورت خودکار برای هر عنوان شغلی جدید، یک عدد ترتیبی اختصاص دهد.
در دستور CREATE TABLE، ستونی به نام job_title_name وجود دارد که مقدار قابل خواندن برای عنوان شغل را ذخیره میکند. این ستون از نوع VARCHAR(50) است که رشتههایی تا طول حداکثر ۵۰ کاراکتر را ذخیره میکند.
همچنین دستور شامل ENGINE = InnoDB است تا از موتور ذخیرهسازی InnoDB استفاده شود که برای تراکنشها مناسب، با قابلیت اطمینان و عملکرد بالا است.
دستور زیر را برای ایجاد جدول job_titles اجرا کنید:
بعد از اجرای دستور CREATE TABLE job_titles… مطمئن شوید بدون خطا پیام تأیید دریافت کردهاید.
اکنون یک جدول lookup برای تمامی عناوین شغلی معتبر ساختهاید. حالا چند عنوان شغلی نمونه وارد جدول job_titles کنید:
بعد از هر دستور، باید پیام تأیید دریافت کنید.
پس از وارد کردن عناوین شغلی، با استفاده از دستور SELECT دادههای جدول job_titles را مشاهده کنید تا صحت آنها را تأیید کنید:
باید لیست تمامی عناوین شغلی مانند زیر نمایش داده شود:
سپس جدول employees را بسازید. این جدول شامل اطلاعات تمام کارکنان شرکت است. ستون job_title_id در جدول employees به همان ستون در جدول job_titles اشاره دارد. این ارتباط با دستور FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id) برقرار شده است. برای حفظ سازگاری، نوع داده BIGINT که در ستون مرتبط استفاده کردید را اینجا هم به کار ببرید.
در جدول employees، ستون employees_id کلید اصلی است و با استفاده از AUTO_INCREMENT به صورت خودکار مقدار آن افزوده میشود.
نامهای کارکنان در ستونهای first_name و last_name با نوع داده VARCHAR(50) ذخیره میشوند. همین نوع داده برای شماره تلفن (phone) هم مناسب است.
برای بهبود سرعت بازیابی اطلاعات بین دو جدول مرتبط، ستون job_title_id به وسیله INDEX ایندکس شده است. همچنین اطمینان حاصل کنید که موتور ذخیرهسازی InnoDB در دستور CREATE TABLE مشخص شده باشد.
دستور زیر را برای ساخت جدول employees اجرا کنید:
پس از اجرای دستور، پیام موفقیتآمیز بودن را مشاهده کنید.
حالا که پایگاه داده و جداول نمونه را ساختید، بررسی میکنید هنگام ورود دادهها چه اتفاقی میافتد.
در این مرحله، چند رکورد یتیم در جدول employees وارد کنید. رکورد یتیم به رکوردهایی گفته میشود که دارای job_title_id نامعتبر هستند. مطابق جدول job_titles، فقط ۳ job_title_id معتبر دارید.
اکنون تلاش کنید دادههایی با job_title_id نامعتبر وارد جدول employees کنید با اجرای دستورات INSERT زیر:
این دستورات باید با خطا مواجه شوند و شکست بخورند، زیرا مقادیر ۴، ۱۵ و ۷ به عنوان job_title_id نامعتبر هستند.
در مرحله بعد، دادههای معتبر را وارد جدول employees کنید و بررسی کنید آیا عملیات موفق میشود یا خیر.
با توجه به اینکه job_title_id های معتبر را وارد میکنید، دستورات INSERT اجرا شده و موفق خواهند بود. خروجی حاصله پس از هر دستور به صورت زیر خواهد بود:
مشاهده کردید که چگونه یکپارچگی ارجاعی مانع ورود دادههای نامعتبر به جداول مرتبط میشود. به عبارت دیگر، استفاده از کلیدهای خارجی، پایگاه داده شما را در حالت سازگار نگه میدارد حتی بدون نیاز به کدنویسی این منطق در برنامههای مشتری.
با کلیدهای خارجی، پایگاه دادهای بهینهتر میسازید که به شما امکان جستجوی کارآمد دادههای مرتبط را میدهد. برای مثال، برای دریافت همه سوابق کارکنان همراه با نام عنوان شغل، دستور JOIN زیر را روی جداول employees و job_titles اجرا کنید:
در خروجی مشاهده میکنید اطلاعات کارکنان به همراه عنوان شغل آنها نمایش داده میشود.
بنابراین، چند نمونه BRANCH MANAGER، چندین CLERK و یک LEVEL 1 SUPERVISOR دارید.
علاوه بر این، کلیدهای خارجی از حذف تصادفی رکوردهای والد که در جدول فرزند لینک شده به آنها ارجاع شده باشد جلوگیری میکنند. چند مثال واقعی که میتوان استفاده کرد:
- در فروشگاه اینترنتی، میتوان از حذف تصادفی اطلاعات مشتری از جدول customers جلوگیری کرد زمانی که سفارشهای فعالی برای آن مشتری در جدول sales وجود دارد.
- در سیستم کتابخانه، نمیتوان دانشآموزی را از جدول registers حذف کرد که رکوردهای مرتبط در جدول issued_books دارد.
- در بانک، از حذف رکورد در جدول savings_accounts پیشگیری میشود وقتی مشتری تراکنشهایی در جدول savings_accounts_transactions انجام داده باشد.
حال میتوانید سعی کنید دادهای را از جدول حذف کنید. در ترمینال، یک عنوان شغلی را از جدول job_titles حذف کنید:
از آنجا که یک رکورد در employees با عنوان BRANCH MANAGER وجود دارد، دستور DELETE شکست میخورد و خطایی مشابه زیر نمایش داده میشود:
دوباره یک عنوان شغلی جدید به جدول job_titles اضافه کنید:
پس از اجرای دستور، پیام موفقیت آمیز دریافت میکنید.
دوباره جدول job_titles را برای بررسی job_title_id نقش جدید کوئری کنید:
حالا باید ۴ عنوان شغلی داشته باشید. عنوان CEO به job_title_id برابر با ۴ دارد:
اکنون بدون اضافه کردن رکورد مرتبط به جدول employees، عنوان شغلی با job_title_id برابر ۴ را حذف کنید. این بار دستور حذف موفق خواهد بود.
پس از تکمیل این آزمایشها بدون خطا، مشخص است که کلیدهای خارجی به درستی کار میکنند.
در این آموزش، پایگاه دادهای نمونه با جداول مرتبط ایجاد کردید و استفاده از یکپارچگی ارجاعی در سیستم مدیریت پایگاه داده رابطهای را تمرین کردید. دیدید که کلیدهای خارجی اهمیت زیادی در اعتبارسنجی دادهها و جلوگیری از حذف دادههای بحرانی دارند که در غیر این صورت پایگاه داده را به حالت ناسازگار میبرند. از این دانش در پروژههای بعدی پایگاه داده خود استفاده کنید و مزایای کلیدهای خارجی را بهرهمند شوید.
از همراهی شما با پارمین کلود سپاسگزاریم.
نظرات کاربران