پایگاه داده های رابطه ای پایگاه داده ها کلید خارجی چیست

آخرین به روز رسانی: 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).

آخرین به روز رسانی: 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 پیش فرض)

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

کلید اصلی در پایگاه داده چیست؟

در یک پایگاه داده، کلید اصلی جدول یکی از ستون های آن (کلید اصلی) است. بیایید به یک مثال نگاه کنیم که این شبیه به چه چیزی است. بیایید یک نگرش ساده از دانشجویان دانشگاه را تصور کنیم (بیایید آن را "دانشجویان" بنامیم).

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

کلید اولیه ساده و مرکب

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

نام و نام خانوادگی.	تاریخ تولد	سری پاسپورت	گذرنامه ID
ایوانف پ.آ.	12.05.1996	75	0553009
سرگیف V.T.	14.07.1958	71	4100654
Krasnov L.V.	22.01.2001	73	1265165

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

ارتباط بین روابط

بنابراین، یک کلید اولیه در یک پایگاه داده یک یا چند ستون از یک جدول است که به فرد اجازه می دهد تا یک ردیف را در آن رابطه به طور منحصر به فرد شناسایی کند. این برای چیست؟

به مثال اول با رابطه «دانشجویان» برگردیم. علاوه بر این رابطه، پایگاه داده اطلاعات دیگری را نیز ذخیره می کند، به عنوان مثال، عملکرد هر دانش آموز. برای اینکه تمام اطلاعاتی که قبلاً در پایگاه داده وجود دارد تکرار نشود، از یک کلید با اشاره به رکورد مورد نظر استفاده می کنند. به نظر می رسد این است.

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

کلید طبیعی و جایگزین

کلید اصلی جدول پایگاه داده چگونه تعیین می شود؟ دو مثالی که به آنها نگاه کردیم - "دانشجویان" و "مشتریان بانک" - مفاهیم کلیدهای طبیعی و کلیدهای جایگزین را نشان می دهند. در جدول مشتریان بانک، کلیدی متشکل از فیلدهای «شماره» و «سری پاسپورت» را با استفاده از ستون‌های موجود تعریف کردیم. این کلید طبیعی نامیده می شود. در مورد رابطه "دانشجویان"، هیچ رشته یا ترکیبی از رشته ها به ما منحصر به فرد نمی بخشید. این ما را مجبور کرد که یک قسمت کد دانشجویی اضافی را وارد کنیم. به این کلید کلید جایگزین می گویند که برای آن یک ستون سرویس دیگر به جدول اضافه کردیم. این ستون حاوی اطلاعات مفیدی نیست و فقط برای شناسایی سوابق است.

کلید خارجی و یکپارچگی داده ها در پایگاه داده

همه موارد فوق ما را به یکپارچگی کلید خارجی و پایگاه داده می رساند. کلید خارجی فیلدی است که به کلید اصلی یک رابطه خارجی اشاره دارد. در جدول پیشرفت، اینها ستون های "دانش آموز" و "انضباط" هستند. داده های آنها ما را به جداول خارجی ارجاع می دهد. یعنی فیلد “Student” در رابطه “Performance” یک کلید خارجی و در رابطه “Student” کلید اصلی در پایگاه داده است.

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

گروه‌های دیگری از قوانین یکپارچگی و همچنین سایر محدودیت‌های پایگاه‌داده وجود دارد که باید مورد توجه قرار گیرند و باید توسط توسعه‌دهندگان مورد توجه قرار گیرند.

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

1.2.5. کلید اصلی

ما قبلاً در مورد زمینه های کلیدی زیاد صحبت کرده ایم، اما هرگز از آنها استفاده نکرده ایم. جالب ترین چیز این است که همه چیز کار می کند. این یک مزیت یا شاید یک نقطه ضعف برای پایگاه داده های Microsoft SQL Server و MS Access است. این ترفند در جداول پارادوکس کار نمی کند و بدون فیلد کلید جدول فقط خواندنی خواهد بود.

