المفاتيح والعلاقات

لماذا يهم

المفاتيح هي الطريقة التي يعثر بها التطبيق على بياناته من جديد. فعميلان بالمعرّف نفسه يعنيان أن موظف الدعم سيفتح الحساب الخاطئ؛ والطلب الذي يشير إلى عميل محذوف يُعطّل سجل الطلبات؛ وقاعدة حذف لم يطلبها أحد تمحو الفواتير مع عميلها. قد تبدو الشاشة سليمة بينما البيانات المخزّنة خاطئة فعلاً، ثم يظهر الإخفاق لاحقاً في تقرير أو ملف تصدير أو خدمة أخرى.

وبالنسبة إلى المختبِر، كل مفتاح أجنبي قاعدةٌ لها اختبار إيجابي (يُقبل المرجع الصحيح) واختبارات سلبية (يُرفض المرجع إلى أب غير موجود، وحذف الأب ينفّذ ما ينص عليه المتطلب).

المفتاح الأساسي: صف واحد وهوية واحدة

المفتاح الأساسي (Primary key أو PK) عمود، أو مجموعة أعمدة، قيمته فريدة في كل صف ولا تكون NULL أبداً. لكل جدول مفتاح أساسي واحد على الأكثر، وترفض قاعدة البيانات الصف الذي يكون مفتاحه NULL أو مستخدماً في صف آخر. والمفتاح الأساسي المكوّن من عدة أعمدة يسمى المفتاح المركّب (Composite key).

المفتاح الطبيعي (Natural key) مفتاح له معنى في العالم الحقيقي أصلاً، مثل رمز المنتج SKU. أما المفتاح البديل (Surrogate key) فلا معنى تجارياً له: عدد صحيح يتزايد تلقائياً، أو معرّف UUID، وهو معرّف بطول 128 بت يستطيع التطبيق توليده دون أن يطلب من قاعدة البيانات الرقم التالي. وكثيراً ما تستخدم الفرق مفتاحاً أساسياً بديلاً، وتضع على المعرّف الطبيعي قيد UNIQUE الذي يرفض أي قيمة يحملها صف آخر من قبل، لأن المعرّفات الحقيقية مثل البريد الإلكتروني تتغير.

• التفرّد: يُرفض عميل ثانٍ ببريد إلكتروني مستخدم من قبل، برسالة واضحة لا بخطأ في الخادم.
• الثبات: العميل الذي يغيّر بريده يحتفظ بقيمة id نفسها، فتبقى طلباته مشيرة إليه.
• التوليد: لا تتصادم المفاتيح عند إنشاء سجلات بالتوازي، ولا بعد استيراد كتب معرّفاته بنفسه. فمثل هذا الاستيراد قد يترك عدّاد قاعدة البيانات متأخراً، فيحصل السجل الجديد التالي على معرّف مستخدم من قبل.
• الانكشاف: المعرّفات المتسلسلة سهلة التخمين، لذا فتغيير الرقم في رابط مثل /orders/1043 للوصول إلى طلب عميل آخر هو اختبار للتحكم في الوصول. ومعرّف UUID أصعب تخميناً، لكنه لا يغني عن هذا الفحص.

جرّبها: مولّد UUID

المفتاح الأجنبي والسلامة المرجعية

المفتاح الأجنبي (Foreign key أو FK) عمود، أو مجموعة أعمدة، في جدول ابن (Child table) يجب أن تطابق قيمه المفتاح الأساسي، أو مفتاحاً فريداً آخر، في جدول أب (Parent table). ففي orders.customer_id REFERENCES customers (id) يكون orders هو الابن. وتتحقق السلامة المرجعية حين تطابق كل قيمة ليست NULL في المفتاح الأجنبي صفاً موجوداً في الأب؛ والصف الابن الذي يخالف ذلك صف يتيم (Orphan row).

عند التصريح بالمفتاح الأجنبي ترفض قاعدة البيانات طلباً لعميل غير موجود. وحذف أب ما زال له أبناء يُطلق الإجراء المرجعي (Referential action) المكتوب في المخطط. واختيار هذا الإجراء قرار يخص العمل، فأكّده مع مالك المنتج ثم اختبره:

• NO ACTION (الافتراضي) أو RESTRICT: يُرفض الحذف، ولا يختلفان إلا في توقيت تنفيذ الفحص.
• CASCADE: يُحذف الأبناء مع الأب.
• SET NULL: يبقى الأبناء وتصبح قيمة مفتاحهم الأجنبي NULL، لذا يجب أن يقبل العمود NULL.
• المفتاح الأجنبي الذي قيمته NULL يجتاز الفحص ما لم يكن العمود NOT NULL: وهذا صحيح في علاقة اختيارية، وعيب في علاقة إلزامية.
• التصريح لا يعني الفرض دائماً: لا يفحص SQLite المفاتيح الأجنبية افتراضياً إلا بعد تنفيذ PRAGMA foreign_keys = ON على كل اتصال، ولا يمتد أي مفتاح أجنبي إلى خدمة أخرى. وهناك لا يحمي السلامة إلا التطبيق.

