5. روابط بین جداول

5.1. کلیدهای خارجی

یک پایگاه داده رابطه‌ای مجموعه‌ای از جداول است که توسط روابط به یکدیگر متصل شده‌اند. بیایید مثالی را بر اساس جدول قبلی [BIBLIO] در نظر بگیریم که ساختار زیر را داشت:

نمونه‌ای از محتوا به شرح زیر بود:

ممکن است بخواهیم اطلاعاتی درباره نویسندگان مختلف این آثار داشته باشیم، برای مثال nom و prénom آنها، تاریخ تولدشان، و nationalité آنها. بیایید چنین جدولی ایجاد کنیم. روی [DBBIBLIO / Tables] کلیک راست کرده و سپس گزینه [New Table] را انتخاب کنید:

اکنون بیایید جدول زیر را ایجاد کنیم: [AUTEURS]:

`id`	کلید اصلی جدول – برای شناسایی منحصربه‌فرد یک سطر استفاده می‌شود
`nom`	نام نویسنده
`prénom`	نام کوچک نویسنده، در صورت وجود
`date_naissance`	تاریخ تولد
`nationalite`	کشور مبدأ آنها

محتویات جدول [AUTEURS] می‌تواند به شرح زیر باشد:

بیایید به جدول [BIBLIO] و محتویات آن بازگردیم:

در ستون [AUTEUR] جدول، دیگر نیازی به درج نام نویسنده نیست. در عوض، ترجیح داده می‌شود شناسه تخصیص‌یافته به آنها در جدول [AUTEURS] درج شود. بنابراین بیایید یک جدول جدید به نام [LIVRES] ایجاد کنیم. برای ایجاد آن، از اسکریپت [biblio.sql] که در بخش 3.14 ایجاد شده است، استفاده خواهیم کرد. این اسکریپت را با ابزار [Script Executive, Ctrl-F12] بارگذاری می‌کنیم:

ما اسکریپت ایجاد جدول BIBLIO را اصلاح می‌کنیم تا آن را با اسکریپت جدول LIVRES هماهنگ سازیم:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     محدودیت‌های یکتا ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           کلیدهای اصلی ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

ما فقط در مورد تغییرات توضیح خواهیم داد:

خط ۴: فیلد [AUTEUR] در جدول یک عدد صحیح می‌شود. این عدد به یکی از نویسندگان در جدول [AUTEURS] که قبلاً ایجاد شده است، اشاره دارد.
خطوط ۱۱–۱۹: نام نویسندگان با شماره نویسندگانشان جایگزین شده است.
خط ۲۹: نام قید تغییر کرده است. قبلاً [ UNQ1_BIBLIO ] نامیده می‌شد. اکنون [ UNQ1_LIVRES ] نامیده می‌شود. این نام می‌تواند هر چیزی باشد. با این حال، ترجیح داده می‌شود که معنادار باشد. در این مورد، چنین تلاشی صورت نگرفته است. محدودیت‌ها روی فیلدها و جداول مختلف در یک پایگاه داده باید با نام‌های متفاوت از هم متمایز شوند. به یاد داشته باشید که محدودیت در خط ۲۹ نیاز دارد که یک عنوان در داخل جدول یکتا باشد.
خط ۳۶: تغییر نام قید بر روی کلید اصلی به ID.

بیایید این اسکریپت را اجرا کنیم. اگر با موفقیت اجرا شود، جدول جدید زیر با نام [LIVRES] ایجاد خواهد شد:

شاید این سؤال پیش بیاید که آیا ما واقعاً از این کار سودی برده‌ایم یا خیر. در واقع، جدول [LIVRES] حاوی شماره نویسندگان به جای نام آن‌هاست. از آنجایی که هزاران نویسنده وجود دارد، به نظر می‌رسد برقراری ارتباط بین یک کتاب و نویسنده آن دشوار باشد. خوشبختانه، زبان SQL برای کمک به ما وجود دارد. این زبان به ما امکان می‌دهد تا به طور همزمان چندین جدول را جستجو کنیم. به عنوان مثال، پرس‌وجوی SQL را ارائه می‌دهیم که به ما امکان بازیابی عناوین کتاب‌های کتابخانه را به همراه اطلاعات مربوط به نویسندگانشان می‌دهد. بیایید از ویرایشگر SQL (F12) برای صدور فرمان زیر استفاده کنیم: SQL:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

هنوز برای توضیح این دستور SQL خیلی زود است. به زودی به آن باز خواهیم گشت. نتیجه این پرس‌وجو به شرح زیر است:

هر کتاب به‌درستی به نویسندهٔ خود و اطلاعات مرتبط با آن پیوند داده شده است.

بیایید خلاصه‌ای از آنچه همین حالا انجام دادیم ارائه دهیم:

ما دو جدول داریم که حاوی انواع مختلفی از اطلاعات هستند:
- جدول AUTEURS حاوی اطلاعات مربوط به نویسندگان است
- جدول LIVRES حاوی اطلاعات مربوط به کتاب‌های خریداری‌شده توسط کتابخانه است
این جداول به یکدیگر مرتبط هستند. یک کتاب باید یک نویسنده داشته باشد. حتی ممکن است چندین نویسنده داشته باشد. این سناریو در اینجا در نظر گرفته نشده است. ستون [AUTEUR] در جدول [LIVRES] به یک سطر در جدول [AUTEURS] ارجاع می‌دهد. این به عنوان یک رابطه شناخته می‌شود.

رابطه‌ای که جدول [LIVRES] را به جدول [AUTEURS] متصل می‌کند، در واقع نوعی محدودیت است: یک سطر در جدول [LIVRES] باید همیشه یک شناسهٔ نویسنده داشته باشد که در جدول [AUTEURS] وجود داشته باشد. اگر یک سطر در [LIVRES] دارای شناسه مؤلفی بود که در جدول [AUTEURS] وجود نداشت، در یک وضعیت غیرعادی قرار می‌گرفتیم که در آن قادر به شناسایی مؤلف یک کتاب نبودیم.

جدول SGBD قادر است تضمین کند که این قید همیشه برقرار باشد. برای دستیابی به این هدف، ما یک قید به جدول [LIVRES] اضافه خواهیم کرد:

رابطه‌ای که ستون [AUTEUR] در جدول [LIVRES] را به فیلد [ID] در جدول [AUTEURS] متصل می‌کند، رابطه کلید خارجی نامیده می‌شود. ستون [AUTEUR] در جدول [LIVRES] در ویزارد بالا به عنوان «کلید خارجی» نامیده می‌شود. تعریف یک کلید خارجی به این معناست که مقدار موجود در ستون [c1] از جدول [T1] باید در ستون [c2] از جدول [T2] وجود داشته باشد. ستون [c1] به‌عنوان «کلید خارجی» از جدول T1 به ستون [c2] در جدول [T2] اشاره می‌شود. ستون [c2] اغلب کلید اصلی جدول [T2] است، اما این امر اجباری نیست.

ما کلید خارجی [AUTEUR] را در جدول [LIVRES] بر روی فیلد [ID] در جدول [AUTEURS] به شرح زیر تعریف می‌کنیم:

نام قید: آزاد
ستون «کلید خارجی»، در این مورد ستون [AUTEUR] در جدول [LIVRES]
جدولی که توسط کلید خارجی ارجاع داده شده است. در اینجا، ستون [AUTEUR] در جدول [LIVRES] باید دارای مقداری در ستون [ID] در جدول [AUTEURS] باشد. بنابراین جدول [AUTEURS] است که ارجاع داده شده است.
ستونی که توسط کلید خارجی ارجاع شده است. در این مورد، ستون [ID] در جدول [AUTEURS].

ما این قید را اعتبارسنجی می‌کنیم:

اگر همه چیز خوب پیش برود، پذیرفته می‌شود:

اثر این قید کلید خارجی جدید چیست؟ با استفاده از ویرایشگر SQL (F12)، بیایید سعی کنیم یک سطر را با شناسه نویسنده غیرموجود در جدول LIVRES درج کنیم:

عملیات [INSERT] بالا سعی کرد یک کتاب با شناسه نویسنده غیرموجود (100) را درج کند. پرس‌وجو ناموفق بود. پیام خطای مربوطه نشان می‌دهد که نقض محدودیت کلید خارجی «FK_LIVRES_AUTEURS» رخ داده است. این همان محدودیتی است که ما همین‌الان تعریف کرده‌ایم.

5.2. عملیات الحاق بین دو جدول

هنوز در پایگاه داده [DBBIBLIO] (یا هر پایگاه داده دیگری، فرقی نمی‌کند)، بیایید دو جدول آزمایشی به نام‌های TA و TB ایجاد کنیم که به شرح زیر تعریف شده‌اند:

جدول TA

- ID: کلید اصلی جدول TA

- DATA: هر داده‌ای

جدول TB

- ID: کلید اصلی جدول TB

- IDTA: کلید خارجی جدول TB، که به ستون ID در جدول TA ارجاع می‌دهد. بنابراین، یک مقدار از ستون IDTA در جدول TA باید در ستون ID از جدول TA وجود داشته باشد.

- VALEUR: هر داده‌ای

در ویرایشگر SQL (F12)، ما دستورات SQL را صادر خواهیم کرد که همزمان از هر دو جدول TA و TB استفاده می‌کنند.

SQL>select * from TA,  TB