تا حدی، کلیدها محدودیت هستند و می‌توان آن‌ها را همراه با عبارت CHECK در نظر گرفت، زیرا اعلان به روشی مشابه انجام می‌شود و حتی از دستور CONSTRAINT استفاده می‌کند. بیایید با یک مثال به این روند نگاه کنیم. برای انجام این کار، جدولی از دو فیلد “guid” و “vcName” ایجاد می کنیم. این قسمت "راهنما" را به عنوان کلید اصلی تنظیم می کند:

CREATE TABLE Globally_Unique_Data (شناسه منحصر به فرد راهنما DEFAULT NEWID()، vcName varchar(50)، CONSTRAINT PK_guid PRIMARY KEY (راهنما))

بهترین بخش در اینجا خط CONSTRAINT است. همانطور که می دانیم بعد از این کلمه کلیدی نام محدودیت می آید و اعلان کلید نیز از این قاعده مستثنی نیست. برای نام‌گذاری کلید اصلی، توصیه می‌کنم از نامی مانند PK_name استفاده کنید، جایی که name نام فیلدی است که باید کلید اصلی شود. مخفف PK از کلید اصلی می آید.

پس از این، به جای کلمه کلیدی CHECK که در محدودیت ها استفاده کردیم، یک عملگر PRIMARY KEY وجود دارد که نشان می دهد ما به چک نیاز نداریم، بلکه به کلید اصلی نیاز داریم. یک یا چند فیلد که کلید را تشکیل می دهند در داخل پرانتز مشخص شده اند.

به یاد داشته باشید که هیچ دو ردیفی نمی توانند مقدار یکسانی در یک فیلد کلید داشته باشند، که در آن یک محدودیت کلید اولیه با یک محدودیت منحصر به فرد یکسان است. این بدان معناست که اگر فیلد ذخیره نام خانوادگی را کلید اصلی قرار دهید، امکان نوشتن دو ایوانوف با نام های مختلف در چنین جدولی وجود نخواهد داشت. این محدودیت کلید اصلی را نقض می کند. به همین دلیل است که کلیدها محدودیت هستند و به همان شیوه محدودیت CHECK اعلام می شوند. اما این فقط برای کلیدهای اولیه و کلیدهای ثانویه با منحصر به فرد صادق نیست.

در این مثال، کلید اصلی یک فیلد از نوع یک شناسه (GUID) است. مقدار پیش‌فرض این فیلد نتیجه رویه سرور NEWID است.

توجه

فقط یک کلید اصلی را می توان برای یک جدول ایجاد کرد

برای ساده‌تر شدن مثال‌ها، توصیه می‌شود از یک نوع عددی به عنوان کلید استفاده کنید و اگر پایگاه داده اجازه می‌دهد، بهتر است از نوع «افزایش خودکار» (به‌طور خودکار افزایش/کاهش عدد) باشد. در MS SQL Server این فیلد IDENTITY و در MS Access یک فیلد از نوع "counter" است.

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

محصولات CREATE TABLE (ID int IDENTITY(1، 1)، محصول varchar(50)، قیمت پول، تعداد عددی (10، 2)، CONSTRAINT PK_id PRIMARY KEY (id))

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

یک کلید اصلی می تواند از بیش از یک ستون تشکیل شده باشد. مثال زیر جدولی را ایجاد می کند که در آن فیلدهای "id" و "Product" کلید اصلی را تشکیل می دهند، به این معنی که یک شاخص منحصر به فرد در هر دو فیلد ایجاد می شود:

CREATE TABLE Products1 (ID int IDENTITY(1, 1)، محصول varchar (50)، قیمت پول، مقدار عددی (10، 2)، CONSTRAINT PK_id PRIMARY KEY (ID، [نام محصول]))

اغلب برنامه نویسان یک پایگاه داده با یک فیلد کلیدی به شکل یک عدد صحیح ایجاد می کنند، اما در عین حال وظیفه به وضوح بیان می کند که فیلدهای خاص باید منحصر به فرد باشند. چرا بلافاصله یک کلید اولیه از آن فیلدها ایجاد نمی شود که باید منحصر به فرد باشد و نیازی به ایجاد راه حل های جداگانه برای این مشکل نخواهد بود.

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

