ON DELETE RESTRICT

The answer to this question comes in two parts. The first part of the answer is that we can use ON DELETE RESTRICT only when declaring a foreign key. This can be done either at the same time as creating the table, or afterwards, with ALTER TABLE, to add the foreign key.

The basic syntax is like this:

create table orders
( ...
, customer_id   integer  not null
, foreign key (customer_id)
      references customers (id) 
          on update cascade
          on delete restrict
, ... )

In this example, the ORDERS table contains a foreign key CUSTOMER_ID which references the primary key ID in the CUSTOMERS table.

Notice that there are two clauses which follow the definition of the foreign key's reference -- ON UPDATE and ON DELETE. The ON UPDATE clause says that if a particular primary key ID value in the CUSTOMERS table ever changes (and why this would happen is unusual, but it can/does happen), then the related foreign key should also be updated (this is the "cascade" part) to match the new value of the CUSTOMER table ID value.

The ON DELETE clause says that if a particular primary key ID value in the CUSTOMERS table is deleted, this action shall be prevented (this is the "restrict" part) if there is any row in the ORDERS table which has a foreign key that matches the value of the CUSTOMER table ID value.

So that brings us to the second part of the answer. When do we use ON DELETE RESTRICT? Whenever we don't want "orphan" rows in the database! We don't want to delete a customer from the CUSTOMER table if there are any orders for that customer in the ORDERS table. If there aren't any orders for that customer, then it's safe to delete the customer, and the delete will be allowed to proceed. If there are some orders for the customer that we wanted to delete, the delete will fail, because if it didn't fail, i.e., if we delete a customer that has orders, then those orders would have a CUSTOMER_ID value that references an ID in the CUSTOMERS table that doesn't exist, and this scenario would violate relational integrity. This is such a serious situation that most databases will default to ON DELETE RESTRICT for foreign keys. You can override it (for example, to ON DELETE SET NULL), but the customers and orders example is not one where you would do that.