حذف الجداول وقواعد البيانات في SQL

تتحدّث هذه المقالة عن كيفية حذف الجداول وقواعد البيانات (DROP و DELETE)، واقتطاع الجداول (TRUNCATE TABLE)، وكيفية استخدام الحذف المتسلسل (Cascading Delete) في SQL.

العبارة DELETE

تُستخدَم عبارة DELETE لحذف السجلات من جدول معيّن.

حذف جميع الصفوف

عند استخدام DELETE بدون عبارة ‎WHERE‎، ستُحذف جميع الصفوف من الجدول.

DELETE FROM Employees

على العموم، أداء العبارة TRUNCATE (انظر الفقرة أدناه) أفضل من أداء DELETE، لأنّها تتجاهل الزنادات (triggers) والفهارس وتحذف البيانات مباشرة.

استخدام DELETE مع WHERE

ستحذف الشيفرة التالية جميع الصفوف التي تفي بشرط ‎WHERE‎، أي الصفوف التي تمثل الموظفين الذين يحملون الاسم John .

DELETE FROM Employees
WHERE FName = 'John'

الاقتطاع عبر TRUNCATE

تُستخدم عبارة الاقتطاع TRUNCATE لإعادة الجدول إلى الحالة التي كان عليها عند إنشائه. إذ تُحذف جميع الصفوف من الجدول، ويُعاد تعيين القيم تلقائية الزيادة (auto-increment) إلى قيمتها الأولى.

لا تحذف TRUNCATE كلّ صف على حدة كما هو الشأن مع DELETE:

TRUNCATE TABLE Employees

حذف بعض الصفوف بناءً على نتائج عمليات المقارنة مع جداول أخرى

من الممكن حذف البيانات (‎DELETE‎) من جدول إذا كانت مطابقة (أو غير مطابقة) لبيانات جدول آخر.

لنفترض أنّنا نريد حذف البيانات من الجدول المصدري بمجرّد تحميلها إلى الجدول الهدف.

DELETE FROM Source
WHERE EXISTS ( SELECT 1 --ليست مهمّة SELECT القيمة المحدّدة في
 FROM Target
 Where Source.ID = Target.ID )

تسمح معظم أنظمة معالجة قواعد البيانات (RDBMS) الشهيرة (مثل MySQL و Oracle و PostgresSQL و Teradata) بضمّ الجداول خلال عملية الحذف ‎DELETE‎، ممّا يتيح إجراء موازنات معقّدة في عبارات قصيرة.

لنفترض الآن أنّنا نريد تجميع (Aggregate) جدول من الجدول الهدف على أساس التاريخ date وليس المُعرّف ID. لنفترض أيضًا أنّنا نريد ألّا تُحذف البيانات من المصدر إلّا بعد أن يُملأ حقل التاريخ Date الخاص بالجدول المُجمَّع (aggregate).

في أنظمة MySQL و Oracle و Teradata، يمكن القيام بذلك باستخدام:

DELETE FROM Source
WHERE Source.ID = TargetSchema.Target.ID
     AND TargetSchema.Target.Date = AggregateSchema.Aggregate.Date

أمّا في PostgreSQL، فاستخدم الصياغة التالية:

DELETE FROM Source
USING  TargetSchema.Target, AggregateSchema.Aggregate
WHERE Source.ID = TargetSchema.Target.ID
     AND TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate

ينتج عن هذا أساسًا عمليات ضمّ داخلي (INNER JOINs) بين الجدول المصدري والجدول الهدف والجدول المُجمّع (Aggregate.) يُنفّذ الحذف على الجدول المصدري في حال وجود نفس المعرّفات في الهدف، وكذلك في حال تساوي التاريخين date في الجدول الهدف وكذلك في الجدول المُجمَّع.

يمكن كتابة الاستعلام نفسه (في MySQL و Oracle و Teradata) على النحو التالي:

DELETE Source
FROM Source, TargetSchema.Target, AggregateSchema.Aggregate
WHERE Source.ID = TargetSchema.Target.ID
 AND TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate

في بعض أنظمة إدارة قواعد البيانات (مثل Oracle و MySQL)، يُمكن أن استخدام عمليات الضمّ joins صراحة في عبارات ‎Delete‎، بيْد أنّها غير مدعومة في جميع المنصات (كما هو الحال في Teradata).

يمكن إجراء عمليات الموازنة للتحقق من سيناريوهات عدم التطابق بدلاً من سيناريوهات التطابق مع جميع أنماط الصياغات (لاحظ ‎NOT‎ EXISTS‎ أدناه):

DELETE FROM Source
WHERE NOT EXISTS ( SELECT 1 -- لا تهمّ SELECT القيم المحدّدة في
 FROM Target
 Where Source.ID = Target.ID )

الاقتطاع عبر TRUNCATE

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

يزيل المثال التالي جميع الصفوف من جدول الموظفين Employee:

TRUNCATE TABLE Employee;

يُفضّل عمومًا استخدام TRUNCATE على DELETE، لأنّها تتجاهل جميع الفهارس والزنادات (triggers)، وتزيل العناصر مباشرة.

حذف الجداول (DELETE) هي عملية تعمل على الصفوف، بمعنى أنّها تحذف كل صفّ على حدة. أما اقتطاع الجداول، فهي عملية تعمل على صفحة كاملة من البيانات (page operation)، إذ يُعاد تخصيص (reallocate) صفحة البيانات بأكملها. إذا كان لديك جدول يحتوي مليون صفّ، فسيكون اقتطاع الجدول أسرع بكثير من استخدام عبارة حذف DELETE الجدول.

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

