Lecture Notes: On Delete Clause in SQL

Introduction

• Explanation of the ON DELETE clause in SQL.
• Two main types: ON DELETE SET NULL and ON DELETE CASCADE.

ON DELETE SET NULL

• Functionality: When a foreign key is deleted, replace it with NULL.
• Example Setup:
  • Consider a table transactions with a foreign key customer_id.
  • When a customer is deleted from the customers table, the corresponding customer_id in transactions can be set to NULL.
• Process:
  • Reinsert the customer (e.g., Poppy Puff) back into the customers table.
  • If creating a new table, use ON DELETE SET NULL in the foreign key clause.
  • For existing tables:
    • Drop the current foreign key constraint using ALTER TABLE transactions DROP FOREIGN KEY FK_customer_id.
    • Add a new constraint using ALTER TABLE transactions ADD CONSTRAINT FK_customer_id FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL.
  • Upon deletion of a customer, the customer_id in transactions becomes NULL.

ON DELETE CASCADE

• Functionality: When a foreign key is deleted, delete the entire row.
• Example Setup:
  • Similar setup with transactions and customers tables.
  • When a customer is deleted, the entire row referencing that customer in transactions is removed.
• Process:
  • Reinsert the customer back into the customers table.
  • Drop the current foreign key constraint using ALTER TABLE transactions DROP FOREIGN KEY FK_customer_id.
  • Add a new constraint using ALTER TABLE transactions ADD CONSTRAINT FK_customer_id FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE.
  • Update the transactions table to reflect the change, then delete the customer.
  • The entire row in transactions linked to the deleted customer ID will be removed.

Conclusion

• ON DELETE clause options:
  • SET NULL: Sets the foreign key value to NULL.
  • CASCADE: Deletes the entire row.
• Each option has its specific use cases based on the integrity requirements of the database.

Additional Points

• Remember to manage foreign key checks during demonstrations or operations to avoid constraint errors.
• Ensure database integrity by carefully choosing between SET NULL and CASCADE based on application requirements.