الدرس الثالث عشر محدد المفتاح الأجنبي Foreign Key Constraint

محدد المفتاح الأجنبي يستخدم لإنشاء علاقة بين الجداول. إضافة إلى ذلك يستخدم المفتاح الأجنبي في منع العمليات ( مثلا حذف سطر) التي قد تؤدي إلى كسر او تدمير العلاقة بين الجداول التي تم ربطها بواسطة المفتاح الأجنبي.

لفهم مبدأ المفتاح الأجنبي سوف ناخذ مثال عن جدولين جدول الزبائن وجدول الطلبات.

Customer table

=============

CUST_ID   CUST_NAME   CUST_ADDR

=======    ==========   ==========

CUST01    AHMED ALI      IRAQ  

CUST02    OMER KAML    EYGEPT

CUST03    MANSOUR        UAE

Order Table

==========

ORDER_ID     CUST_ID     DATE      AMOUNT

========     =======     ======   ========

ORD001         CUST01      01-09-18   2000

ORD002         CUST02      02-09-18   1450

ORD003         CUST01      02-09-18    3459

ORD004         CUST03      04-09-18    4000

ان الحقل CUST_ID هو مفتاح أساسي في جدول الزبائن وتمت إضافة هذا الحقل أيضا إلى جدول الطلبيات. من الناحية الوظيفية إضافة حقل CUST_ID إلى جدول الطلبيات هو أمر ضروري حتى نتمكن من معرفة الطلبية تعود إلى اي زبون بالتالي نستطيع تتبع الدفعات التي يجب تحصيلها من الزبون وكذلك تتبع نمط المشتريات لكل زبون.

يمكن اضافة حقل  CUST_ID إلى جدول الطلبيات بطريقتين وكما يلي

الطريقة الاولى: نضيف حقل CUST_ID إلى جدول الطلبيات بدون استخدام محدد المفتاح الأجنبي. في هذه الحالة سوف لن يقوم نظام قواعد البيانات باي عمليات تحقق على القيم التي يتم إدخالها في حقل CUST_ID في جدول الطلبيات. والمقصود بلتحقق مثلا عند ادخل الطلبية سوف المستخدم بإدخال المعلومات باستخدام التطبيق ( والذي قد يكون تطبيق خاص للهواتف الذكية او موقع ويب او تطبيق ويندوز الخ) رقم الطلب ORDER_ID مثلا ORD001 ومعرف الزبونCUST_ID مثلا CUST004 وتاريخ الطلبية ومبلغ الطلبية.اذا لاحظنا قيمة معرف الزبون التي تم أدخلها CUST004 غير موجودة في جدول الزبائن وهذا يعني أن هذا الزبون غير معرف و بالتالي سوف لا نستطيع من من متابعة الدفعات. هنا نظام قاعدة البيانات سوف لا ينبه التطبيق على هذا الخلل وذلك لان نظام قاعدة البيانات لا يعرف بوجود علاقة بين جدول الزبائن و جدول الطلبيات. في هذه الحالة على المبرمج ان يضع قواعد التحقق داخل التطبيق ( والذي قد يكون تطبيق خاص للهواتف الذكية او موقع ويب او تطبيق ويندوز الخ)بحيث يقوم التطبيق بتنيه المستخدم إلى ضرورة القيام بإضافة سجل جديد خاص بCUST04 إلى جدول الزبائن قبل ان يسمح للمستخدم بإضافة طلبية خاصة بهذا الزبون وهذا سوف يحافظ على مبدأ تكامل البيانات. ولكن منطق تكامل البيانات تم بنائه ضمن التطبيق وليس ضمن نظام إدارة قواعد البيانات. 

الطريقة الثانية: هي ان نضيف حقل CUST_ID(وهو عبارة عن مفتاح اساسي في جدول الزبائن)إلى جدول الطلبيات مع استخدام محدد المفتاح الأجنبي وهذا سوف يؤدي إلى بناء علاقة بين الجدول الأساسي وهو في هذا الحالة جدول الزبائن والجدول الفرعي وهو في هذا الحالة جدول الزبائن. يمكن القيام بذلك أثناء عملية تكوين الجدول وكما يلي :

CREATE TABLE ORDERS

(

ORDER_ID VARCHAR(25) PRIMARY KEY,

CUST_ID    VARCHAR(25)FOREIGN KEY REFERENCES CUSTOMER (CUST_ID),

DATE DATETIME,

AMOUNT NUMERIC(19,5)

)

أما إذا كان الجدول موجود اصلا وكان الحقل موجود يمكن اضافة المفتاح كما يلي