يؤدّي حذف جميع الصفوف (عبر DELETE) ثم إدراج سجلات جديدة إلى زيادة قيمة المفتاح الرئيسي المتزايد تلقائيًا (Auto incremented Primary key) انطلاقًا من القيمة المُدرجة سابقًا، أمّا في عبارة Truncate، فسيُعاد تعيين قيمة المفتاح الرئيسي التلقائي، وسيبدأ من 1.

لاحظ أنه عند اقتطاع جدول ما، يجب ألا تكون هناك مفاتيح خارجية (foreign keys)، وإلا فسيُطرح خطأ.

DROP

محو جدول DROP TABLE

تحذف عبارة DROP TABLE جدولًا مع بياناته من قاعدة البيانات بشكل دائم.

الأمثلة التالية تتحقّق من وجود الجدول قبل محوه:

MySQL ≥ 3.19

DROP TABLE IF EXISTS MyTable;

PostgreSQL ≥ 8.x

DROP TABLE IF EXISTS MyTable;

SQL Server ≥ 2005

If Exists(Select * From Information_Schema.Tables
      Where Table_Schema = 'dbo'
         And Table_Name = 'MyTable')
    Drop Table dbo.MyTable

SQLite ≥ 3.0

DROP TABLE IF EXISTS MyTable;

محو قاعدة بيانات

يمكن محو قاعدة البيانات باستخدام عبارة DROP DATABASE.

تنبيه: تحذف DROP DATABASE قاعدة البيانات نهائيًا، لذا عليك أن تحرص دائمًا على تخزين نسخة احتياطية من قاعدة البيانات إن خشيت ضياع البيانات.

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

DROP DATABASE [dbo].[Employees]

الحذف المتسلسل Cascading Delete

لنفترض أنّ لديك تطبيقًا يدير فندقًا يضمّ عددًا من الغرف. لنفترض أنّ لديك العديد من العملاء، وقد قرّرت إنشاء قاعدة بيانات لتخزين المعلومات الخاصة بعملائك.

ستحتوي قاعدة البيانات جدولًا واحدًا للعملاء، وآخر للغرف. كل عميل يمكن أن يستأجر N غرفة. هذا يعني أنّ جدول الغرف سيحتوي مفتاحًا خارجيًا (foreign key) يشير إلى جدول العملاء.

ALTER TABLE dbo.T_Room WITH CHECK ADD CONSTRAINT FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCES dbo.T_Client (CLI_ID)
GO

عند خروج أحد العملاء، سيتعيّن عليك حذف بياناته من البرنامج. .لكن إن كتبت:

DELETE FROM T_Client WHERE CLI_ID = x

فسترتَكب "انتهاكَ مفتاحٍ خارجي" (foreign key violation)، ذلك أنّه لا يجوز لك حذف عميل لديه غرفة.

عليك حذف غرف العميل قبل أن تحذف العميل. لنفترض أنّك تتوقّع أنّه قد تُضاف العديد من المفاتيح الخارجية (foreign key dependencies) في قاعدة البيانات مستقبلا نتيجةً لنموّ التطبيق. قد يخلق هذا مشكلة كبيرة. لأنّه في كلّ مرّة تعدّل قاعدة البيانات، سيكون عليك تعديل شيفرة تطبيقك في كل المواضِع المرتبطة بها. وقد يكون عليك أيضًا تعديل شيفرات تطبيقات أخرى (مثل الواجهات البرمجية للأنظمة الأخرى).

هناك حل أفضل يكفيك كلّ هذا العناء. فيكفي أن تضيف العبارة ‎ON DELETE CASCADE‎ إلى مفتاحك الخارجي.

ALTER TABLE dbo.T_Room -- WITH CHECK -- SQL-Server can specify WITH CHECK/WITH NOCHECK
ADD CONSTRAINT FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCES dbo.T_Client (CLI_ID)
ON DELETE CASCADE

الآن يمكنك أن تكتب:

DELETE FROM T_Client WHERE CLI_ID = x

وستُحذف الغرف تلقائيًا عند حذف العميل. لقد حللنا المشكلة دون الحاجة إلى إجراء تغييرات في ِشيفرة التطبيق.

تنبيه: في Microsoft SQL-Server، لن تنجح هذه المقاربة إذا كان الجدول يشير إلى نفسه. لذا إن حاولت إجراء حذف متسلسل على بنية متشعّبة عودية (recursive tree structure)، على النحو التالي:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id =
OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] WITH CHECK ADD CONSTRAINT
[FK_T_FMS_Navigation_T_FMS_Navigation] FOREIGN KEY([NA_NA_UID])
REFERENCES [dbo].[T_FMS_Navigation] ([NA_UID])
ON DELETE CASCADE
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id =
OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] CHECK CONSTRAINT [FK_T_FMS_Navigation_T_FMS_Navigation]
GO

فلن ينجح الأمر، لأنّ Microsoft-SQL-server لن تسمح لك بتعيين مفتاح خارجي باستخدام ‎ON DELETE CASCADE‎ على بنية متشعّبة عودية. أحد أسباب ذلك هو أنّ الشعبة قد تكون دورية، وهذا قد يؤدي إلى عملية سرمدية غير منتهية.

نظام PostgreSQL من ناحية أخرى يمكنه القيام بذلك؛ شريطة ألّا تكون الشعبة دورية (non-cyclic). إذ أنّه في حال كانت الشعبة دورية، فسيُطرح خطأ وقت التشغيل. الحل في مثل هذه الحالة هو إنشاء دالة حذف مخصّصة.

تنبيه: لا يمكنك حذف جدول العملاء وإعادة إدراج القيم مرّة أخرى، وإن حاولت ذلك، فستُحذف جميع المدخلات في الجدول T_Room.

ترجمة -وبتصرّف- للفصول من 29 إلى 33 من الكتاب SQL Notes for Professionals