همه آنچه که به هنگام بروز مشکل با کلیدهای خارجی در MySql باید بدانید 1390/07/22

آرش میلانی

یک روز کاری پر انرژی رو شروع می کنی و می خواهی یک ارتباط ساده بین دو جدول در پایگاه داده MySQL ایجاد کنی؛ اصلا فکرش رو هم نمی کردی که دستور ساده زیر پیام خطایی رو بده که کل روزت رو صرف debug اون بکنی:
ALTER TABLE `BlogPosts`
ADD CONSTRAINT `FK_myKey` FOREIGN KEY (`BlogID`)
REFERENCES `Blogs` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
دستور رو اجرا می کنی و دااانگ... با پیام خطای کاملا بی‌ربط زیر روبرو می شی:
MySQL Error Number 1005 Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)
خوب این فایل sql-328_45.frm چه ربطی به کلید خارجی ما داره؟ آیا مشکلی در ایجاد اون به علت سطوح دسترسی سیستم عامل به وجود آمده؟
نه! این طور نیست. جواب معما را در جایی دیگر و دقیقا در جدول ها و فیلد هایی که در دستور بالا استفاده کردیم هست اش. یکی از حالت های زیر یا ترکیبی از آنها می‌تواند عامل این اتفاق باشد.

  • نوع و اندازه هر دو فیلد که می‌خواهید با هم رابطه داشته باشند باید کاملا یکی باشد. برای مثال اگر یکی از آنها INT(11) و دیگری INT(20) باشد، امکان ایجاد این رابطه نیست. برای مشاهده اطلاعات دقیق هر یک از جداول می‌توانید از دستور SHOW CREATE TABLE استفاده کنید. گاهی نمی‌توان به ابزارهای گرافیکی همچون MySQL Workbench اعتماد کرد.
  • تنها نوع و اندازه‌ی فیلد ها کافی نیست! بررسی کنید که آیا فیلدهای شما از لحاظ Unsigned بودن یکی هستند یا نه. اگر نه که لطف کنید و با تغییر آنها به مقداری یکسان 6 ساعت در زمان خود صرفه جویی کنید.
  • کلید خارجی نباید مقدار پیش‌فرض یا default داشته باشد.
  • یکی از فیلدهایی که می خواهید با هم رابطه داشته باشند بایستی ایندکس یا کلید اصلی (Primary Key) باشند.
  • نام قید یا CONSTRAINT ای که استفاده کرده اید تکراری است و یا بیشتر از 64 کارکتر است (در دستور بالا `FK_myKey`).
  • ممکن است دستور شما شامل ON DELETE SET NULL باشد ولی کلید خارجی شما دارای خاصیت NotNull باشد. در این صورت یا خاصیت این فیلد را تغییر دهید و یا اینکه دستور خود را به ON DELETE NO ACTION تغییر دهید.
  • از همسان بودن Charset و Collate هم در سطح جدول‌ها و هم در سطح فیلد‌ها اطمینان حاصل کنید.
  • و به احتمال بسیار کم دستور شما داری syntax error یا اشکال تایپی است. نام فیلدها و جدول‌ها را دوباره بررسی کنید.
  • در صورتی که کد شما برای تولید کلید خارجی در جدولی بدون دادن خطا اجرا می‌شود ولی در واقع این کلید ایجاد نمی‌شود، احتمالا جدول ‌های شما MyISAM هستند که رابطه بین جداول را پشتیبانی نمی‌کند. نوع جدول‌های خود را به InnoDB تغییر دهید و مشکل حل خواهد شد.

اگر هیچ یک از موارد بالا نتوانست مشکل شما رو حل بکند می‌توانید برای مشاهده آخرین error مربوط به کلیدهای دستور زیر زا در MySql اجرا کنید:
SHOW ENGINE INNODB STATUS
و سپس در خروجی این دستور به دنبال بخش LATEST FOREIGN KEY ERROR بگردید. این بخش می تواند اطلاعات کافی ای در اختیار شما قرار دهد تا بتوانید به راحتی مشکل مربوط به کلید های خارجی و ارتباط جدول ها را حل کنید.