ALTER TABLE ORDERS ADD FOREIGN KEY REFERENCES CUSTOMER (CUST_ID)

بعد انشاء العلاقة بين الجدولين سوف يأخذ نظام إدارة قواعد البيانات مسؤلية القيام بعملية عمليات التحقق وبلتالي سوف يمنع المستخدم من القيام بعمليات قد تكسر العلاقة بين الجداول وتخل بتكامل البيانات. مثلا اذا حاول المستخدم إدخال تفاصيل طلبية باستخدام معرف الزبون CUST004 سوف يقوم نظام إدارة قواعد البيانات بمنع عملية إدخال البيانات وسوف يعيد رسالة خطاء إلى التطبيق والذي بدوره سوف فقط يحتاج إلى عرض الرسالة للمستخدم بدون الحاجة إلى وضع شفرات برمجية للتحقق من سلامة البيانات وهذا هو جوهر الاختلاف عن الطريقة الاولى.

هناك نقطة مهمة يجب فهمها عند استخدام المفتاح الأجنبي وهي تتعلق بحذف البيانات. هنا أيضا يقوم نظام إدارة قواعد البيانات بأخذ المبادرة وهناك خياران للتحكم بحذف البيانات وذلك للحفاظ على تكامل البيانات. فعند حذف سجل من الجدول الأساسي وهو جدول الزبائن فكيف سوف يتم التعامل مع قيم المفتاح الأجنبي في الجدول الفرعي وهو جدول الطلبيات؟ هناك خياران وهما :

On Delete Cascade

================

في هذا الخيار سوف يقوم نظام إدارة قواعد البيانات بحذف الأسطر من الجدول الفرعي اذا تم حذف قيم المفتاح الأجنبي من الجدول الأساسي.

لفهم هذا المبدأ سوف نفرض ان المستخدم قد حذف الزبون ذو رقم المعرفCUST02 سوف يقوم النظام بحذف الطلبية رقم ORD002 من جدول الطلبيات وذلك لان  قيمة المفتاح الأجنبي CUST02 تم حذفها من جدول الزبائن. هذا الخيار يعتبر جيد ولكن يجب الحذر عند استخدامه وذلك لأنه قد يؤدي إلى حذف معلومات حساسة من قاعدة البيانات. على سبيل المثال قد يؤدي هذا إلى حذف طلبيات تم شحنها إلى الزبون ولم يقم لحد الان بدفعها. لذا يجب التفكير مليا قبل تفعيل هذا الخيار ويجب اخذ بعين الاعتبار كيف سوف يؤثر على وظائفية التطبيق الذي يستخدم قاعدة البيانات.

On Delete Null

============  

في هذا الخيار سوف يقوم نظام إدارة قواعد البيانات بتحديث قيم المفتاح الأجنبي إلى القيمة الخالية NULL في الجدول الفرعي اذا تم حذف قيم المفتاح الأجنبي من الجدول الأساسي.

لفهم هذا المبدأ سوف نفرض ان المستخدم قد حذف الزبون ذو رقم المعرفCUST02 سوف يقوم النظام بتعديل الطلبية رقم ORD002 من جدول الطلبيات ويستخدم القيمة  NULL ضمن حقل معرف الزبون وذلك لان قيمة المفتاح الأجنبي CUST02 تم حذفها من جدول الزبائن. هذا الخيار يعتبر جيد ولكن يجب الحذر عند استخدامه وذلك لأنه قد يؤدي إلى تعديل معلومات حساسة من قاعدة البيانات. على سبيل المثال قد يؤدي هذا إلى حذف اسم الزبون من طلبيات تم شحنها إلى الزبون ولم يقم لحد الان بدفعها. لذا يجب التفكير مليا قبل تفعيل هذا الخيار ويجب اخذ بعين الاعتبار كيف سوف يؤثر على وظائفية التطبيق الذي يستخدم قاعدة البيانات.

اذا لم نستخدم اي من الخيارات السابقة فأن نظام إدارة قواعد البيانات سوف يمنع عمليات الحذف من الجدول الأساسي(جدول الزبائن) اذا كانت هناك قيم مستخدمة ضمن حقل المفتاح الأجنبي في الجدول الفرعي(جدول الطلبيات).

