يحتاج المفتاح الأجنبي المركب في PostgreSQL إلى مفتاح فريد مؤهل يشمل الأعمدة المشار إليها. المفتاح الأساسي على id وحده لا يؤهل مرجعًا إلى (id, edition)، رغم أنه يجعل هذا الزوج فريدًا منطقيًا. PostgreSQL: CREATE TABLE

يساعد هذا الدليل مطورًا يعمل في Terminal على جهاز Mac على تشخيص هذا التعارض، واختيار العلاقة المناسبة، واختبار النتيجة باستخدام node-postgres. تصف جميع الصفوف في الأمثلة مقالات اصطناعية. تستهدف أوامر SQL إصدار PostgreSQL 18 والمفاتيح الأجنبية العادية القائمة على المساواة؛ ويوثّق PostgreSQL 18 قواعد منفصلة للمراجع الزمنية التي تستخدم PERIOD. PostgreSQL: CREATE TABLE

لماذا يرفض PostgreSQL المرجع المركب؟

لنفترض أن جدول المقالات يحتوي على ثلاثة أعمدة: id وedition وtitle. مفتاحه الأساسي هو id. ويخزّن جدول آخر مرجعًا إلى مقال في قيمتين: article_id وedition.

تُعبّر هذه القطعة من قيد الجدول عن العلاقة المقصودة:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

يطلب هذا التصريح من PostgreSQL التحقق من القيمتين معًا مقابل صف واحد في الجدول الأب. يجب أن يكون الهدف الموثّق أعمدة قيد فريد أو مفتاح أساسي غير قابل للتأجيل، أو أعمدة فهرس فريد غير جزئي. لا يغطي المفتاح الأساسي الحالي سوى id؛ أما المرجع فيحدّد (id, edition). PostgreSQL: CREATE TABLE

يمكن فهم الأساس الرياضي لهذا الاعتراض: إذا لم يكن هناك صفّان في الجدول الأب يشتركان في id، فلا يمكنهما أيضًا الاشتراك في زوج (id, edition). ومع ذلك، لا يوفّر هذا الاستنتاج الهدف المطابق المعلن الذي يتطلبه تعريف المفتاح الأجنبي. في هذا المخطط، يوفّر إضافة UNIQUE (id, edition) ذلك الهدف. القيد الإضافي زائد منطقيًا من ناحية التفرد، لكنه يؤدي غرضًا عمليًا بوصفه هدفًا للمرجع المركب.

تأمل صفوف الجدول الأب التوضيحية التالية:

id edition title
101 morning Paper Shapes
102 evening Color Notes

للزوج التابع (101, 'morning') تطابق. أما الزوج (101, 'evening') فلا يتطابق: تظهر قيمتاه في الجدول، لكن في صفين مختلفين. وهذا هو سبب استخدام علاقة مركبة عندما يجب أن تصف القيمتان المخزنتان المقال نفسه.

هل ينبغي إضافة المفتاح الفريد أم تبسيط المفتاح الأجنبي؟

اختر العلاقة قبل تغيير المخطط. السؤال المناسب هو ما إذا كان الجدول التابع يحتاج إلى تخزين إصدار إلى جانب معرّف المقال وفرض اتساقهما.

السلوك المطلوب التصميم المناسب النتيجة
يحدّد الجدول التابع مقالًا ويعرض إصداره الحالي خزّن article_id؛ وأشر إلى id في الجدول الأب اقرأ الإصدار من الجدول الأب عند الاستعلام
يخزّن الجدول التابع إصدارًا يجب أن يتطابق مع مقاله أبقِ المرجع المركب؛ وأضف UNIQUE (id, edition) إلى الجدول الأب يتحقق PostgreSQL من وجود الزوج المخزّن معًا
يمكن لرقم المقال نفسه أن يحدّد عدة صفوف للإصدارات نمذج هوية الجدول الأب على أنها (id, edition) سيمنع المفتاح الأساسي على id وحده وجود صفوف الجدول الأب تلك

يصف الصف الأخير نموذج بيانات مختلفًا. لا تزل المفتاح الأساسي أحادي العمود لمجرد إخفاء رسالة خطأ؛ قرّر أولًا ما إذا كان ينبغي فعلًا أن يحتوي الجدول الأب على أرقام مقالات مكررة. يسمح PostgreSQL بمفتاح أساسي مركب، ولا يمكن للجدول أن يضم سوى مفتاح أساسي واحد. PostgreSQL: Constraints