واحد إلى واحد، وواحد إلى متعدد، ومتعدد إلى متعدد

التعددية (Cardinality) تصف كم صفاً في طرف يرتبط بكم صفاً في الطرف الآخر. والأنواع الثلاثة كلها تُبنى بالمفتاحين أنفسهما؛ ولا يتغير إلا موضع المفتاح الأجنبي والقيد المفروض عليه.

• واحد إلى متعدد (One-to-many، ⁦1:N⁩): للعميل الواحد طلبات كثيرة. يقع المفتاح الأجنبي في جانب «المتعدد»، أي orders.customer_id، وقد لا يكون للأب أي ابن.
• واحد إلى واحد (One-to-one، ⁦1:1⁩): للعميل بطاقة ولاء واحدة على الأكثر. والمفتاح الأجنبي هنا عليه قيد UNIQUE أيضاً، فتُرفض بطاقة ثانية للعميل نفسه.
• متعدد إلى متعدد (Many-to-many، ⁦N:M⁩): الطلب فيه منتجات كثيرة والمنتج يظهر في طلبات كثيرة. فيخزّن جدول وسيط (Junction table) مثل order_items صفاً لكل زوج، مع مفتاح أجنبي إلى كل طرف؛ ويمنع مفتاح أساسي مركّب على الزوج تكراره.
• لا يستطيع أي مفتاح أجنبي أن يُلزم الأب بأن يكون له أبناء: فقاعدة «لكل طلب سطر واحد على الأقل» مسؤولية التطبيق، والطلب الفارغ عيب كلاسيكي يستحق البحث عنه.

مثال تطبيقي: اختبار مفاتيح متجر صغير

هذا المخطط جزء من أساس الاختبار (Test basis) لمتجر إلكتروني: كل عبارة REFERENCES علاقة، وكل عبارة ON DELETE، أو غيابها، قاعدة. ويؤكد مالك المنتج أن العميل الذي له طلبات لا يُحذف، وأن حذف الطلب يحذف أسطره، وأن المنتج الذي طُلب من قبل لا يُحذف. نفّذ كل إجراء عبر واجهة المستخدم أو الواجهة البرمجية (API)، ثم تحقّق من الصفوف المخزّنة.

• طلب للعميل الموجود ⁦42⁩: مقبول، ويُخزَّن وفيه customer_id بقيمة ⁦42⁩.
• طلب للعميل ⁦999⁩ غير الموجود، مُرسَل عبر الواجهة البرمجية: مرفوض برسالة تشرح الخطأ، لا بخطأ في الخادم، ولا يُخزَّن شيء.
• حذف العميل ⁦42⁩ وله طلبات: مرفوض (RESTRICT).
• بطاقة ولاء ثانية للعميل ⁦42⁩: مرفوضة بقيد UNIQUE على المفتاح الأجنبي.
• المنتج نفسه مرتين في طلب واحد: مرفوض بالمفتاح المركّب، أو مدموج في سطر واحد إن نصّ المتطلب على ذلك.
• حذف طلب: تُحذف صفوفه في order_items معه (CASCADE).
• حذف منتج سبق طلبه: مرفوض (NO ACTION).
• حذف عميل له بطاقة ولاء وليست له طلبات: مرفوض أيضاً، لأن الجدول loyalty_cards بلا قاعدة حذف (NO ACTION). لم ينصّ أحد على هذه القاعدة، فاسأل مالك المنتج هل هي مقصودة.

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  email       VARCHAR(254) NOT NULL UNIQUE,
  full_name   VARCHAR(100) NOT NULL
);

CREATE TABLE loyalty_cards (                 -- 1:1 with customers
  card_no     CHAR(10) PRIMARY KEY,
  customer_id INTEGER NOT NULL UNIQUE REFERENCES customers (id)
);

CREATE TABLE orders (                        -- 1:N: one customer, many orders
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  created_at  TIMESTAMP NOT NULL
);

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  sku         VARCHAR(20) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL
);

CREATE TABLE order_items (                   -- N:M junction: orders x products
  order_id    INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id  INTEGER NOT NULL REFERENCES products (id),
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

جرّبها: منسّق SQL جرّبها: مولّد جمل SQL INSERT

أسئلة شائعة

المراجع

• Constraints — PostgreSQL DocumentationPostgreSQL Global Development Groupقيود التحقق وعدم القيمة الفارغة والتفرّد والمفتاح الأساسي والمفتاح الأجنبي والإقصاء — القواعد التي تفرضها قاعدة البيانات ويتحقق منها المختبِر.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023الجزء الإطاري من معيار SQL الدولي، الذي تنفّذه لهجات PostgreSQL وMySQL وSQLite وغيرها بدرجات متفاوتة.
• SQL As Understood By SQLiteSQLiteقواعد SQL التي يقبلها SQLite، عبارة تلو الأخرى، مع مخططات نحوية.