لذا يجب التفكير مليا قبل القيام لأنشاء علاقات بين الجداول باستخدام محدد المفتاح الأجنبي لان هذا قد يؤدي إلى تعقيدات على مستوى التطبيق وعلى مستوى الوظائف التي يقدمها التطبيق. ان الاختيار بين استخدام محدد المفتاح الأجنبي من عدمه يخضع للعديد من الاعتبارات وأهمها ان يعرف مبرمج قاعدة البيانات التأثير النهائي لهذا الخيار على تصميم التطبيق. لذا في كثير من الاحيان يفضل عدم استخدامه وترك مسؤولية التحقق من تكامل البيانات للتطبيق نفسه بدلا من القيام بذلك على مستوى نظام إدارة قواعد البيانات. لان فيه مرونة أكثر من اختيار استخدام محدد المفتاح الأجنبي.

الدرس الثاني عشر محدد المفتاح الأساسي SQL PRIMARY KEY Constraint

محدد المفتاح الأساسي Primary Key Constraint
هو عنصر مهم جدا في تصميم قواعد البيانات ويلعب دور كبير في بناء تكامل البيانات Data Integrity ويضمن الدقة المطلوبة في عمليات تخزين واسترجاع وتحديث البيانات.
محدد المفتاح الأساسي يجمع خواص اثنين من المحددات هما محدد القيمة الخالية NOT NULL Constraint والذي سوف يضمن ادخال قيم  في الحقل ولا يسمح بأستخدام القيمة الفارغة (NULL Value) في الحقل(لمزيد من التفاصيل يرجى مراجعة الدرس العاشر) والمحدد الثاني هو محدد القيمة الفريدة UNIQUE Constraint والذي سوف يضمن ان تكون جميع القيم في الحقل فريدة اي سوف لا يسمح بتكرار نفس القيمة في هذا الحقل(لمزيد من التفاصيل يرجى مراجعة الدرس الحادي عشر).
تكمن أهمية المفتاح الأساسي في انه يوفر معرف فريد unique identifier لكل سطر من أسطر البيانات في الجدول وبلتالي يمكننا هذا المعرف من الوصول إلى سطر  البيانات للقيام بعمليات التعديل Update او حذف delete سطر البيانات من دون التأثير على الأسطر الاخرى. لشرح هذه النقطة بتفصيل أكثر لنفترض انه لدينا جدول زبائن بسيط يحتوي على اسم الزبون ورقم الهاتف فقط ولا يحتوي هذا الجدول على مفتاح أساسي. الان اذا كان لدينا سطرين فقط في هذا الجدول وكلاهما يحتويان على نفس اسم الزبون مثلا محمد أحمد و السطر الأول رقم الهاتف هو ١٢٣٤٥٦ و السطر الثاني رقم الهاتف هو ٩٨٧٦٥٥. الان اذا رغبنا ان نعدل رقم الهاتف في السطر الثاني فقط يجب أن نستخدم رقم الهاتف في جملة التعديل ولا نستطيع أن نستخدم اسم الزبون:
UPDATE CUTOMER
SET MOBILE ='9999999'
WHERE MOBILE ='987655'
العبارة السابقة سوف تعمل بشكل صحيح على تحديث فقط السطر الثاني وذلك لان رقم الهاتف كان محدد وهو يختلف عن رقم الهاتف في السطر الاول.

الان اذا استخدمنا اسم الزبون في عملية التحديث كما في العبارة التالية
UPDATE CUTOMER
SET MOBILE ='9999999'
WHERE NAME ='محمد أحمد
ما الذي سوف يحصل ؟
هنا سوف يقوم نظام قاعدة البيانات بتغيير رقم الهاتف إلى ٩٩٩٩٩٩ في كلا السطرين الأول والثاني والسبب في ذلك هو أن اسم الزبون مكرر في كلا السطرين لذلك ان عبارة WHERE سوف تعيد سطرين الأول والثاني التالي يقوم النظام بتعديل هذين السطرين. وهذا يعتبر فشل في عملية التحديث لأننا المفروض نحدث سطر واحد فقط ولكن حدثنا سطرين

الان لنفترض أن كلا السطرين يمتلكان نفس القيمة في حقل الاسم أحمد محمد ونفس رقم الهاتف ١٢٣٤٥٦ في كلا السطرين. الان اذا نفذنا اي من عبارات التعديل التالية

UPDATE CUTOMER
SET MOBILE ='999999'
WHERE NAME =' احمد محمد'

او عبارة التعديل

UPDATE CUTOMER
SET MOBILE ='999999'
WHERE MOBILE ='123456'

فإن كلا العبارتين سوف تحدث كلا السطرين ولا توجد أي طريقة لتعديل السطر الثاني فقط دون المساس السطر الأول وبنفس الطريقة سوف لا نستطيع أن نحذف فقط السطر الثاني. وهذا يعتبر خلل في تصميم جداول قاعدة البيانات.