في المثال القابل للتشغيل أدناه، تظل معرّفات المقالات فريدة على مستوى قاعدة البيانات. يخزّن الجدول التابع إصدارًا أيضًا عن قصد، لذا يحتفظ الجدول الأب بـPRIMARY KEY (id) ويكتسب الزوج الفريد الإضافي.

كيف تعيد إنتاج الخطأ وتصلحه على جهاز Mac؟

استخدم قاعدة بيانات PostgreSQL 18 محلية موجودة وتثبيتًا عاملًا لـNode.js؛ يفترض هذا الشرح أن كليهما متاح بالفعل. افتح Terminal وأنشئ مجلد مشروع جديدًا. ثبّت برنامج التشغيل باستخدام أمر الحزمة الموثّق. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

احفظ البرنامج الكامل التالي باسم demo.mjs. ينشئ جداول مؤقتة، ويوضح المخطط المرفوض، ويضيف المفتاح المطابق، ثم يختبر أزواج المقالات الصالحة وغير الصالحة. تستخدم جميع العبارات عميلًا واحدًا لأن node-postgres يتطلب بقاء المعاملة على مثيل العميل نفسه. node-postgres: Transactions

يستخدم الإجراء المساعد نقطة حفظ حول كل إخفاق متوقع لكي تتمكن المعاملة من المتابعة. يوثّق PostgreSQL أن ROLLBACK TO SAVEPOINT يلغي الأوامر المنفذة بعد نقطة الحفظ تلك ويسمح باستئناف العمل. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

يمرر البرنامج قيم المقالات منفصلة عبر $1 و$2 والعناصر النائبة اللاحقة، وفق واجهة استعلامات المعاملات في برنامج التشغيل. تبقى أسماء الجداول والأعمدة ثابتة في أوامر SQL؛ فلا يمكن لمعاملات PostgreSQL أن تحل محل المعرّفات. node-postgres: Queries

شغّل البرنامج باستخدام إعدادات الاتصال بقاعدة بياناتك المحلية الموجودة. يفترض هذا الاستدعاء أن اسم قاعدة البيانات هو article_demo، وأن الخادم يستمع على المنفذ 5432، وأن إعدادات الاتصال المحلية الافتراضية الحالية لديك تعمل. غيّر اسم قاعدة البيانات أو المنفذ إذا لزم الأمر. يقرأ node-postgres متغيرات بيئة الاتصال بـPostgreSQL. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

اجتاز JavaScript فحصًا نحويًا أثناء إعداد هذه المقالة. وتم التحقق من سلوك قاعدة البيانات بالرجوع إلى الوثائق؛ لكن البرنامج لم يُشغَّل على خادم PostgreSQL هنا.

ماذا يوضّح الخطآن والاستعلام النهائي؟

مع المتطلبات المسبقة المذكورة، الرسائل المتوقعة هي:

Expected error: 42830
Expected error: 23503

يحدث الخطأ الأول أثناء إنشاء الجدول التابع، قبل إدراج أي صفوف مقالات. يحدّد PostgreSQL 42830 على أنه invalid_foreign_key. بعد أن يكتسب الجدول الأب المفتاح الفريد المطابق، يصبح تعريف جدول التابع نفسه صالحًا. PostgreSQL: Error Codes

يحدث الخطأ الثاني أثناء إدراج (101, 'evening'). أصبح المخطط الآن صالحًا، لكن الزوج المقدّم ليس له صف في الجدول الأب. يحدّد PostgreSQL 23503 على أنه foreign_key_violation. يتيح التحقق من الرمز تمييز عدم تطابق الصف هذا عن عدم تطابق المخطط السابق من دون الاعتماد على نص رسالة خطأ مترجم. PostgreSQL: Error Codes

ينبغي أن يعيد الاستعلام النهائي صفًا واحدًا: الرابط 1، والعنوان Paper Shapes، والإصدار morning. يتضمن شرط الربط كلا مكوّني العلاقة. وبذلك يصف الاستعلام الزوج نفسه الذي يفرضه القيد.

يعبر مفتاحان أجنبيان منفصلان عن قاعدة مختلفة. فحتى لو كان لـedition مفتاح فريد مؤهل خاص به، فإن المراجع المستقلة إلى id وedition ستسمح لكل قيمة بأن تطابق صفًا مختلفًا في الجدول الأب. يوضح المثال العملي هذا الفرق: فكلتا القيمتين في الزوج المرفوض موجودتان منفردتين، لكنهما لا تجتمعان في زوج واحد.

