MySQL وتصميم قواعد البيانات

السلامة المرجعية

13 دقيقة الدرس 30 من 40

السلامة المرجعية

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

ما هي السلامة المرجعية؟

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

تشبيه من الواقع: فكر في السلامة المرجعية مثل بطاقات المكتبة. يجب أن يشير سجل استعارة كتاب إلى رقم بطاقة مكتبة صالح. إذا فقد شخص ما بطاقته، يجب على المكتبة أن تقرر: منع الحذف (RESTRICT)، حذف جميع استعاراته (CASCADE)، أو تمييز الاستعارات كمجهولة (SET NULL).

إنشاء المفاتيح الخارجية

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

-- الجدول الأب (الجدول المُشار إليه)
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

-- الجدول الطفل (الجدول المُشير)
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL,

    -- قيد المفتاح الخارجي
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

-- إدراج بيانات صالحة
INSERT INTO customers (id, name, email) VALUES (1, 'John Doe', 'john@ex.com');
INSERT INTO orders (order_number, customer_id, total)
VALUES ('ORD-001', 1, 99.99);  -- يعمل!

-- هذا يفشل: لا يمكن إضافة أو تحديث صف طفل
INSERT INTO orders (order_number, customer_id, total)
VALUES ('ORD-002', 999, 49.99);  -- customer_id 999 غير موجود

إضافة المفاتيح الخارجية إلى الجداول الموجودة

يمكنك إضافة المفاتيح الخارجية بعد إنشاء الجدول:

-- إضافة مفتاح خارجي إلى جدول موجود
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id);

-- إضافة مفاتيح خارجية متعددة
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id)
    REFERENCES orders(id),
ADD CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id)
    REFERENCES products(id);

شرط أساسي: قبل إضافة مفتاح خارجي، تأكد من أن جميع القيم الموجودة في عمود الطفل موجودة في جدول الأب، أو ستفشل عبارة ALTER TABLE.

إجراءات ON DELETE

يحدد بند ON DELETE ما يحدث عند حذف صف مُشار إليه:

RESTRICT (افتراضي): منع الحذف إذا كانت صفوف الأطفال موجودة
CASCADE: حذف صفوف الأطفال تلقائياً
SET NULL: تعيين المفتاح الخارجي إلى NULL في صفوف الأطفال
NO ACTION: نفس RESTRICT (فحص مؤجل في بعض قواعد البيانات)
SET DEFAULT: تعيين إلى قيمة افتراضية (غير مدعوم في InnoDB)

RESTRICT - منع الحذف

RESTRICT يمنع حذف صفوف الأب التي لديها سجلات أطفال:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE RESTRICT  -- هذا هو الافتراضي
);

-- إدراج بيانات
INSERT INTO customers (id, name, email) VALUES (1, 'Alice', 'alice@ex.com');
INSERT INTO orders (customer_id, total) VALUES (1, 100.00);

-- هذا يفشل: لا يمكن حذف أو تحديث صف الأب
DELETE FROM customers WHERE id = 1;

-- الحل: احذف صفوف الأطفال أولاً
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;  -- الآن هذا يعمل

حالة الاستخدام: RESTRICT مثالي عندما تكون سجلات الأطفال مهمة ولا ينبغي حذفها تلقائياً. على سبيل المثال، منع حذف الحساب بينما الطلبات النشطة موجودة.

CASCADE - الحذف التلقائي

CASCADE يحذف تلقائياً صفوف الأطفال ذات الصلة:

CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,

    CONSTRAINT fk_posts_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,

    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES blog_posts(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_comments_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

-- إدراج بيانات
INSERT INTO users (id, name, email) VALUES (1, 'Bob', 'bob@ex.com');
INSERT INTO blog_posts (id, user_id, title, content)
VALUES (1, 1, 'My Post', 'Content');
INSERT INTO comments (post_id, user_id, comment)
VALUES (1, 1, 'Great post!');

-- حذف المستخدم: يحذف تلقائياً جميع مشاركاته وتعليقاته
DELETE FROM users WHERE id = 1;
-- النتيجة: تم حذف المستخدم والمشاركة والتعليق جميعاً

تحذير: يمكن أن يحذف CASCADE كميات كبيرة من البيانات. في الإنتاج، فكر في استخدام الحذف الناعم (تمييز السجلات كمحذوفة) بدلاً من CASCADE للبيانات الحرجة.

SET NULL - الحفاظ على سجلات الأطفال

SET NULL يحتفظ بسجلات الأطفال ولكن يزيل المرجع:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NULL,  -- يجب السماح بـ NULL
    sales_rep_id INT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_orders_sales_rep
        FOREIGN KEY (sales_rep_id)
        REFERENCES employees(id)
        ON DELETE SET NULL
);

