العودة إلى جميع الأدلة
SQL··قراءة في 5 دقائق

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

فهم المفاتيح الأساسية مقابل المفاتيح الخارجية في SQL. تعرّف على كيف تفرض سلامة البيانات، وتبني العلاقات، وتعمل مع الإجراءات المتتالية.

المفتاح الأساسي للعميل 42 المشار إليه بواسطة مفاتيح طلب أجنبية، مع قيد SQL ملموس
في هذا الدليل
  1. فهم المفاتيح الأساسية
  2. فهم المفاتيح الخارجية
  3. كيف تعمل المفاتيح الأساسية والخارجية معاً
  4. المفاتيح المركبة
  5. الإجراءات المتتالية
  6. أفضل الممارسات
  7. المراجع والقراءات الإضافية

تستخدم أمثلة SQL لغة PostgreSQL ما لم يتم تسمية لهجة أخرى. يفترض المقتطفات القصيرة أن جداولها المُشار إليها موجودة بالفعل. تحقق من صحة DDL في قاعدة بياناتك المستهدفة قبل تطبيقها.

فهم المفاتيح الأساسية

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

المفاتيح الطبيعية مقابل المفاتيح البديلة

أ مفتاح طبيعي يستخدم بيانات موجودة مسبقاً في مجال العالم الحقيقي. على سبيل المثال، رمز البلد ISO (US, FR) أو رقم الكتاب المعياري الدولي (ISBN). المفاتيح الطبيعية ذات معنى ولكنها يمكن أن تتغير بمرور الوقت وقد تحمل مخاوف تتعلق بالخصوصية (فكر في أرقام الضمان الاجتماعي).

أ مفتاح بديل هو معرف اصطناعي ليس له معنى تجاري - عادةً عدد صحيح يتم زيادته تلقائيا أو UUID. المفاتيح البديلة مستقرة ومدمجة وسهلة الفهرسة، وهذا هو سبب تفضيل معظم المخططات الحديثة لها.

-- Natural key example
CREATE TABLE countries (
    iso_code  CHAR(2)      PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

-- Surrogate key example
CREATE TABLE users (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  DEFAULT now()
);

فهم المفاتيح الخارجية

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

CREATE TABLE posts (
    id        SERIAL       PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INT          NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users (id)
);

مع وضع هذا القيد في مكانها، سترفض قاعدة البيانات أي INSERT إلى posts حيث author_id لا يطابق users.id.

قيد المفتاح الأجنبي معروض كخط علاقة في ERD
تحدد المفاتيح الأساسية الصفوف؛ بينما تحول المفاتيح الخارجية تلك المعرفات إلى علاقات مرئية في المخطط.

كيف تعمل المفاتيح الأساسية والخارجية معاً

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

-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM   posts p
JOIN   users u ON u.id = p.author_id;

لرسم الكيانات قبل كتابة SQL، افتح منشئ ERD. لفحص الجدول وتعريفات المفاتيح الخارجية الواردة أعلاه، قم بلصقها في محرر SQL إلى ERD.

المفاتيح المركبة

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

-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

المفتاح الأساسي المركب (student_id, course_id) يضمن عدم تمكن الطالب نفسه من التسجيل في نفس الدورة التدريبية مرتين مع السماح لكل طالب بأخذ دورات متعددة ولكل دورة أن يكون لها العديد من الطلاب.

الإجراءات المتتالية

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

  • CASCADE — حذف صفوف الأبناء أو تحديثها تلقائياً عندما يتغير صف الأب.
  • SET NULL — تعيين عمود المفتاح الأجنبي إلى NULL (يجب أن يقبل العمود قيم فارغة null).
  • SET DEFAULT — إعادة ضبط العمود إلى قيمته الافتراضية، والتي يجب أن تظل مستوفية للمفتاح الأجنبي. يختلف الدعم حسب المحرك؛ ترفض MySQL InnoDB هذا الإجراء.
  • RESTRICT / NO ACTION — رفض التغيير الذي يترك مراجع غير صالحة. في PostgreSQL، يمكن تأجيل NO ACTION عندما يكون القيد قابلاً للتأجيل؛ بينما RESTRICT فوري.
CREATE TABLE comments (
    id        SERIAL       PRIMARY KEY,
    body      TEXT         NOT NULL,
    post_id   INT          NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Deleting a post now automatically removes all its comments.

استخدم CASCADE بعناية - حذف صف أب سيؤدي إلى مسح كل صف فرعي ذي صلة بصمت. في الأنظمة التي تعتمد بشكل كبير على التدقيق، ضع في اعتبارك RESTRICT مدمجا مع علامة الحذف الناعم (soft-delete) بدلاً من ذلك.

أفضل الممارسات

  • تفضيل المفاتيح البديلة (SERIAL, BIGSERIAL، أو UUID) كمفاتيح أساسية لجداول التطبيقات. استخدم مفتاحاً طبيعياً عندما يكون معرف النطاق مستقراً بدرجة تكفي متطلباتك، وخطط لكيفية تأثير التغييرات على المراجع.
  • دائماً قم بتسمية القيود الخاصة بك (على سبيل المثال، fk_posts_author) لكي تكون رسائل الخطأ ذات معنى.
  • فهرسة أعمدة المفاتيح الخارجية. لا تنشئ العديد من قواعد البيانات فهارس تلقائياً على المفاتيح الخارجية، ويمكن أن تؤدي الفهارس المفقودة إلى إبطاء عمليات الدمج وعمليات الحذف المتتالية بشكل كبير.
  • فكر في إجراء متتابع لكل مفتاح خارجي. التراجع الافتراضي إلى NO ACTION هو الأكثر أمانا؛ التبديل إلى CASCADE فقط عندما تفقد صفوف الأبناء معناها حقاً بدون أبنائها.
  • اختر مفاتيح مركبة من قواعد الهوية الخاصة بالنموذج. يمكن للمفتاح البديل تبسيط المراجع، ولكن احتفظ بقيد فريد (UNIQUE) على مفتاح النشاط التجاري الأصلي عندما يجب منع التكرارات.
  • قم بنمذجة مخططك بصرياً أولاً. الـ منشئ ERD يتيح لك رسم نموذج مفاهيمي ومراجعة تحويل SQL الخاص به - اكتشاف أخطاء التصميم قبل وصولها إلى الإنتاج.

المراجع والقراءات الإضافية

المزيد من المنشورات