1.2.6. کلید خارجی

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

• نام ها - شامل نام افراد است و شامل فیلدهای شناسه (فیلد کلید)، نام است.
• Phones یک جدول تلفن است که از یک شناسه (فیلد کلید)، یک کلید خارجی برای اتصال به جدول نام ها و یک فیلد رشته برای ذخیره شماره تلفن تشکیل شده است.

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

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

جدول 1.3 محتویات جدول نامها

جدول 1.4. محتویات جدول تلفن ها

جدول 1.4 شامل پنج شماره تلفن است. فیلد اصلی کلید همچنین حاوی یک کلید اصلی منحصر به فرد است که می تواند به طور خودکار افزایش یابد. یک کلید ثانویه یک رابطه با کلید اصلی جدول نام ها است. این اتصال چگونه کار می کند؟ پتروف شماره 1 را به عنوان کلید اصلی در جدول نام ها دارد. در مورد بقیه ورودی ها هم همینطور. به صورت بصری اتصال را می توان در شکل 1.5 مشاهده کرد.

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

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

شما می توانید یک ردیف جداگانه با نام خانوادگی برای هر تلفن در جدول نام ها ایجاد کنید، اما این فقط برای یک مثال ساده آسان است، زمانی که فقط باید نام خانوادگی را وارد کنید و به راحتی می توانید چندین ورودی برای Petrov با چندین تلفن ایجاد کنید. شماره. اگر 10 یا 20 فیلد وجود داشته باشد چطور؟ بنابراین، ایجاد دو جدول مرتبط با یک کلید خارجی را می توان در لیست 1.6 مشاهده کرد.

لیست 1.6. ایجاد جداول مرتبط با یک کلید خارجی

CREATE TABLE Names (idName int IDENTITY(1,1)، vcName varchar(50)، CONSTRAINT PK_guid PRIMARY KEY (idName)،) CREATE TABLE Phones (idPhone int IDENTITY(1,1)، idName int, vcPhone(10) کلید اصلی محدودیت PK_idPhone (idPhone)، محدودیت FK_idName کلید خارجی (idName) مراجع نامها (idName))

لطفا محتویات لیست را با دقت بررسی کنید. این بسیار جالب است زیرا از برخی از اپراتورهایی که قبلاً توضیح داده ایم استفاده می کند و یک مثال اضافی مفید خواهد بود. برای هر دو جدول، یک فیلد کلید ایجاد می‌شود که اول می‌آید، از نوع int است و به طور خودکار افزایش می‌یابد و از 1 در افزایش یک شروع می‌شود. فیلد کلید با استفاده از یک محدودیت CONSTRAINT به کلید اصلی تبدیل می شود.

در توضیحات جدول Phones، خط آخر حاوی یک اعلان جدید برای ما است، یعنی اعلان یک کلید خارجی با استفاده از عملگر FOREIGN KEY. همانطور که می بینید، این نیز یک محدودیت است و کمی بعد خواهید دید که چرا. فیلد جدولی که باید به جدول دیگری پیوند داده شود در داخل پرانتز نشان داده شده است. پس از این کلمه کلیدی REFERENCES (پیوند)، نام جدولی که باید با آن ارتباط برقرار شود (Names) و در پرانتز نام فیلد ("idName") می آید. بنابراین، ما یک اتصال ایجاد کرده ایم که در شکل 1.4 نشان داده شده است.

توجه!

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

حالا اگر می توانید جداول را با داده پر کنید. سه تیم بعدی سه نامی را که در جدول 1.3 دیدیم اضافه می کنند:

INSERT INTO Names(vcName) VALUES("Petrov") INSERT INTO Names(vcName) VALUES("Ivanov") INSERT INTO Names(vcName) VALUES("Sidorov")