-- إدراج بيانات
INSERT INTO customers (id, name, email) VALUES (1, 'Charlie', 'charlie@ex.com');
INSERT INTO employees (id, name) VALUES (10, 'Sales Rep');
INSERT INTO orders (customer_id, sales_rep_id, total)
VALUES (1, 10, 500.00);

-- حذف موظف: يبقى الطلب ولكن sales_rep_id يصبح NULL
DELETE FROM employees WHERE id = 10;
-- الطلب لا يزال موجوداً مع customer_id=1، sales_rep_id=NULL

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

إجراءات ON UPDATE

مشابهة لـ ON DELETE، يحدد ON UPDATE السلوك عند تعديل مفتاح الأب:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE  -- إذا تغير معرف العميل، حدّث جميع الطلبات
);

-- إذا قمت بتحديث معرف العميل، يتم تحديث جميع طلباته تلقائياً
UPDATE customers SET id = 100 WHERE id = 1;
-- جميع الطلبات مع customer_id=1 الآن لديها customer_id=100

أفضل ممارسة: استخدم مفاتيح أساسية متزايدة تلقائياً لا تتغير أبداً. هذا يجعل ON UPDATE CASCADE غير ضروري في معظم الحالات. المفاتيح الطبيعية التي قد تتغير (مثل عناوين البريد الإلكتروني) من الأفضل أن تكون أعمدة UNIQUE، وليس مفاتيح أساسية.

معالجة السجلات اليتيمة

السجلات اليتيمة هي صفوف أطفال تشير إلى صفوف أب غير موجودة:

-- البحث عن طلبات يتيمة (عملاء لم يعودوا موجودين)
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- تنظيف السجلات اليتيمة
DELETE FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers);

-- أو تعيين إلى NULL إذا كان مسموحاً
UPDATE orders
SET customer_id = NULL
WHERE customer_id NOT IN (SELECT id FROM customers);

التسلسلات متعددة المستويات

يمكن أن تنتشر التسلسلات عبر مستويات متعددة:

-- تسلسل هرمي من ثلاثة مستويات
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,

    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES categories(id)
        ON DELETE CASCADE
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,

    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE CASCADE
);

-- حذف فئة: يحذف المنتجات، والتي تحذف order_items
DELETE FROM categories WHERE id = 5;
-- تم حذف جميع المنتجات في الفئة 5
-- تم حذف جميع order_items لتلك المنتجات

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

المراجع الدائرية

تعامل مع المراجع الدائرية بعناية:

-- السيناريو: جدول الموظفين حيث كل موظف لديه مدير (أيضاً موظف)
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    manager_id INT NULL,

    CONSTRAINT fk_employees_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(id)
        ON DELETE SET NULL  -- عند حذف المدير، يبقى المرؤوسون
);

-- إدراج CEO (لا يوجد مدير)
INSERT INTO employees (id, name, manager_id) VALUES (1, 'CEO', NULL);

-- إدراج مدير يرفع تقارير إلى CEO
INSERT INTO employees (id, name, manager_id) VALUES (2, 'Manager', 1);

-- إدراج موظف يرفع تقارير إلى المدير
INSERT INTO employees (id, name, manager_id) VALUES (3, 'Employee', 2);

-- حذف المدير: يبقى الموظف ولكن manager_id يصبح NULL
DELETE FROM employees WHERE id = 2;

عرض المفاتيح الخارجية

استعلم عن مخطط المعلومات لرؤية علاقات المفتاح الخارجي:

-- عرض جميع المفاتيح الخارجية في قاعدة البيانات
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'your_database'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- عرض قيود المفتاح الخارجي مع الإجراءات
SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    REFERENCED_TABLE_NAME,
    UPDATE_RULE,
    DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database';

