Q

ON DELETE RESTRICT

I would like to know where we use DELETE RESTRICT?

I would like to know where we use 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.

This was first published in May 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close