SQL Interview Question and Answers | Cascade Delete Constraint in SQL

Опубликовано: 15 Октябрь 2024
на канале: Parag Dhawan

823

The term "CASCADE DELETE constraint" typically refers to a database constraint that specifies what should happen when a record in a parent table is deleted, and that record has related records in one or more child tables. The purpose of this constraint is to define the behavior of the database when a parent record is deleted. There are two primary options for this constraint: CASCADE and SET NULL.

1. **CASCADE DELETE**:
When a CASCADE DELETE constraint is defined, it means that when a parent record is deleted, all related records in child tables will also be automatically deleted.
This is a way to ensure that the database remains consistent and that there are no "orphaned" child records left behind when the parent record is removed.
The CASCADE DELETE constraint can be useful when you want to maintain data integrity and ensure that all associated data is removed when the parent data is no longer relevant.

2. **SET NULL**:
Alternatively, you can define a SET NULL constraint. When a parent record is deleted, the foreign key columns in the child tables are set to NULL.
This is useful in cases where you want to maintain the child records but remove their association with the parent record.
It's essential to ensure that the foreign key columns in the child tables allow NULL values if you plan to use the SET NULL constraint.

Here's an example to illustrate the use of CASCADE DELETE:

Let's say you have two tables: `Orders` (the parent) and `OrderDetails` (the child). Each order can have multiple order details. If you define a CASCADE DELETE constraint on the relationship between these tables, it means that when you delete an order from the `Orders` table, all associated order details in the `OrderDetails` table will be automatically deleted.

Example SQL to define a CASCADE DELETE constraint:

```sql
CREATE TABLE Orders (
OrderID INT PRIMARY KEY
-- Other columns
);

CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
-- Other columns
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE
);

#sql
```

It's important to be cautious when using CASCADE DELETE constraints, as they can lead to data loss if not used carefully. Before implementing such constraints, consider the data requirements and the potential impact on the database. It's also common to have related database triggers or application logic to handle such cascading actions, depending on the specific needs of your database and application.