CASCADE
CASCADE is an option in SQL that automatically propagates changes from a parent entity to a child entity in a relational database management system. In the context of foreign key constraints, CASCADE ensures that action performed on the parent table (such as DELETE or UPDATE) are also performed on the associated records in the child table to maintain data consistency and referential integrity.
Example
CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE);
DELETE FROM customers WHERE customer_id = 1;
Output
The DELETE statement does not provide a visible output when executed. It does its job in the background.
Explanation
In the given code, we created a table orders
with a foreign key customer_id
that references the customer_id
in the customers
table. The ON DELETE CASCADE
clause specifies that when a customer record in the customers
table is deleted, then all corresponding records in the orders
table for that customer should also be deleted automatically. In the last command, we deleted a customer with customer_id = 1
from the customers
table, which also deleted all orders related to this customer from the orders
table thanks to the CASCADE
option.
Example
CREATE TABLE orders ( order_no integer PRIMARY KEY, product_no integer REFERENCES products ON DELETE CASCADE);
If the related product row is deleted from the products
table, the corresponding rows in the orders
table will be deleted due to the CASCADE
rule.
Output
NOTICE: CREATE TABLE will create implicit sequence "orders_order_no_seq" for serial column "orders.order_no"CREATE TABLE
This output indicates that the orders
table has been successfully created with an implied sequence for the order_no
column.
Explanation
In the code example, a new table ‘orders’ is created with two columns: ‘order_no’ and ‘product_no’. The ‘product_no’ column references the ‘products’ table. The ON DELETE CASCADE
rule is applied, meaning if a referenced row in the ‘products’ table is deleted, all rows in ‘orders’ table that hold a reference to it will also be deleted. The output confirms the successful creation of the ‘orders’ table with an automatic sequence created for the ‘order_no’ column.
Example
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ON DELETE CASCADE);
Output
Command(s) completed successfully.
#### Explanation
The example above creates an `Orders` table with `OrderID`, `OrderNumber`, and `PersonID` as columns. The `OrderID` is the primary key, and `PersonID` references a foreign key from a hypothetical `Persons` table. The `ON DELETE CASCADE` constraint implies that if a record in the `Persons` table is deleted, all corresponding records in the `Orders` table with the same `PersonID` will be automatically deleted.
</Fragment>
<Fragment slot="tab-4">Oracle</Fragment><Fragment slot="panel-4">#### Example
CREATE TABLE Parent ( id INT, PRIMARY KEY(id) );
CREATE TABLE Child ( id INT, parent_id INT, FOREIGN KEY(parent_id) REFERENCES Parent(id) ON DELETE CASCADE );
INSERT INTO Parent (id) VALUES (1); INSERT INTO Child (id, parent_id) VALUES (1, 1); DELETE FROM Parent WHERE id=1;
#### Output
No rows in set (0.01 sec)
#### Explanation
The CASCADE keyword in Oracle is used in conjunction with the DELETE or UPDATE commands to automatically remove or adjust entries in child tables when the corresponding entries in the parent table are removed or modified. In this example, when an item is deleted from the 'Parent' table, the CASCADE constraint ensures that any corresponding entries in the 'Child' table will also be deleted.
</Fragment>
<Fragment slot="tab-5">SQLite</Fragment><Fragment slot="panel-5">#### Example```sqlPRAGMA foreign_keys = ON;
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT);
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ON DELETE CASCADE);
INSERT INTO artist (artistid, artistname) VALUES (1, 'Artist 1');INSERT INTO track (trackid, trackname, trackartist) VALUES (1, 'Track 1', 1);
DELETE FROM artist WHERE artistid=1;
Output
sqlite> SELECT * FROM artist;sqlite> SELECT * FROM track;
Explanation
This provided SQL script first enables the foreign_keys
feature in SQLite, because it’s turned off by default. It then goes on to declare two tables: artist
and track
. The track
table has a foreign key trackartist
that references artistid
in the artist
table. Through the ON DELETE CASCADE
rule, any deletion of a record in the artist
table triggers the deletion of all associated records in the track
table. After inserting example data into both tables, deleting the artist with artistid=1
also deletes the track with trackid=1
due to the cascade effect. The output queries for both the tables return nothing proving the cascade delete.