يتخلّص الأمر النهائي ROLLBACK عمدًا من العرض التوضيحي، بما فيه تعريفات الجداول. لتكييف التصميم مع جداول دائمة، نفّذ تغيير المخطط ضمن عملية الترحيل المعتادة؛ فنسخ برنامج الجداول المؤقتة هذا لا ينشئ مخططًا دائمًا للتطبيق.

ما الذي ينبغي التحقق منه قبل تغيير مخطط قائم؟

ابدأ بقائمة الأعمدة المشار إليها. حدّد مفتاح الجدول الأب الذي يغطي هذه القائمة فعلًا، بدلًا من الاعتماد على اسم فهرس أو على كون الصفوف الحالية متميزة بالمصادفة. في هذا المثال، الإضافة اللازمة هي القيد المسمّى UNIQUE (id, edition) الظاهر في البرنامج.

ينشئ إضافة قيد فريد فهرس B-tree فريدًا. ضع هذا الفهرس الإضافي في الحسبان عند الاختيار بين التصميم المركب والاكتفاء بتخزين article_id. لا ينشئ تعريف المفتاح الأجنبي نفسه تلقائيًا فهرسًا على أعمدة الجدول التابع. PostgreSQL: Constraints

قبل إضافة مفتاح أجنبي إلى جدول تابع قائم، تحقق من أن أزواجه المخزنة تطابق صفوفًا في الجدول الأب. يحل مفتاح الهدف الصالح متطلب المخطط، لكنه لا يصلح قيم الجدول التابع غير المتطابقة. يتحقق الأمر العادي ALTER TABLE ... ADD CONSTRAINT من الصفوف الموجودة. ويدعم PostgreSQL أيضًا إضافة مفتاح أجنبي بوصفه NOT VALID، ثم تشغيل VALIDATE CONSTRAINT لاحقًا؛ إذ يؤجل ذلك التحقق من الصفوف الموجودة، مع تطبيق القيد على عمليات الإدراج أو التحديث اللاحقة. PostgreSQL: ALTER TABLE

تعامل مع التحقق بوصفه مسألة منفصلة عن أهلية الهدف. لا يوفّر NOT VALID مفتاح الجدول الأب المفقود. كما أنه ليس وسيلة لإضافة قيد فريد دون إثبات التفرد: يقصر PostgreSQL 18 هذا الخيار على قيود المفاتيح الأجنبية وقيود التحقق وقيود عدم السماح بالقيم الخالية. PostgreSQL: ALTER TABLE

ما الحالات الخاصة التي قد تغيّر النتيجة؟

لا يؤهل فهرس فريد جزئي ليكون هدف المفتاح الأجنبي العادي الموصوف هنا. وبالمثل، فإن جعل القيد الفريد المشار إليه قابلًا للتأجيل لا يستوفي شرط القيد غير القابل للتأجيل الموثّق. استخدم القيمة الافتراضية المباشرة UNIQUE (id, edition) في هذا المثال. PostgreSQL: CREATE TABLE

كما أن التعامل مع القيم الخالية مهم. وفق سلوك المطابقة الافتراضي، لا يتطلب المفتاح الأجنبي المركب تطابقًا في الجدول الأب إذا كان أي مكوّن من المكونات المرجعية خاليًا. يحدد المثال كلا مكوّني الجدول التابع على أنهما NOT NULL، بحيث يجب أن يطابق كل زوج مخزّن صفًا. إذا كانت العلاقة اختيارية، تسمح MATCH FULL بمرجع خالٍ بالكامل، لكنها ترفض المرجع الخالي جزئيًا. PostgreSQL: Constraints

الاختبار المفيد هو اختبار الزوج نفسه: هل يستطيع الجدول التابع تسمية معرّف مقال وإصدار ينتميان إلى صفين مختلفين؟ إذا كان يجب رفض ذلك، فأبقِ العلاقة المركبة وامنحها مفتاحًا مطابقًا في الجدول الأب. وإذا كان الجدول التابع يحتاج فقط إلى تحديد مقال، فأشِر إلى id واستخرج الإصدار من ذلك المقال عند الاستعلام.

المصادر