ondelete

OnDelete is an option used with foreign key constraints in relational databases to define what happens to dependent rows when the referenced parent row is deleted. It is written as ON DELETE <action> and is part of the SQL standard; it is supported by major databases such as PostgreSQL, MySQL, SQL Server, and Oracle.

The most common actions are CASCADE, SET NULL, SET DEFAULT, and RESTRICT (also known as NO ACTION in some systems). CASCADE deletes any child rows automatically when the parent is deleted. SET NULL sets the foreign key values in the child rows to NULL. SET DEFAULT assigns the column’s default value to the child rows. RESTRICT/NO ACTION prevents the deletion if there are existing dependent rows, with some databases enforcing the check immediately and others deferring it.

Example: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE); This demonstrates a cascading delete from the customers table to related orders. Another example: FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL allows deleting a customer while preserving orders by nullifying the customer reference.

Considerations: ON DELETE enforces referential integrity but can trigger large cascades that affect many rows, with implications for backups and replication. Some actions require the child foreign key to be nullable (SET NULL) or to have an appropriate default (SET DEFAULT). Not all databases support every action, and behavior can vary across systems. In ORM frameworks, OnDelete semantics are often mapped to cascade or nullify delete behavior in application code.