اگر قبلاً با SQL کار کرده اید، می توانید ورودی هایی را برای جدول تلفن اضافه کنید. من این دستورات را حذف می کنم، اما می توانید آنها را در فایل Foreign_keys.sql در فهرست Chapter1 روی سی دی ببینید.

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

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

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

حذف خود جداول نیز باید با جدول فرعی یعنی Phones شروع شود و تنها پس از آن می توانید جدول اصلی Names را حذف کنید.

در نهایت، من به شما نشان خواهم داد که چگونه به زیبایی بین نام و شماره تلفن از دو جدول مطابقت داشته باشید:

SELECT vcName، vcPhone FROM Names، Phones WHERE Names.idName=Phones.idName

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

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

خود میز همچنین می تواند حداکثر 253 کلید خارجی داشته باشد. کلیدهای خارجی در یک جدول کمتر رایج هستند، معمولاً بیش از 3 عدد نیستند. اغلب، یک جدول می تواند پیوندهای زیادی به جداول دیگر داشته باشد.

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

جدول 1.5. جدول با لینک داخلی

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

بیایید ببینیم چگونه می توانیم همه اینها را به عنوان یک پرس و جوی SQL ایجاد کنیم:

CREATE TABLE Positions (idPosition int IDENTITY(1,1)، idParentPosition int، vcName varchar(30)، CONSTRAINT PK_idPosition PRIMARY KEY (idPosition)، CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPoition (idParentPoitions)

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

رابطه یک به یک

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

مثال زیر دو جدول را ایجاد می کند که یک رابطه کلید اصلی دارند:

CREATE TABLE Names (idName uniqueidentifier DEFAULT NEWID()، vcName varchar(50)، CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID()، vcPhone varchar(50) محدودیت FK_idPhone کلید خارجی (idPhone) مراجع نامها (idName))

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

بسیاری از بسیاری

پیچیده ترین رابطه چند به چند است، که در آن بسیاری از رکوردهای یک جدول با بسیاری از رکوردهای جدول دیگر مطابقت دارند. برای اجرای این، دو جدول کافی نیست.

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

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

جداول 1.6 و 1.7 به ترتیب نمونه هایی از جدول نام خانوادگی و تلفن را نشان می دهد. و جدول 1.8 جدول پیوند را نشان می دهد.

جدول 1.6. جدول نام خانوادگی

جدول 1.7. میز تلفن

جدول 1.8. میز تلفن

حال بیایید ببینیم منطق جستجوی داده در رابطه چند به چند چگونه خواهد بود. بیایید بگوییم که ما باید تمام تلفن های متعلق به ایوانف را پیدا کنیم. کلید اصلی ایوانف برابر با 1 است. ما در جدول پیوندی همه رکوردهایی را پیدا می کنیم که فیلد "رابطه با نام" برای آنها برابر با 1 است. اینها رکوردهای 1 و 2 خواهند بود. در این رکوردها در قسمت "رابطه با تلفن" وجود دارد. به ترتیب شناسه های 1 و 2 هستند و این بدان معنی است که ایوانوف مالک شماره های میز تلفن است که در خطوط 1 و 2 قرار دارند.

حالا بیایید مشکل معکوس را حل کنیم - تعیین کنیم چه کسی به شماره تلفن 567575677 دسترسی دارد. این شماره در جدول تلفن دارای کلید 3 است. ما به دنبال همه رکوردها در جدول پیوند هستیم که در قسمت "اتصال تلفن" برابر است با 3. اینها رکوردهایی با اعداد 4 و 5 هستند که در قسمت "پیوند نام" به ترتیب دارای مقادیر 2 و 3 هستند. اگر اکنون به جدول نام خانوادگی نگاه کنید، پتروف و سیدوروف را در شماره های 2 و 3 خواهید دید. یعنی این دو ساکن از شماره تلفن 567575677 استفاده می کنند.

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

ایجاد نام جدول (idname iniesiredientifier پیش فرض NewId () ، vcname varchar (50) ، محدودیت pk_guid کلید اصلی (idname)) ایجاد تلفن های جدول (idphone iniesiredientifier پیش فرض جدید () ، vcphone varchar (10) ، محدودیت کلید اصلی pk_idphone (idphone)) CREATE TABLE LinkTable (idLinkTable منحصر به فرد DEFAULT NEWID()، شناسه منحصر به فرد idName، شناسه منحصر به فرد idPhone، کلید اصلی محدودیت PK_idLinkTable (idLinkTable)، CONSTRAINT FK_idPhone FOREIGN KEY (idPFENSREDNH) من کلید خارجی (idName REF) ERENCES نامها (idName ))

جدول پیوند دارای دو کلید خارجی است که به نام ها و جداول تلفن پیوند می خورد و یک کلید اصلی که منحصر به فرد بودن رکوردها را تضمین می کند.

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

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

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

در این مقاله سعی می کنیم همه چیز مربوط به کلیدها را در نظر بگیریم SQL:ایجاد و محدودیت کلیدها برای چیست؟ به طور کلی: خسته کننده خواهد بود 😉

برنامه امروز این است:

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

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

کلید اصلی

ستونی که باید در پایگاه داده منحصر به فرد باشد با کلید اصلی مشخص می شود. کلید اصلی یا کلید اصلی به این معنی است که مقدار ستون کلید اصلی را نمی توان در جدول تکرار کرد. بنابراین، این کلید به شما این امکان را می دهد تا بدون ترس از تکرار مقدار ستون، یک رکورد را در جدول به طور منحصر به فرد شناسایی کنید. فقط یک مثال: فرض کنید یک جدول کاربران دارید. این جدول دارای فیلدهای: نام کامل، سال تولد، تلفن است. چگونه یک کاربر را شناسایی کنیم؟ پارامترهایی مانند نام کامل و شماره تلفن قابل اعتماد نیستند. از این گذشته، ما می توانیم چندین کاربر نه تنها با نام خانوادگی یکسان، بلکه با یک نام داشته باشیم. ممکن است شماره تلفن در طول زمان تغییر کند و کاربر دارای شماره تلفن ممکن است شخصی در پایگاه داده ما نباشد.

به همین دلیل کلید اصلی اختراع شد. زمانی که یک شناسه منحصر به فرد اختصاص داده شد و تمام. که در mySqlکه تمام مثال های میدانی را بر روی نمونه آن اجرا می کنیم افزایش خودکارنمی توان تنظیم کرد مگر اینکه نشان دهید که این یک کلید اصلی است.

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

کلید خارجی ( کلید خارجی)

آیا مقدار بیشتری وجود دارد کلید خارجی (کلید خارجی). به آن مرجع نیز می گویند. برای پیوند جداول به یکدیگر لازم است.

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

ستون source_idبرای هر ورودی منحصر به فرد خواهد بود. مقدار آن در ستون ظاهر می شود ارائه دهندهدر جدول کفش.پیشنهاد می کنم فوراً به نمونه ای از نحوه ایجاد یک کلید خارجی نگاه کنید.

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

ایجاد کفش های جدول (shoes_id int auto_increment کلید اولیه، متن عنوان، اندازه int، قیمت شناور، تعداد int، نوع varchar(30)، تامین کننده int، کلید خارجی (تامین کننده) مرجع مرجع تامین کننده (supplier_id));

همانطور که در مثال بالا می بینید، نحو برای ایجاد یک کلید خارجی بسیار ساده است. شما باید یک فیلد را به جدول اضافه کنید و سپس این فیلد را به عنوان یک کلید خارجی اعلام کنید و مشخص کنید که به کجا اشاره می کند. در این مورد، میدان تامین کنندهبه میدان مراجعه خواهد کرد source_idدر جدول تامین کننده

کلید ترکیبی (کلید ترکیبی)

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

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

ایجاد تست جدول (فیلد_1 int، فیلد_2 متن، فیلد_3 بیگین، کلید اصلی (فیلد_1، فیلد_3))؛

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

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