-- رؤية إنشاء الجدول مع المفاتيح الخارجية
SHOW CREATE TABLE orders;

إسقاط المفاتيح الخارجية

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

-- إسقاط قيد المفتاح الخارجي
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

-- إسقاط وإعادة إنشاء بإجراء مختلف
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer,
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON DELETE CASCADE;

مثال من الواقع: مخطط تجارة إلكترونية كامل

إليك مخطط شامل مع سلامة مرجعية مناسبة:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    total DECIMAL(10, 2) NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE RESTRICT  -- لا يمكن حذف العملاء مع الطلبات
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,

    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(id)
        ON DELETE CASCADE,  -- حذف العناصر عند حذف الطلب

    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE RESTRICT  -- لا يمكن حذف المنتجات في الطلبات الموجودة
);

CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NULL,  -- السماح بـ NULL للعملاء المحذوفين
    rating INT NOT NULL,
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_reviews_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE CASCADE,  -- حذف المراجعات عند حذف المنتج

    CONSTRAINT fk_reviews_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE SET NULL,  -- الاحتفاظ بالمراجعة عند حذف العميل

    CONSTRAINT chk_rating_range CHECK (rating BETWEEN 1 AND 5)
);

تمرين عملي:

السيناريو: صمم نظام تسجيل دورات مع سلامة مرجعية مناسبة.

المتطلبات:

جداول الطلاب والدورات والمدرسين
جدول التسجيلات يربط الطلاب بالدورات
كل دورة لها مدرس
إذا تم حذف دورة، احذف تسجيلاتها
إذا تم حذف طالب، احذف تسجيلاته
إذا تم حذف مدرس، امنع الحذف إذا كان لديه دورات

الحل:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE)
);

CREATE TABLE instructors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department VARCHAR(100)
);

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    instructor_id INT NOT NULL,
    credits INT NOT NULL,

    CONSTRAINT fk_courses_instructor
        FOREIGN KEY (instructor_id)
        REFERENCES instructors(id)
        ON DELETE RESTRICT  -- لا يمكن حذف المدرسين مع الدورات
);

CREATE TABLE enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    grade VARCHAR(2),

    CONSTRAINT fk_enrollments_student
        FOREIGN KEY (student_id)
        REFERENCES students(id)
        ON DELETE CASCADE,  -- حذف التسجيلات عند حذف الطالب

    CONSTRAINT fk_enrollments_course
        FOREIGN KEY (course_id)
        REFERENCES courses(id)
        ON DELETE CASCADE,  -- حذف التسجيلات عند حذف الدورة

    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

الحذف الناعم مقابل تسلسلات المفتاح الخارجي

فكر في الحذف الناعم للبيانات المهمة:

-- بدلاً من CASCADE DELETE، استخدم الحذف الناعم
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    deleted_at TIMESTAMP NULL,  -- NULL = نشط، طابع زمني = محذوف
    INDEX idx_deleted_at (deleted_at)
);

-- "حذف" عميل (حذف ناعم)
UPDATE customers SET deleted_at = NOW() WHERE id = 1;

-- استعلام العملاء النشطين فقط
SELECT * FROM customers WHERE deleted_at IS NULL;

-- استعادة عميل
UPDATE customers SET deleted_at = NULL WHERE id = 1;

الملخص

في هذا الدرس، تعلمت:

تضمن السلامة المرجعية العلاقات المتسقة بين الجداول
تفرض المفاتيح الخارجية قيود السلامة المرجعية
ON DELETE RESTRICT يمنع حذف الصفوف المُشار إليها
ON DELETE CASCADE يحذف تلقائياً صفوف الأطفال ذات الصلة
ON DELETE SET NULL يحتفظ بصفوف الأطفال ولكن يزيل المراجع
يمكن أن تنتشر التسلسلات متعددة المستويات عبر التسلسلات الهرمية بأكملها
استخدم أسماء قيود مفتاح خارجي ذات معنى
فكر في الحذف الناعم للبيانات المهمة بدلاً من CASCADE

مبروك! لقد أكملت الوحدة 5: المعاملات وسلامة البيانات. أنت الآن تفهم كيفية الحفاظ على اتساق البيانات من خلال المعاملات ومستويات العزل والأقفال والقيود والسلامة المرجعية—مهارات أساسية لأي محترف في قواعد البيانات!