دستور SQL، پس از کلمه کلیدی FROM، شامل دو جدول TA و TB است. عملیات FROM TA، TB منجر به ایجاد موقت یک جدول جدید می‌شود که در آن هر سطر از جدول TA به هر یک از سطرهای جدول TB پیوند داده می‌شود. بنابراین، اگر جدول TA دارای NA سطر و جدول TB دارای NB سطر باشد، جدول حاصل دارای NA × NB سطر خواهد بود. این موضوع در اسکرین‌شات بالا نشان داده شده است. علاوه بر این، هر سطر شامل ستون‌های هر دو جدول است. ستون‌های مشخص‌شده در دستور [SELECT col1, col2, ... FROM ...] نشان می‌دهند که کدام ستون‌ها باید حفظ شوند. در اینجا، کلمه کلیدی * نشان می‌دهد که تمام ستون‌های جدول حاصل مورد نیاز هستند. گاهی گفته می‌شود که جدول حاصل از دستور قبلی SQL، حاصلضرب کارتزین جدول‌های TA و TB است.

در بالا، هر سطر از جدول TA با هر سطر از جدول TB مطابقت داده شده است. به‌طور کلی، مایل هستیم ردیف‌های جدول TB را که با یک ردیف در جدول TA رابطه دارند، به آن ردیف مرتبط سازیم. این رابطه اغلب به شکل یک قید کلید خارجی (foreign key constraint) است. در اینجا نیز همین‌طور است. یک سطر در جدول TA می‌تواند با سطرهای جدول TB که رابطه TB.IDTA=TA.ID را برآورده می‌کنند، مرتبط شود. راه‌های مختلفی برای پرس‌وجوی این موضوع وجود دارد:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

فرمان قبلی SQL مشابه مورد بالا است، اما با دو تفاوت:

رده‌هایی که از حاصل ضرب کارتزین TA و TB به دست می‌آیند، توسط یک عبارت WHERE که یک ردیف از جدول TA را مرتبط می‌سازد، فیلتر می‌شوند، فقط آن سطرهایی از جدول TB که رابطه TB.IDTA=TA.ID را برآورده می‌کنند
فقط ستون‌های خاصی با استفاده از نحو [T.col] درخواست می‌شوند، که در آن T نام یک جدول و col نام یک ستون در آن جدول است. این نحو هرگونه ابهامی را که ممکن است در صورت وجود دو جدول با ستون‌هایی به نام یکسان پیش بیاید، برطرف می‌کند. در مواردی که چنین ابهامی وجود ندارد، می‌توان از سینتکس [col] بدون مشخص کردن جدول برای آن ستون استفاده کرد.

نتیجهٔ حاصل به شرح زیر است:

می‌توان به نتیجه مشابه با دستور زیر دست یافت: SQL

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

اصطلاح [inner join] نام «inner join» را برای این نوع عملیات بین دو جدول به وجود آورده است. خواهیم دید که «outer join» نیز وجود دارد. در یک inner join، ترتیب جدول‌ها در پرس‌وجو هیچ تأثیری بر نتیجه ندارد: FROM TA inner join TB معادل FROM TB inner join TA.

پرس‌وجوی قبلی SQL در مجموعه نتایج تنها آن سطرهایی از جدول TA را شامل می‌شود که توسط حداقل یک سطر از جدول TB ارجاع شده‌اند. بنابراین، سطر مربوط به TA، [3, data3]، در نتیجه ظاهر نمی‌شود زیرا توسط هیچ سطری از TB ارجاع نشده است. ممکن است بخواهید تمام سطرهای جدول TA را بازیابی کنید، صرف‌نظر از اینکه آیا توسط سطری در جدول TB ارجاع شده‌اند یا خیر. در این صورت، از یک پیوند بیرونی بین این دو جدول استفاده می‌شود:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

این یک «پیوند خارجی سمت چپ» است. برای درک اصطلاح «FROM TA left outer join TB»، باید یک پیوست با جدول TA در سمت چپ و جدول TB در سمت راست را تصور کنید. تمام ردیف‌های جدول سمت چپ در نتیجه یک پیوند خارجی سمت چپ ظاهر می‌شوند، حتی آنهایی که شرط پیوند برایشان برقرار نیست. این شرط پیوند لزوماً یک قید کلید خارجی نیست، هرچند این رایج‌ترین حالت است.

به ترتیب زیر:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

جدول TB در پیوست خارجی در سمت «چپ» قرار دارد. بنابراین، ما تمام ردیف‌های جدول TB را در نتیجه خواهیم یافت:

برخلاف پیوند داخلی، ترتیب جداول در اینجا اهمیت دارد. پیوندهای بیرونی راست نیز وجود دارند:

FROM TA LEFT OUTER JOIN TB معادل FROM TB RIGHT OUTER JOIN QZXW2HTMLP000 است.821ZQX: جدول TA در سمت چپ قرار دارد
FROM TB LEFT OUTER JOIN TA معادل FROM TA RIGHT OUTER JOIN QZXW2HTMLP000 است.828ZQX: جدول TB در سمت چپ قرار دارد

اکنون که مبانی پرس‌وجوی همزمان چندین جدول را پوشش دادیم، می‌توانیم به پرس‌وجوهای پیچیده‌تر پایگاه داده بپردازیم.