• پایگاه داده. پایگاه داده های رابطه ای یکپارچگی داده های کلید خارجی فیلد کلیدی

    آخرین به روز رسانی: 2019/04/27

    کلیدهای خارجی به شما این امکان را می دهند که بین جداول ارتباط برقرار کنید. یک کلید خارجی روی ستون‌های یک جدول وابسته و فرعی تنظیم می‌شود و به یکی از ستون‌های جدول اصلی اشاره می‌کند. به طور معمول، یک کلید خارجی به یک کلید اصلی از یک جدول اصلی مرتبط اشاره می کند.

    نحو کلی برای تنظیم یک کلید خارجی در سطح جدول به شرح زیر است:

    کلید خارجی (ستون 1، ستون 2، ... ستونN) منابع جدول اصلی (جدول_اصلی_ستون1، جدول_اصلی_ستون2، ... جدول_اصلی_ستون N)

    برای ایجاد یک محدودیت کلید خارجی، پس از کلید خارجی، ستون جدولی را مشخص می کنید که کلید خارجی را نشان می دهد. و بعد از کلیدواژه REFERENCES نام جدول مربوطه و سپس در داخل پرانتز نام ستون مربوطه که کلید خارجی به آن اشاره خواهد کرد مشخص می شود. بعد از عبارت REFERENCES عبارات ON DELETE و ON UPDATE قرار دارند که به ترتیب عملکرد هنگام حذف و به روز رسانی یک ردیف از جدول اصلی را مشخص می کنند.

    به عنوان مثال، بیایید دو جدول تعریف کنیم و با استفاده از یک کلید خارجی آنها را پیوند دهیم:

    مشتریان جدول ایجاد کنید (ID INT PRIMARY KEY AUTO_INCREMENT، سن INT، نام VARCHAR(20) NOT NULL، LastName VARCHAR(20) NOT NULL، تلفن VARCHAR(20) NOT NULL UNIQUE). CREATE TABLE Orders (ID INT PRIMARY KEY AUTO_INCREMENT، CustomerId INT، CreatedAt Date، FOREIGN KEY (CustomerId) References Customers (Id));

    در این حالت جداول مشتریان و سفارشات تعریف می شوند. مشتریان اصلی ترین هستند و نماینده مشتری هستند. سفارشات وابسته است و نشان دهنده سفارش ارسال شده توسط مشتری است. جدول Orders از طریق ستون CustomerId به جدول Customers و ستون Id آن مرتبط می شود. یعنی ستون CustomerId یک کلید خارجی است که از جدول Customers به ​​ستون Id اشاره می کند.

    می توانید از عملگر CONSTRAINT برای تعیین نام برای یک محدودیت کلید خارجی استفاده کنید:

    CREATE TABLE Orders (ID INT PRIMARY KEY AUTO_INCREMENT، CustomerId INT، CreatedAt Date، CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) References Customers (Id));

    در حذف و در به روز رسانی

    با استفاده از دستورات ON DELETE و ON UPDATE، می توانید اقداماتی را که به ترتیب هنگام حذف یا اصلاح یک ردیف مرتبط از جدول اصلی انجام می شود، تنظیم کنید. از گزینه های زیر می توان به عنوان یک اقدام استفاده کرد:

      CASCADE: هنگامی که ردیف های مرتبط در جدول اصلی حذف یا اصلاح می شوند، به طور خودکار ردیف ها را از یک جدول وابسته حذف یا تغییر می دهد.

      SET NULL: هنگام حذف یا به روز رسانی یک ردیف مرتبط از جدول اصلی، ستون کلید خارجی را NULL تنظیم می کند. (در این مورد، ستون کلید خارجی باید از تنظیمات NULL پشتیبانی کند)

      RESTRICT: حذف یا اصلاح سطرها در جدول اصلی را در صورت وجود سطرهای مرتبط در جدول وابسته رد می کند.

      NO ACTION: مانند RESTRICT.

      SET DEFAULT: هنگام حذف یک ردیف مرتبط از جدول اصلی، ستون کلید خارجی را به مقدار پیش فرضی که با استفاده از ویژگی DEFAULT تعیین می شود، تنظیم می کند. با وجود این واقعیت که این گزینه در اصل موجود است، موتور InnoDB از این عبارت پشتیبانی نمی کند.

    حذف آبشاری

    حذف آبشاری به شما این امکان را می دهد که وقتی یک ردیف را از جدول اصلی حذف می کنید، به طور خودکار تمام ردیف های مرتبط را از جدول وابسته حذف کنید. برای این کار از گزینه CASCADE استفاده کنید:

    CREATE TABLE Orders (ID INT PRIMARY KEY AUTO_INCREMENT، CustomerId INT، CreatedAt Date، FOREIGN KEY (CustomerId) References Customers (ID) ON DELETE CASCADE).

    عبارت ON UPDATE CASCADE به روشی مشابه کار می کند. وقتی مقدار یک کلید اصلی را تغییر می دهید، مقدار کلید خارجی مرتبط با آن به طور خودکار تغییر می کند. با این حال، از آنجایی که کلیدهای اولیه به ندرت تغییر می کنند، و معمولاً استفاده از ستون هایی با مقادیر قابل تغییر به عنوان کلیدهای اصلی توصیه نمی شود، عبارت ON UPDATE به ندرت در عمل استفاده می شود.

    تنظیم NULL

    وقتی گزینه SET NULL را برای یک کلید خارجی تنظیم می کنید، ستون کلید خارجی باید NULL مجاز باشد:

    CREATE TABLE Orders (ID INT PRIMARY KEY AUTO_INCREMENT، CustomerId INT، CreatedAt Date، FOREIGN KEY (CustomerId) مراجع ارجاع مشتریان (ID) ON DELETE SET NULL).

    قبلاً در این کتاب، به روابط خاصی که بین فیلدهای خاصی از جداول معمولی وجود دارد اشاره کردیم. به عنوان مثال، فیلد snum جدول مشتریان با فیلد snum در جدول فروشندگان و جدول سفارشات مطابقت دارد. فیلد cnum جدول مشتریان نیز با فیلد cnum جدول سفارشات مطابقت دارد. ما این نوع رابطه را یکپارچگی مرجع نامیدیم. و در طول بحث، دیدید که چگونه می توان از آن استفاده کرد.

    در این فصل، یکپارچگی مرجع را با جزئیات بیشتری بررسی خواهید کرد و همه چیز را در مورد محدودیت هایی که می توانید برای حفظ آن استفاده کنید، یاد خواهید گرفت. همچنین خواهید دید که چگونه این محدودیت زمانی که از دستورات تغییر DML استفاده می کنید اعمال می شود. از آنجایی که یکپارچگی مرجع شامل ارتباط فیلدها یا گروه‌هایی از فیلدها، اغلب در جداول مختلف است، این عمل می‌تواند تا حدودی پیچیده‌تر از سایر محدودیت‌ها باشد. به همین دلیل، خوب است که با آن آشنایی کامل داشته باشید، حتی اگر قصد ایجاد جداول را ندارید. دستورات اصلاح شما را می‌توان با استفاده از یک محدودیت یکپارچگی مرجع کارآمدتر کرد (مانند سایر محدودیت‌ها، اما یک محدودیت یکپارچگی مرجع می‌تواند بر جداولی غیر از جدول‌هایی که روی آن تعریف شده است تأثیر بگذارد)، و توابع پرس و جوی خاص، مانند اتصالات، به صورت تکراری ساختار می‌شوند. از نظر روابط یکپارچگی مرجع (همانطور که در فصل 8 تاکید شد).

    کلید خارجی و کلید والد

    وقتی تمام مقادیر یک فیلد جدول در یک فیلد در جدول دیگر نشان داده می شود، می گوییم که فیلد اول به قسمت دوم اشاره دارد. این نشان دهنده رابطه مستقیم بین مقادیر دو فیلد است. به عنوان مثال، هر یک از مشتریان در جدول مشتریان دارای یک فیلد snum است که به فروشنده اختصاص داده شده در جدول فروشندگان اشاره می کند. برای هر سفارش در جدول سفارشات، یک و تنها این فروشنده و یک و تنها این مشتری وجود دارد. این با استفاده از فیلدهای snum و cnum در جدول Orders نمایش داده می شود.

    هنگامی که یک فیلد در جدول به فیلد دیگری اشاره می کند، آن را کلید خارجی می نامند. و فیلدی که به آن اشاره دارد، کلید والد نامیده می شود. بنابراین فیلد snum جدول مشتریان یک کلید خارجی است و فیلد snum که در جدول Vendors به ​​آن اشاره می کند، کلید والد است.

    به همین ترتیب، فیلدهای cnum و snum جدول سفارشات، کلیدهای خارجی هستند که به کلیدهای والد آنها که در جدول مشتریان و جدول فروشندگان نامگذاری شده اند، اشاره دارند. لازم نیست نام کلید خارجی و کلید اصلی یکسان باشد، این فقط یک قرارداد است که ما برای شفاف‌تر کردن پیوستن از آن پیروی می‌کنیم.

    کلیدهای خارجی چند ستونی

    در واقع، یک کلید خارجی لزوماً فقط از یک جنسیت تشکیل نمی شود. مانند یک کلید اولیه، یک کلید خارجی می‌تواند هر تعداد فیلد داشته باشد که همگی به عنوان یک واحد در نظر گرفته می‌شوند. کلید خارجی و کلید اصلی که به آن اشاره می کند، البته باید تعداد و جنسیت یکسانی داشته باشند و به یک ترتیب باشند. کلیدهای خارجی متشکل از یک جنسیت - آنهایی که به طور انحصاری در جداول استاندارد خود استفاده می کنیم، رایج ترین هستند. برای ساده نگه داشتن بحث، ما اغلب به یک کلید خارجی به عنوان یک ستون اشاره می کنیم. این تصادفی نیست. اگر به این نکته توجه نشود، هرکسی در مورد فیلدی که کلید خارجی است می گوید که آن هم به گروهی از فیلدها تعلق دارد که کلید خارجی هستند.

    معنی کلیدهای خارجی و والد

    وقتی یک فیلد یک کلید خارجی است، به نوعی با جدولی که به آن اشاره می کند مرتبط است. آنچه شما اساساً می گویید این است که "هر مقدار در این فیلد (کلید خارجی) مستقیماً به مقداری در فیلد دیگر (کلید والد) مرتبط است." هر مقدار (هر ردیف) یک کلید خارجی باید به طور واضح به یک و تنها مقدار (ردیف) کلید والد اشاره داشته باشد. اگر اینطور باشد، در واقع سیستم شما، همانطور که می گویند، در وضعیت یکپارچگی مرجع قرار می گیرد. با یک مثال می توانید این را ببینید. کلید خارجی snum در جدول مشتریان دارای مقدار 1001 برای ردیف های Hoffman و Clemens است. بیایید فرض کنیم که ما دو ردیف در جدول Vendors با مقدار فیلد snum = 1001 داشتیم. به همین ترتیب، اگر چنین ردیف هایی در جدول Vendors وجود نداشته باشد، در نهایت به هافمن و کلمنس به فروشنده ای اختصاص داده شده است که وجود ندارد!

    واضح است که هر مقدار در یک کلید خارجی باید یک بار و فقط یک بار در کلید والد نمایش داده شود.

    در واقع، یک مقدار کلید خارجی معین فقط می‌تواند به یک مقدار کلید والد اشاره داشته باشد بدون اینکه عکس آن ممکن باشد: i.e. هر تعداد کلید خارجی می تواند به یک مقدار کلید والد اشاره کند. شما می توانید این را در جداول معمولی نمونه های ما مشاهده کنید. هر دو Hoffman و Clemens به Peel اختصاص داده شده اند، بنابراین هر دو مقادیر کلید خارجی آنها با همان کلید والد یکسان است، که چیز خوبی است. یک مقدار کلید خارجی باید تنها به یک مقدار کلید اصلی ارجاع دهد، اما یک مقدار کلید والد را می توان با هر تعداد از مقادیر کلید خارجی ارجاع داد. به عنوان مثال، مقادیر کلید خارجی از جدول مشتریان که با کلید اصلی آنها در جدول فروشندگان مطابقت دارد، در شکل 19.1 نشان داده شده است. برای راحتی، جنسیت غیر مرتبط با این مثال را در نظر نگرفته ایم.

    محدودیت کلید خارجی

    SQL یکپارچگی ارجاعی را با محدودیت FOREIGN KEY حفظ می کند. اگرچه محدودیت FOREIGN KEY یک ویژگی جدید در SQL است، اما هنوز آن را جهانی نمی کند. علاوه بر این، برخی از پیاده سازی های آن پیچیده تر از بقیه هستند. این تابع باید مقادیری را که می توانید در پایگاه داده خود وارد کنید تا کلید خارجی و کلید والد را مجبور به رعایت یکپارچگی ارجاعی کنید، محدود کند. یکی از اقدامات یک محدودیت کلید خارجی، کنار گذاشتن مقادیر برای فیلدهایی است که به عنوان یک کلید خارجی محدود شده اند و قبلاً در کلید والد نشان داده نشده اند. این محدودیت همچنین بر توانایی شما برای تغییر یا حذف مقادیر کلید والد تأثیر می‌گذارد (ما در ادامه این فصل به آن خواهیم پرداخت).

    چگونه می توان فیلدها را به عنوان کلیدهای خارجی نشان داد

    شما از یک محدودیت کلید خارجی در دستور CREATE TABLE (یا ALTER TABLE) استفاده می کنید که حاوی فیلدی است که می خواهید به عنوان کلید خارجی اعلام کنید. شما کلید والد را به آنها می دهید که در داخل محدودیت FOREIGN KEY به آن ارجاع خواهید داد. قرار دادن این محدودیت در دستور همانند سایر محدودیت هایی است که در فصل قبل مورد بحث قرار گرفت. شکل 19.1: کلید خارجی جدول مشتری با کلید والد

    مانند بسیاری از محدودیت ها، می تواند یک محدودیت جدول یا ستون باشد، به شکل جدولی که اجازه می دهد چندین فیلد به عنوان یک کلید خارجی استفاده شود.

    کلید خارجی به عنوان محدودیت جدول

    نحو محدودیت جدول FOREIGN KEY: FOREIGN KEY منابع [ ] فهرست ستون اول یک لیست جدا شده با کاما از یک یا چند ستون جدول است که با این دستور ایجاد یا اصلاح می شود. Pktable جدول حاوی کلید والد است. این می تواند جدولی باشد که با دستور فعلی ایجاد یا اصلاح شود. لیست ستون دوم لیستی از ستون هایی است که کلید والد را تشکیل می دهند. دو لیست ستون باید با هم سازگار باشند، به عنوان مثال:

    * باید تعداد ستون های یکسانی داشته باشند.

    * در این دنباله، ستون‌های اول، دوم، سوم و غیره از فهرست ستون‌های کلید خارجی باید دارای انواع داده‌ها و اندازه‌های مشابه ستون‌های اول، دوم، سوم و غیره از فهرست ستون‌های کلید والد باشند. ستون‌های فهرست هر دو ستون نباید نام یکسانی داشته باشند، اگرچه ما از این روش در مثال‌های خود برای شفاف‌تر کردن رابطه استفاده کردیم.

    بیایید یک جدول مشتریان با فیلد snum تعریف شده به عنوان یک کلید خارجی و ارجاع به جدول فروشندگان ایجاد کنیم: CREATE TABLE مشتریان (cnum عدد صحیح NOT PRIMARY Cname cname char(10)، شهر char(10)، عدد صحیح snum، کلید خارجی (snum) مراجع فروشندگان (snum)؛ به خاطر داشته باشید که هنگام استفاده از ALTER TABLE به جای CREATE TABLE، برای اعمال محدودیت FOREIGN KEY، مقادیری که در کلید خارجی و کلید والد مشخص می کنید باید در حالت یکپارچگی مرجع باشند، در غیر این صورت دستور اگر چه ALTER TABLE بسیار مفید است - برای راحتی آن، شما باید ابتدا اصول ساختاری، مانند یکپارچگی مرجع، را در هر زمان که ممکن است در سیستم خود فرموله کنید.

    کلید خارجی به عنوان محدودیت ستونی

    گزینه محدود کردن یک ستون با یک محدودیت کلید خارجی، محدودیت منابع نیز نامیده می شود، زیرا در واقع حاوی کلمات FOREIGN KEY نیست، بلکه به سادگی از کلمه REFERENCES و به دنبال آن کلید والد استفاده می کند، مانند این: CREATE TABLE Customers ( cnum عدد صحیح NOT NULL PRIMARY KEY, cname char(10), city char(10), snum integer REFERENCES Salespeople (snum)); موارد فوق Customers.snum را به عنوان یک کلید خارجی تعریف می کند که کلید اصلی آن Salespeople.snum است. این معادل محدودیت جدولی مانند این است: کلید خارجی (snum) REGERENCES فروشندگان (snum)

    فهرستی از ستون های کلید اصلی را مشخص نکنید

    با استفاده از یک محدودیت کلید خارجی در جدول یا ستون، می‌توانید لیست ستون‌های کلید والد را حذف کنید اگر کلید والد دارای محدودیت کلید اصلی باشد. طبیعتاً در مورد کلیدهای دارای فیلدهای زیاد، ترتیب ستونها در کلیدهای خارجی و اصلی باید مطابقت داشته باشد و در هر صورت اصل سازگاری بین دو کلید همچنان برقرار است. به عنوان مثال، اگر یک محدودیت PRIMARY KEY را در فیلد snum جدول فروش قرار دهیم، می توانیم آن را به عنوان یک کلید خارجی در جدول مشتریان (مشابه مثال قبلی) با این دستور استفاده کنیم: CREATE TABLE Customers (cnum integer NOT NULL). PRIMARY KEY, cname char(10) , city char(10), snum integer REFERENCES Salespeople); این ویژگی برای تشویق شما به استفاده از کلیدهای اصلی به عنوان کلیدهای والد در زبان تعبیه شده است.

    چگونه یکپارچگی مرجع مقادیر یک کلید والدین را محدود می کند

    حفظ یکپارچگی ارجاعی مستلزم برخی محدودیت‌ها در مقادیری است که می‌توانند در فیلدهایی که به‌عنوان کلید خارجی و کلید والد معرفی می‌شوند، نمایش داده شوند. کلید والد باید طوری ساختار یافته باشد که اطمینان حاصل شود که هر مقدار کلید خارجی با یک ردیف مشخص مطابقت دارد. این بدان معنی است که آن (کلید) باید منحصر به فرد باشد و حاوی مقادیر خالی (NULL) نباشد. اگر همان الزامی که هنگام اعلام یک کلید خارجی برآورده شود، برای کلید مادر کافی نیست. SQL باید اطمینان حاصل کند که مقادیر دوگانه یا مقادیر تهی در کلید والد وارد نمی شوند. بنابراین، باید اطمینان حاصل کنید که تمام فیلدهایی که به عنوان کلیدهای والد استفاده می‌شوند، دارای یک محدودیت PRIMARY KEY یا یک محدودیت UNIQUE هستند، مانند محدودیت NOT NULL.

    کلید اولیه به عنوان یک کلید خارجی منحصر به فرد

    پیوند دادن کلیدهای خارجی فقط به کلیدهای اصلی، همانطور که در جداول استاندارد انجام دادیم، استراتژی خوبی است. هنگامی که از کلیدهای خارجی استفاده می کنید، آنها را فقط با کلیدهای اصلی که به آنها اشاره می کنند مرتبط نمی کنید. شما آنها را با یک ردیف جدول خاص مرتبط می کنید که در آن کلید والد پیدا می شود. خود کلید والد هیچ اطلاعاتی را ارائه نمی دهد که قبلاً در کلید خارجی وجود نداشته باشد. به عنوان مثال، معنای جنسیت snum به عنوان یک کلید خارجی در جدول مشتریان، رابطه ای است که ارائه می دهد، نه با مقدار جنسیت که به آن اشاره می کند، بلکه با سایر اطلاعات موجود در جدول فروش، مانند نام ها افراد فروش، مکان آنها و غیره. . یک کلید خارجی صرفاً رابطه بین دو مقدار یکسان نیست. این یک رابطه با استفاده از این دو مقدار، بین دو ردیف جدول مشخص شده در پرس و جو است. از این فیلد snum می توان برای مرتبط کردن هر گونه اطلاعات در یک ردیف از جدول مشتریان با یک ردیف مرجع از جدول فروشندگان استفاده کرد - برای مثال، برای یافتن اینکه آیا آنها در همان شهر زندگی می کنند، چه کسی نام طولانی تری دارد یا خیر، آیا فروشنده نام دارد یا خیر. هر مشتری دیگری غیر از این مشتریان مشتری و غیره. از آنجایی که هدف کلید اصلی شناسایی منحصر به فرد بودن یک ردیف است، برای یک کلید خارجی انتخابی منطقی تر و کمتر مبهم است. برای هر کلید خارجی که از یک کلید منحصر به فرد به عنوان کلید اصلی خود استفاده می کند، باید یک کلید خارجی ایجاد کنید که از کلید اصلی همان جدول برای همان جلوه استفاده کند. یک کلید خارجی که هدف دیگری جز پیوند دادن ردیف ها ندارد، شبیه کلید اصلی است که صرفاً برای شناسایی ردیف ها استفاده می شود و روش خوبی برای شفاف و ساده نگه داشتن ساختار پایگاه داده شما و در نتیجه کمتر پیچیده است.

    محدودیت های کلیدی خارجی

    یک کلید خارجی، به ویژه، فقط می تواند حاوی مقادیری باشد که در واقع در کلید والد وجود دارند یا خالی هستند (NULL). هرگونه تلاش برای وارد کردن مقادیر دیگر در این کلید رد خواهد شد. شما می توانید یک کلید خارجی را به عنوان NOT NULL اعلام کنید، اما این ضروری نیست و در بیشتر موارد، نامطلوب است. برای مثال، فرض کنید مشتری را وارد می کنید بدون اینکه از قبل بدانید به کدام فروشنده منصوب می شود. بهترین راه در این شرایط استفاده از یک مقدار NOT NULL است که باید بعداً به یک مقدار خاص تغییر یابد.

    اگر یک دستور اصلاح را اجرا کنید چه اتفاقی می افتد

    اجازه دهید شرط کنیم که تمام کلیدهای خارجی ایجاد شده در جداول مثال ما با محدودیت های کلید خارجی اعلان و اجرا شوند، به شرح زیر: CREATE TABLE فروشندگان (عدد صحیح NOT NULL PRIMARY KEY، sname char(10) NOT NULL، شهر char(10) comm اعشاری ) CREATE TABLE مشتریان (cnum integer NOT NULL PRIMARY KEY، cname char(10) NOT NULL، city char(10)، رتبه بندی عدد صحیح، snum integer، FOREIGN KEY (snum) مراجع فروشندگان، UNIQUE (cnum، OrdersREATEB) cnum عدد صحیح NOT NULL PRIMARY KEY, amt اعشاری, تاریخ تاریخ NOT NULL, cnum عدد صحیح NOT NULL عدد صحیح NOT NULL کلید خارجی (cnum, snum) مراجع CUSTOMERS (cnum, snum);

    شامل توضیحات جدول

    چندین ویژگی از این گونه تعاریف وجود دارد که باید مورد بحث قرار گیرد. دلیل اینکه ما تصمیم گرفتیم طبقه‌های cnum و snum را در جدول Orders یک کلید خارجی قرار دهیم این است که اطمینان حاصل کنیم که برای هر مشتری موجود در سفارش‌ها، فروشنده اعتبار دهنده این سفارش همان چیزی است که در جدول مشتریان نشان داده شده است. برای ایجاد چنین کلید خارجی، ما باید یک محدودیت جدول UNIQUE را در دو طبقه از جدول مشتری قرار دهیم، حتی اگر خود آن جدول به آن نیازی نداشته باشد. تا زمانی که فیلد cnum در این جدول دارای یک محدودیت PRIMARY KEY باشد، در هر صورت منحصر به فرد خواهد بود و بنابراین نمی توان ترکیب دیگری از فیلد cnum با فیلد دیگری به دست آورد. ایجاد یک کلید خارجی به این روش، یکپارچگی پایگاه داده را حفظ می کند، حتی اگر مانع از قطع داخلی به اشتباه و اعتبار دادن به هر فروشنده ای غیر از فروشنده ای شود که به آن مشتری خاص اختصاص داده شده است.

    از نقطه نظر حفظ یکپارچگی پایگاه داده، وقفه های داخلی (یا استثناها) البته نامطلوب هستند. اگر به آنها اجازه می دهید و در عین حال می خواهید یکپارچگی پایگاه داده خود را حفظ کنید، می توانید فیلدهای snum و cnum را در جدول Orders به ​​عنوان کلید خارجی مستقل این فیلدها در جدول Vendors و جدول Customers اعلام کنید. در واقع، استفاده از sex snum در جدول Order همانطور که ما انجام دادیم ضروری نیست، اگرچه برای تنوع مفید است. فیلد cnum که هر سفارش مشتری را در جدول مشتری، در جدول سفارش و در جدول مشتری پیوند می دهد، باید همیشه به اشتراک گذاشته شود تا فیلد snum صحیح برای آن سفارش (بدون اجازه هیچ استثنایی) پیدا شود. این بدان معنی است که ما در حال ضبط یک قطعه اطلاعات هستیم - کدام مشتری به کدام فروشنده اختصاص داده شده است - و باید کارهای بیشتری انجام شود تا مطمئن شویم که هر دو نسخه سازگار هستند. اگر همانطور که در بالا گفته شد محدودیت کلید خارجی نداشته باشیم، این وضعیت به ویژه مشکل ساز خواهد بود زیرا هر سفارش باید به صورت دستی (همراه با درخواست) بررسی شود تا اطمینان حاصل شود که فروشنده مربوطه هر فروش مربوطه را اعتبار داده است. داشتن این نوع افزونگی اطلاعات در پایگاه داده شما غیرعادی سازی نامیده می شود که در یک پایگاه داده رابطه ای ایده آل نامطلوب است، اگرچه در عمل قابل حل است. تخریب می تواند باعث شود برخی از پرس و جوها سریعتر اجرا شوند، زیرا یک پرس و جو در یک جدول همیشه بسیار سریعتر از یک پرس و جو در یک پیوست است.

    اثر محدودیت ها

    چنین محدودیت هایی چگونه بر توانایی و ناتوانی شما در استفاده از دستورات اصلاح DML تأثیر می گذارد؟ برای فیلدهایی که به عنوان کلیدهای خارجی تعریف می شوند، پاسخ بسیار ساده است: هر مقداری که با دستور INSERT یا UPDATE در آن فیلدها قرار می دهید باید از قبل در کلیدهای والد آنها وجود داشته باشد. می توانید مقادیر NULL را در این فیلدها قرار دهید، اگرچه مقادیر NULL در کلیدهای والد مجاز نیستند اگر محدودیت NOT NULL داشته باشند. می‌توانید بدون استفاده از کلیدهای والد، هر ردیفی را با کلیدهای خارجی حذف کنید.

    از آنجایی که سوال تغییر مقادیر کلید والد مطرح می شود، پاسخ، همانطور که توسط ANSI تعریف شده است، حتی ساده تر است، اما شاید تا حدودی محدودتر است: هر مقدار کلید اصلی که توسط یک مقدار کلید خارجی ارجاع می شود، قابل حذف یا تغییر نیست. این به این معنی است که، برای مثال، نمی‌توانید مشتری را از جدول مشتریان حذف کنید، در حالی که هنوز سفارش‌هایی در جدول سفارش‌ها وجود دارد. بسته به نحوه استفاده شما از این جداول، این می تواند مطلوب یا دردسرساز باشد. با این حال، این مطمئناً بهتر از داشتن سیستمی است که به شما امکان می‌دهد مشتری را با سفارش‌های فعلی حذف کنید و جدول سفارش‌ها را به مشتریانی که وجود ندارند رجوع کنید. نکته این سیستم محدودیت این است که سازنده جدول Orders با استفاده از جدول مشتریان و جدول فروشندگان به عنوان کلیدهای مادر، می تواند محدودیت های قابل توجهی را برای اعمال در این جداول اعمال کند. به همین دلیل، تا زمانی که مالک (خالق) آن جدول به طور خاص این حق را به شما اعطا نکند، نمی توانید از جدولی استفاده کنید که کنترل آن را ندارید (یعنی شما آن را ایجاد نکرده اید و مالک آن نیستید). بنابراین (همانطور که در فصل 22 توضیح داده شد). برخی دیگر از اقدامات ممکن برای تغییر کلید والد وجود دارد که بخشی از ANSI نیستند اما ممکن است در برخی از برنامه‌های تجاری یافت شوند. اگر می خواهید مقدار مرجع فعلی یک کلید والد را تغییر دهید یا حذف کنید، اساساً سه احتمال وجود دارد:

  • شما می توانید تغییرات را محدود یا ممنوع کنید (به روش ANSI) با تعیین اینکه تغییرات در کلید والد محدود است.
  • می توانید تغییری در کلید والد ایجاد کنید و از این طریق تغییراتی را در کلید خارجی به صورت خودکار ایجاد کنید که به آن تغییر آبشاری می گویند.
  • می توانید تغییری در کلید والد ایجاد کنید و کلید خارجی را به صورت خودکار روی NULL قرار دهید (با فرض اینکه NULLS در کلید خارجی مجاز باشد) که به آن تغییر کلید خارجی تهی می گویند.

    حتی در این سه دسته، ممکن است نخواهید همه دستورات اصلاح را به این شکل مدیریت کنید. البته INSERT بی ربط است. مقادیر جدید کلید والد را در جدول قرار می دهد تا در حال حاضر هیچ یک از آن مقادیر قابل فراخوانی نباشد. با این حال، ممکن است بخواهید اجازه دهید تغییرات بدون حذف آبشاری شوند و بالعکس. یک موقعیت بهتر ممکن است موقعیتی باشد که به شما امکان می دهد هر یک از سه دسته را مستقل از دستورات UPDATE و DELETE تعریف کنید. بنابراین ما به جلوه‌های به‌روزرسانی و جلوه‌های حذف اشاره می‌کنیم، که تعیین می‌کنند اگر فرمان UPDATE یا DELETE را روی کلید والد صادر کنید چه اتفاقی می‌افتد. این اثراتی که در مورد آنها صحبت کردیم نامیده می شوند: تغییرات محدود، تغییرات CASCADES و تغییرات NULL. قابلیت های واقعی سیستم شما باید در استاندارد ANSI سختگیرانه باشد - اثرات اصلاح و حذف هر دو به طور خودکار محدود می شوند - برای وضعیت ایده آل تر که در بالا توضیح داده شد. برای نشان دادن، ما چند نمونه از کارهایی را که می‌توانید با طیف کاملی از جلوه‌های اصلاح و حذف انجام دهید، نشان خواهیم داد. البته، افکت‌های اصلاح و حذف، که ابزارهای غیراستاندارد هستند، فاقد نحو استاندارد حالت هستند. نحوی که ما در اینجا استفاده می کنیم برای نوشتن آسان است و برای نشان دادن بیشتر عملکرد این افکت ها مفید است.

    برای کامل‌تر شدن آزمایش، فرض می‌کنیم که در موردی که جدول Vendors ما پارتیشن‌ها را تغییر می‌دهد، دلیلی برای تغییر فیلد snum جدول Vendors دارید. (معمولاً تغییر کلیدهای اصلی چیزی نیست که ما انجام آن را در عمل توصیه می کنیم. این فقط دلیل دیگری برای کلیدهای اصلی موجود است که نمی دانند چگونه به جز کلید اصلی عمل کنند: آنها نباید تغییر کنند.) وقتی فروشنده را تغییر می دهید. شماره، شما می خواهید همه مشتریان آن ذخیره شوند. با این حال، اگر این فروشنده شرکت یا شرکت خود را ترک کند، ممکن است نخواهید مشتریانش را حذف کنید و او را از پایگاه داده حذف کنید. در عوض، باید مطمئن شوید که مشتریان به شخص دیگری اختصاص داده شده اند. برای این کار باید UPDATE با افکت Cascading و DELETE با افکت Limited مشخص کنید. ایجاد جدول مشتریان (cnum عدد صحیح NOT NULL PRIMARY KEY، cname char(10) NOT NULL، city char(10)، رتبه بندی عدد صحیح، snum عدد صحیح مراجع فروشندگان، به روز رسانی فروشندگان CASCADES، DELEEST OF Salespeop); اگر اکنون سعی کنید Peel را از جدول Vendors حذف کنید، این فرمان تا زمانی که مقدار جنسیت مشتریان Hoffman و Clemens را برای فروشنده اختصاص داده شده دیگر تغییر ندهید معتبر نخواهد بود. از طرف دیگر، می توانید مقدار snum جنسی را برای Peel به 1009 تغییر دهید و هافمن و کلمنز نیز به طور خودکار تغییر می کنند.

    سومین اثر تغییرات خالی (NULL) است. این اتفاق می افتد که هنگام خروج فروشندگان از یک شرکت، سفارشات فعلی آنها به فروشنده دیگری منتقل نمی شود. از طرف دیگر، شما می خواهید تمام سفارشات را به طور خودکار برای مشتریانی که حساب هایشان را حذف می کنید لغو کنید. با تغییر شماره های فروشنده یا مشتری می توانید به سادگی آنها را به او منتقل کنید. مثال زیر نشان می دهد که چگونه می توانید با استفاده از این افکت ها یک جدول سفارش ایجاد کنید. سفارشات جدول ایجاد کنید (عدد صحیح NOT NULL PRIMARY KEY، AMT اعشاری، تاریخ تاریخ NOT NULL عدد صحیح NULL NULL ارجاعات مشتریان تعداد صحیح مراجع فروشندگان، به روز رسانی مشتریان CASCADES، DELETE OFCADLESCADES,DELETE OF Customers OF Salespeople NULLS)؛ البته در دستور DELETE با اثر تغییر Empty در جدول Vendors، محدودیت NOT NULL باید از قسمت snum حذف شود.

    کلیدهای خارجی که به جداول موضوعی خود ارجاع می دهند

    همانطور که قبلا ذکر شد، یک محدودیت FOREIGN KEY می تواند این جدول خصوصی را به عنوان یک جدول کلید والد نشان دهد. این ویژگی به دور از ساده بودن، می تواند مفید باشد. بیایید فرض کنیم که ما یک جدول Employees با یک فیلد مدیر داریم. این فیلد شامل شماره هر یک از کارکنان است که برخی از آنها مدیر نیز هستند. اما از آنجایی که هر مدیر در همان زمان کارمند باقی می ماند، طبیعتاً در این جدول نیز نماینده خواهد بود. بیایید جدولی ایجاد کنیم که در آن شماره کارمند (ستونی به نام empno) به عنوان کلید اصلی اعلام شود، و مدیر به عنوان یک کلید خارجی به آن ارجاع دهد: CREATE TABLE Employees (empno integer NOT NULL PRIMARY KEY، نام char(10) NOT NULL UNIOUE , manager integer REFERENCES Employees); (از آنجایی که کلید خارجی، کلید اصلی مرجع جدول است، لیست ستون را می توان حذف کرد.) محتوای این جدول وجود دارد: EMPNO NAME MANAGER _____ ________ _______ 1003 Terrence 2007 2007 Atali NULL 1681 1681 McKenna 1681 McKenna می توانید ببینید، هر یک از اینها (اما نه آتالی)، به کارمند دیگری در جدول به عنوان سرپرست خود اشاره می کند. آتالی که بیشترین عدد را در جدول دارد، باید مقدار آن NULL باشد. این اصل دیگری از یکپارچگی ارجاعی می دهد. یک کلید خارجی که به یک جدول خصوصی ارجاع می دهد باید مقادیر = NULL را مجاز کند. اگر اینطور نیست، چگونه ردیف اول را وارد می کنید؟ حتی اگر این ردیف اول به خودش اشاره داشته باشد، زمانی که مقدار کلید خارجی وارد می شود، باید مقدار کلید والد از قبل تنظیم شده باشد. این اصل صادق خواهد بود حتی اگر کلید خارجی به جدول خصوصی نه مستقیماً بلکه از طریق ارجاع به جدول دیگری که سپس به جدول کلید خارجی باز می گردد، ارجاع دهد. به عنوان مثال، فرض کنید جدول فروش ما دارای یک فیلد اضافی است که به جدول مشتریان ارجاع می دهد، به طوری که هر جدول به جدول دیگر ارجاع می دهد، همانطور که در عبارت CREATE TABLE زیر نشان داده شده است: فروشندگان جدول ایجاد کنید (عدد صحیح NOT NULL PRIMARY KEY، sname char(10) NOT NULL، city char(10)، comm declmal، cnum integer REFERENCES Customers); CREATE TABLE مشتریان (cnum عدد صحیح NOT NULL PRIMARY KEY، cname char(10) NOT NULL، city char(10)، رتبه بندی عدد صحیح، snum عدد صحیح مراجع فروشندگان). این یک مرجع متقابل نامیده می شود. SQL در تئوری از این پشتیبانی می کند، اما در عمل می تواند مشکل ساز باشد. هر کدام از این دو جدول اول ایجاد شود، جدول مرجعی است که هنوز برای دیگری وجود ندارد. به منظور ارجاع متقابل، SQL در واقع این اجازه را می‌دهد، اما هیچ یک از جدول‌ها در زمانی که هر دو در حال ایجاد هستند قابل استفاده نخواهد بود. از طرف دیگر، اگر این دو جدول توسط کاربران مختلف ایجاد شود، مشکل حتی دشوارتر می شود. ارجاع متقابل می تواند ابزار مفیدی باشد، اما خالی از ابهام و خطر نیست. برای مثال مثال قبلی کاملاً قابل استفاده نیست زیرا فروشنده را به یک مشتری محدود می کند و برای دستیابی به این امر نیازی به استفاده از ارجاع متقابل نیست. توصیه می کنیم قبل از ایجاد یک سیستم یکپارچگی ارجاع متقابل، در استفاده از آن دقت کنید و نحوه مدیریت اثرات اصلاح و حذف و همچنین فرآیندهای امتیازات و پردازش پرس و جو تعاملی را تجزیه و تحلیل کنید. (امتیازات و پردازش درخواست تعاملی به ترتیب در فصل های 22 و 1 مورد بحث قرار خواهند گرفت.)

    خلاصه

    اکنون کنترل نسبتاً خوبی بر یکپارچگی مرجع دارید. ایده اصلی این است که تمام مقادیر کلید خارجی به ردیف کلید اصلی مشخص شده اشاره دارد. این بدان معناست که هر مقدار کلید خارجی باید یک بار و فقط یک بار در کلید اصلی نمایش داده شود. هر زمان که یک مقدار در یک کلید خارجی قرار می گیرد، کلید والد بررسی می شود تا اطمینان حاصل شود که مقدار آن نشان داده شده است. در غیر این صورت دستور رد خواهد شد. کلید والد باید دارای یک محدودیت PRIMARY KEY یا UNIQUE باشد تا اطمینان حاصل شود که مقدار بیش از یک بار نمایش داده نمی شود. تلاش برای تغییر یک مقدار کلید اصلی که در حال حاضر در یک کلید خارجی نشان داده شده است به طور کلی رد می شود. با این حال، سیستم شما ممکن است این انتخاب را به شما پیشنهاد دهد که مقدار کلید خارجی را NULL تنظیم کنید یا مقدار جدید کلید والد را دریافت کنید و مشخص کنید که کدام یک را می توان به طور مستقل برای دستورات UPDATE و DELETE بدست آورد. این بحث ما در مورد دستور CREATE TABLE به پایان می رسد. در ادامه شما را با نوع دیگری از دستورات - CREATE - آشنا می کنیم. در فصل 20، شما یاد خواهید گرفت که چگونه اشیاء داده ای را نشان دهید که شبیه جدول هستند و عمل می کنند اما در واقع نتایج پرس و جو هستند. برخی از توابع محدودیت را می توان توسط view ها نیز انجام داد، بنابراین می توانید پس از خواندن سه فصل بعدی، نیاز خود به محدودیت ها را بهتر ارزیابی کنید.

    کار با SQL

    1. جدولی به نام Cityorders ایجاد کنید. باید دارای فیلدهای onum، amt و snum مشابه جدول Orders و همان فیلدهای cnum و شهر جدول مشتریان باشد تا سفارش هر مشتری به همراه شهر خود در این جدول وارد شود. فیلد onum کلید اصلی Cityorders خواهد بود. همه طبقات در Cityorders باید در مقایسه با جداول مشتریان و سفارشات دارای محدودیت باشند. این امکان وجود دارد که کلیدهای والد در این جداول از قبل دارای محدودیت های مناسب باشند.

    2. بیایید مشکل را پیچیده کنیم. جدول Orders را به صورت زیر تعریف کنید: یک ستون جدید به نام prev اضافه کنید که برای هر سفارش مشخص می شود، فیلد onum سفارش قبلی برای مشتری فعلی. این کار را با استفاده از یک کلید خارجی که به جدول Order ارجاع می دهد، انجام دهید. کلید خارجی همچنین باید به فیلد cnum مشتری ارجاع داده شود و یک رابطه تجویز شده خاص بین سفارش فعلی و سفارش ارجاع شده ارائه کند.

    (برای پاسخ به پیوست A مراجعه کنید.)

    • InterBase می تواند از انواع محدودیت های زیر استفاده کند:
    • کلید اولیه - کلید اصلی جدول.
    • UNIQUE - کلید جدول منحصر به فرد.
    • کلید خارجی- کلید خارجی، پیوندی به جدول دیگری ارائه می دهد و یکپارچگی ارجاعی را بین والد و تضمین می کند میزهای کودک.

    نکته ای در مورد اصطلاحات

    اگر شما مانند نویسنده این دوره هستید که دوست دارید در آثار مختلف نویسندگان مختلف به طور جامع به دنبال پاسخ سؤالی باشید که مورد علاقه شماست، پس نمی توانید متوجه سردرگمی در تعاریف شوید. اصلی (استاد) -> تابع (جزئیات)جداول به یاد بیاورید که جدول اصلی اغلب جدول والد و جدول فرعی اغلب جدول فرزند نامیده می شود.

    این احتمالاً به دلیل نحوه تفسیر این تعاریف در DBMS های محلی و سرور SQL است.

    در DBMS های محلی، جدول اصلی جدولی است که حاوی داده های اصلی است و جدول فرعی حاوی داده های اضافی است. برای مثال، سه جدول مرتبط را در نظر می گیریم. اولی شامل اطلاعات مربوط به فروش، دومی - در مورد محصولات و سوم - در مورد مشتریان است:


    برنج. 18.1.

    در اینجا اطلاعات اصلی در جدول فروش ذخیره می شود، از این رو جدول اصلی (والد) است. اطلاعات تکمیلی در جداول محصول و مشتریان ذخیره می شود، به این معنی که آنها کودک هستند. این قابل درک است: یک دختر نمی تواند دو مادر بیولوژیک داشته باشد، اما یک مادر کاملاً قادر به به دنیا آوردن دو دختر است.

    اما در سرورهای پایگاه داده SQL تعریف متفاوتی از روابط وجود دارد: وقتی یک فیلد در جدول به فیلدی در جدول دیگر اشاره می کند، آن را نامیده می شود. کلید خارجی. و میدانی که به آن اشاره می کند نامیده می شود والدین یا کلید اصلی. جدولی که دارای کلید خارجی است (پیوندی به یک رکورد در جدول دیگر) اغلب فرزند و جدولی با نامیده می شود کلید والد- والدین همچنین در تعریف روابط می گویند که یک پدر و مادر می تواند تنها یک رکورد منحصر به فرد داشته باشد که می تواند توسط چندین رکورد به آن ارجاع داده شود. میز کودک.

    بنابراین در مثال بالا، جدول فروش دارای دو کلید خارجی است: شناسه محصول و شناسه مشتری. و هر دو جدول سمت راست شکل دارند کلید والد"مشخص کننده". از آنجایی که همان مشتری یا محصول می تواند بارها و بارها در جدول فروش ظاهر شود، معلوم می شود که هر دو جدول سمت راست شکل والدین هستند و جدول سمت چپ یک کودک است. چون الان داریم درس میخونیم InterBase - SQLسرور پایگاه داده، در سخنرانی های بعدی با این تعاریف هدایت خواهیم شد. برای اینکه مغزمان را بیشتر از این سردرگمی درگیر نکنیم، بیایید فوراً توافق کنیم: میز کودکدارای یک کلید خارجی (FREIGN KEY) به جدول دیگر.

    کلید اولیه

    کلید اولیه- کلید اصلی یکی از انواع اصلی محدودیت ها در پایگاه داده است. کلید اولیه برای شناسایی یک رکورد در جدول طراحی شده است و باید منحصر به فرد باشد. کلیدهای اولیه PRIMARY KEY در جداول قرار دارند که معمولاً به آنها والد (Parent) می گویند. کلید اولیه نباید با شاخص های اولیه پایگاه های داده محلی اشتباه گرفته شود؛ کلید اولیه یک شاخص نیست، بلکه یک محدودیت است. هنگام ایجاد یک کلید اصلی InterBaseبه طور خودکار برای او ایجاد می کند شاخص منحصر به فرد. با این حال، اگر ایجاد کنیم شاخص منحصر به فرد، این ایجاد نمی کند محدودیت های کلید اولیه. یک جدول فقط می تواند یک کلید اصلی داشته باشد، کلید اصلی.

    فرض کنید یک جدول با لیستی از کارمندان دارید. فیلد نام خانوادگی می تواند حاوی مقادیر تکراری (همنام) باشد، بنابراین نمی توان از آن به عنوان کلید اصلی استفاده کرد. نادر است، اما همنام هایی هستند که علاوه بر این، نام های مشابهی دارند. حتی به ندرت، نام های کامل وجود دارد، بنابراین حتی هر سه قسمت "نام خانوادگی" + "نام" + "نام پدر" نمی توانند منحصر به فرد بودن رکورد را تضمین کنند و نمی توانند کلید اصلی باشند. در این مورد، راه حل، مانند قبل، اضافه کردن یک فیلد شناسه است که شامل شماره سریال این شخص است. چنین فیلدهایی معمولاً به صورت خودکار افزایش می یابند (در سخنرانی های بعدی در مورد سازماندهی فیلدهای افزایش خودکار صحبت خواهیم کرد). بنابراین،

    کلید اصلی یک یا چند فیلد در یک جدول است که ترکیب آنها برای هر رکورد منحصر به فرد است.

    اگر کلید اصلی شامل یک ستون واحد باشد (همانطور که اغلب مورد است)، مشخص کننده کلید اولیه زمانی استفاده می شود که تعریف ستون:

    CREATE TABLE Prim_1(Stolbec1 INT NOT NULL PRIMARY KEY، Stolbec2 VARCHAR(50))

    اگر کلید اصلی بر روی چندین ستون ساخته شده باشد، پس از تعریف تمام فیلدها، مشخص کننده قرار می گیرد:

    CREATE TABLE Prim_2 (Stolbec1 INT NOT NULL، Stolbec2 VARCHAR(50) NOT NULL، PRIMARY KEY (Stolbec1، Stolbec2))

    همانطور که از مثال ها مشخص است، کلید اصلی است باید یک محدودیت NOT NULL ستون(های) داشته باشد.

    منحصر بفرد

    منحصر بفرد- کلید منحصر به فرد مشخص کننده UNIQUE نشان می دهد که تمام مقادیر این فیلد باید منحصر به فرد باشد، بنابراین، چنین فیلدهایی نیز نمی توانند حاوی مقادیر باشند. خالی. می توانید بگویید که یک کلید UNIQUE جایگزینی برای کلید اصلی است، اما تفاوت هایی وجود دارد. تفاوت اصلی این است که باید فقط یک کلید اصلی وجود داشته باشد، در حالی که می تواند چندین کلید منحصر به فرد وجود داشته باشد. علاوه بر این، یک محدودیت UNIQUE را نمی توان روی همان مجموعه ستون هایی که برای یک کلید اولیه یا سایر محدودیت های UNIQUE استفاده شده است، ایجاد کرد. کلیدهای منحصر به فرد، مانند کلیدهای اصلی، در جداولی یافت می شوند که پدر و مادر جداول دیگر هستند.

    یک ستون اعلام شده با یک محدودیت UNIQUE، مانند یک کلید اصلی، می تواند برای اعمال یکپارچگی ارجاعی بین والد و آن استفاده شود. میزهای کودک. در این مورد، کلید خارجی میز کودکبه این فیلد(ها) اشاره خواهد کرد. مانند یک کلید اولیه، زمانی که یک کلید منحصر به فرد ایجاد می شود، a شاخص منحصر به فرد. اما نه برعکس. نمونه ای از ایجاد جدول با یک کلید اصلی و دو کلید منحصر به فرد:

    CREATE TABLE Prim_3(Stolbec1 INT NOT NULL PRIMARY KEY، Stolbec2 VARCHAR(50) NOT NULL UNIQUE، Stolbec3 FLOAT NULL UNIQUE)

    کلید خارجی

    کلید خارجی- کلید خارجی این یک ابزار بسیار قدرتمند برای اطمینان از یکپارچگی ارجاعی بین جداول است که به شما امکان می دهد نه تنها بر وجود پیوندهای صحیح نظارت کنید، بلکه آنها را به طور خودکار مدیریت کنید. کلیدهای خارجی در جداول فرزند (کودک) جداول دیگر موجود است. تمامیت ارجاعیدقیقاً توسط یک کلید خارجی ارائه می شود که به اولیه یا اشاره دارد

    آخرین به روز رسانی: 1396/07/09

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

    نحو کلی برای تنظیم یک کلید خارجی در سطح ستون به شرح زیر است:

    REFERENCES main_table (main_table_column)

    برای ایجاد محدودیت کلید خارجی در سطح ستون، پس از کلمه کلیدی REFERENCES نام جدول مربوطه و در داخل پرانتز، نام ستون مربوطه که کلید خارجی به آن اشاره خواهد کرد، آمده است. کلمات کلیدی FOREIGN KEY نیز معمولاً اضافه می شوند، اما در اصل ضروری نیستند. بعد از عبارت REFERENCES عبارت ON DELETE و ON UPDATE می آید.

    نحو کلی برای تنظیم یک کلید خارجی در سطح جدول به شرح زیر است:

    کلید خارجی (ستون 1، ستون 2، ... ستونN) منابع جدول اصلی (جدول_اصلی_ستون1، جدول_اصلی_ستون2، ... جدول_اصلی_ستون N)

    به عنوان مثال، بیایید دو جدول تعریف کنیم و با استفاده از یک کلید خارجی آنها را پیوند دهیم:

    مشتریان جدول ایجاد کنید (شناسه INT PRIMARY KEY IDENTITY، سن INT DEFAULT 18، نام NVARCHAR(20) NOT NULL، LastName NVARCHAR(20) NOT NULL، ایمیل VARCHAR(30) UNIQUE، تلفن VARCHAR(تلفن UNIQUE(220)); CREATE TABLE Orders (ID INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date);

    جداول مشتریان و سفارشات در اینجا تعریف شده است. مشتریان اصلی ترین هستند و نماینده مشتری هستند. سفارشات وابسته است و نشان دهنده سفارش ارسال شده توسط مشتری است. این جدول از طریق ستون CustomerId به جدول Customers و ستون Id آن مرتبط می شود. یعنی ستون CustomerId یک کلید خارجی است که از جدول Customers به ​​ستون Id اشاره می کند.

    تعریف یک کلید خارجی در سطح جدول به صورت زیر است:

    CREATE TABLE Orders (ID INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) References Customers (Id));

    می توانید از عملگر CONSTRAINT برای تعیین نام برای یک محدودیت کلید خارجی استفاده کنید. معمولاً این نام با پیشوند "FK_" شروع می شود:

    CREATE TABLE Orders (ID INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) References Customers (Id));

    در این مورد، محدودیت کلید خارجی CustomerId "FK_Orders_To_Customers" نامیده می شود.

    در حذف و در به روز رسانی

    با استفاده از دستورات ON DELETE و ON UPDATE، می توانید اقداماتی را تنظیم کنید که به ترتیب هنگام حذف یا اصلاح یک ردیف مرتبط از جدول اصلی انجام شوند. و برای تعریف عمل می توانیم از گزینه های زیر استفاده کنیم:

      CASCADE: هنگامی که ردیف های مرتبط در جدول اصلی حذف یا اصلاح می شوند، به طور خودکار ردیف ها را از یک جدول وابسته حذف یا تغییر می دهد.

      NO ACTION: هنگامی که ردیف های مرتبط در جدول اصلی حذف یا اصلاح می شوند، از هر گونه عملی در جدول وابسته جلوگیری می کند. یعنی در واقع هیچ اقدامی وجود ندارد.

      SET NULL: هنگام حذف یک ردیف مرتبط از جدول اصلی، ستون کلید خارجی را NULL قرار می دهد.

      SET DEFAULT: هنگام حذف یک ردیف مرتبط از جدول اصلی، ستون کلید خارجی را به مقدار پیش فرضی که با استفاده از ویژگی DEFAULT تعیین می شود، تنظیم می کند. اگر ستونی دارای مقدار پیش فرض نباشد، از NULL به عنوان مقدار پیش فرض استفاده می شود.

    حذف آبشاری

    به طور پیش فرض، اگر یک ردیف از جدول اصلی توسط یک کلید خارجی توسط هر ردیفی از یک جدول وابسته ارجاع داده شود، نمی توانیم این ردیف را از جدول اصلی حذف کنیم. ابتدا باید تمام سطرهای مرتبط را از جدول وابسته حذف کنیم. و اگر هنگام حذف یک ردیف از جدول اصلی، لازم باشد که تمام سطرهای مرتبط از جدول وابسته حذف شوند، از یک cascade delete استفاده می شود، یعنی گزینه CASCADE:

    ایجاد سفارشات جدول (شناسه INT PRIMARY KEY IDENTITY، CustomerId INT، CreatedAt Date، FOREIGN KEY (CustomerId) ارجاعات مشتریان (شناسه) در DELETE CASCADE)

    عبارت ON UPDATE CASCADE به طور مشابه کار می کند. وقتی مقدار یک کلید اصلی را تغییر می دهید، مقدار کلید خارجی مرتبط با آن به طور خودکار تغییر می کند. اما از آنجایی که کلیدهای اولیه معمولاً به ندرت تغییر می کنند و در اصل استفاده از ستون هایی با مقادیر قابل تغییر به عنوان کلیدهای اصلی توصیه نمی شود ، در عمل از عبارت ON UPDATE به ندرت استفاده می شود.

    تنظیم NULL

    وقتی گزینه SET NULL را برای یک کلید خارجی تنظیم می کنید، ستون کلید خارجی باید NULL مجاز باشد:

    CREATE TABLE Orders (ID INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) References Customers (Id) ON DELETE SET NULL);

    تنظیم مقدار پیش فرض

    ایجاد سفارشات جدول (شناسه INT PRIMARY KEY IDENTITY، CustomerId INT، CreatedAt Date، FOREIGN KEY (CustomerId) ارجاعات مشتریان (شناسه) در DELETE SET پیش فرض)

    کلید خارجیبرای محدودیت لینک استفاده می شود.
    هنگامی که تمام مقادیر یک فیلد جدول در یک فیلد در جدول دیگر نشان داده می شود، گفته می شود که فیلد اول به قسمت دوم اشاره دارد. این نشان دهنده رابطه مستقیم بین مقادیر دو فیلد است.

    هنگامی که یک جنسیت در جدول به جنسیت دیگر اشاره می کند، نامیده می شود کلید خارجی; و فیلدی که به آن اشاره می کند نامیده می شود کلید والد. لازم نیست نام کلید خارجی و کلید والد یکسان باشد. یک کلید خارجی می تواند هر تعداد فیلد داشته باشد که همگی به صورت یک واحد پردازش می شوند. یک کلید خارجی و کلید اصلی که به آن اشاره می‌کند باید شماره فیلد و نوع فیلد یکسانی داشته باشند و به ترتیب یکسان باشند. وقتی یک فیلد یک کلید خارجی است، به نحوی با جدولی که به آن ارجاع می دهد مرتبط است. هر مقدار (هر ردیف) یک کلید خارجی باید به طور واضح به یک و تنها مقدار (ردیف) کلید والد اشاره داشته باشد. اگر این شرط برقرار باشد، دیتابیس در حالت است یکپارچگی ارجاعی.

    SQLیکپارچگی ارجاعی را با محدودیت حفظ می کند کلید خارجی. این تابع باید مقادیری را که می توان در پایگاه داده وارد کرد تا کلید خارجی و کلید والد را مجبور به رعایت یکپارچگی ارجاعی کند، محدود کند. یکی از اعمال محدودیت کلید خارجیحذف مقادیر برای فیلدهایی است که به عنوان یک کلید خارجی محدود شده اند و هنوز در کلید والد نمایش داده نشده اند. این محدودیت همچنین بر توانایی تغییر یا حذف مقادیر کلید والد تأثیر می گذارد

    محدودیت کلید خارجیدر دستور CREATE TABLE (یا ALTER TABLE (با هدف تغییر ساختار جدول)) استفاده می شود که حاوی فیلدی است که به عنوان کلید خارجی اعلام شده است. به کلید والد نامی داده می شود که در محدودیت ارجاع داده می شود. کلید خارجی.

    مانند بسیاری از محدودیت ها، می تواند یک محدودیت جدول یا ستون باشد، به شکل جدولی که اجازه می دهد چندین فیلد به عنوان یک کلید خارجی استفاده شود.

    نحو محدودیت جدول کلید خارجی:

    کلید خارجی منابع

    [ ]

    لیست ستون اول یک لیست جدا شده با کاما از یک یا چند ستون جدول است که با این دستور ایجاد یا اصلاح می شود.

    Pktable- این جدول حاوی کلید والد است. این می تواند جدولی باشد که با دستور فعلی ایجاد یا اصلاح شود.

    لیست ستون دوم لیستی از ستون هایی است که کلید والد را تشکیل می دهند. دو لیست ستون باید با هم سازگار باشند، به عنوان مثال:

    • تعداد ستون های یکسانی دارند
    • در یک دنباله معین، ستون های اول، دوم، سوم، و غیره، ستون های لیست ستون های کلید خارجی باید دارای انواع داده ها و اندازه های مشابه با ستون های اول، دوم، سوم و غیره از فهرست ستون های کلید اصلی باشند.
    • ستون های موجود در لیست هر دو ستون نباید نام یکسانی داشته باشند.

    کلید خارجی مثال 1

    ایجاد جدول دانش آموز
    (عدد صحیح Kod_stud NOT NULL PRIMARY کلید,
    عدد صحیح Kod_spec NOT NULL،

    آدرس ها char(50)،
    توپ اعشاری)،
    کلید خارجی(Kod_spec) منابعمشخصات (Kod_spec)
    );

    هنگام استفاده از ALTER TABLE به جای CREATE TABLE برای اعمال محدودیت کلید خارجی، مقادیر مشخص شده در کلید خارجی و کلید والد باید در حالت یکپارچگی ارجاعی باشند. در غیر این صورت دستور رد خواهد شد.

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

    کلید خارجی مثال 2

    ایجاد جدول دانش آموز (
    Kod_stud عدد صحیح NOT NULL PRIMARY کلید,
    فام char(30) NOT NULL UNIQUE،
    آدرس ها char(50)،
    توپ اعشاری)،
    Kod_spec عدد صحیح مراجع Spec
    );

    حفظ یکپارچگی ارجاعی مستلزم برخی محدودیت‌ها در مقادیری است که می‌توانند در فیلدهایی که به‌عنوان کلید خارجی و کلید والد معرفی می‌شوند، نمایش داده شوند. کلید والد باید طوری ساختار یافته باشد که اطمینان حاصل شود که هر مقدار کلید خارجی با یک ردیف مشخص مطابقت دارد. این بدان معنی است که آن (کلید) باید منحصر به فرد باشد و حاوی مقادیر خالی (NULL) نباشد.

    این برای اینکه کلید والد همان الزامی را که هنگام اعلام یک کلید خارجی برآورده می‌کند، کافی نیست. SQLباید مطمئن شوید که مقادیر دوگانه یا مقادیر تهی به کلید والد وارد نشده اند. بنابراین، باید اطمینان حاصل کنید که تمام فیلدهایی که به عنوان کلیدهای والد استفاده می شوند دارای یک محدودیت اولیه هستند کلیدیا یک محدودیت UNIQUE، مانند محدودیت NOT NULL.

    ارجاع کلیدهای خارجی فقط به کلیدهای اصلی استراتژی خوبی است. هنگامی که از کلیدهای خارجی استفاده می شود، آنها صرفاً با کلیدهای اصلی که به آنها اشاره می کنند مرتبط نیستند. آنها با یک ردیف جدول خاص مرتبط هستند که در آن کلید والد پیدا می شود. خود کلید والد هیچ اطلاعاتی را ارائه نمی دهد که قبلاً در کلید خارجی وجود نداشته باشد.

    از آنجایی که هدف کلید اصلی شناسایی منحصر به فرد بودن یک ردیف است، برای یک کلید خارجی انتخابی منطقی تر و کمتر مبهم است. برای هر کلید خارجی که از یک کلید منحصر به فرد به عنوان کلید اصلی خود استفاده می کند، باید یک کلید خارجی ایجاد کنید که از کلید اصلی همان جدول برای همان جلوه استفاده کند. یک کلید خارجی که هدفی جز پیوند دادن ردیف ها ندارد، شبیه کلید اصلی است که صرفاً برای شناسایی ردیف ها استفاده می شود و وسیله خوبی برای شفاف و ساده نگه داشتن ساختار پایگاه داده است. یک کلید خارجی فقط می تواند حاوی مقادیری باشد که در واقع در کلید والد وجود دارند یا خالی هستند (NULL). هرگونه تلاش برای وارد کردن مقادیر دیگر در این کلید رد خواهد شد.

    کلید خارجی مثال 3

    پرداخت جدول ایجاد کنید (
    sh_payout عدد صحیح،
    sh_eml عدد صحیح،
    date_تاریخ پرداخت،
    sum_payout واقعی،
    کلید خارجی(sh_eml) مراجع k_sotr2 (eid)
    );

    در این مثال کلید خارجیستون sh_eml با ستون eid از جدول k_sotr2 مرتبط است.

    بیشتر بخوانید: