CASCADE

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
```sql
PRAGMA 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.