Last Updated: 2024-11-23

Foreign keys sometimes contain ON DELETE clauses, as do the first two fields below:

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

What are the possible settings?

• NO ACTION - In effect this does not mean "do nothing". Rather, it means produce an error indicating that the deletion or update would create a foreign key constraint violation. Think of it as "no additional action"

• RESTRICT - same as NO ACTION (except this check is not deferabble to later in the transaction). Why might you want it to de deferrable? Perhaps because you set things right with another operation in the same transaction.

• CASCADE - Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively.

• SET NULL - Set any referencing columns to null

The default is "NO ACTION"

Cascade and Web Frameworks (e.g. Rails)

Watch out - it skips lifecyle callbacks that might occur in your web framework.

Resources

• https://www.postgresql.org/docs/9.5/ddl-constraints.html