Does a foreign key make sense when I do not use Referential Integrity with On Delete/Update Cascade

Question

I do not want the orders to be deleted when a customer is deleted. (On Delete Cascade)
I use identity columns so I do not need On Update Cascade
It should be possible to delete a customer table although there exist orders pointing/referencing to a customer. I do not care when the customer is gone because I still need the order table for other tables.

Does a foreign key make sense in this scenario when I do not use Referential Integrity with On Delete/Update Cascade ?

Aaron Bertrand · Accepted Answer · 2012-06-10T18:53:22.553

Yes. The foreign key is not in place only to clean up after yourself but primarily to make sure the data is right in the first place (it can also assist the optimizer in some cases). I use foreign keys all over the place but I have yet to find a need to implement on cascade actions. I do understand the purpose of cascade but I've always found it better to control those processes myself.

EDIT even though I tried to explain already that you can work around the cascade issue (thus still satisfying your third condition), I thought I would add an illustration:

You can certainly still allow for orders to remain after you've deleted a customer. The key is to make the Orders.CustomerID column nullable, e.g.

CREATE TABLE dbo.Customers(CustomerID INT PRIMARY KEY);

CREATE TABLE dbo.Orders(OrderID INT PRIMARY KEY, CustomerID INT NULL
    FOREIGN KEY REFERENCES dbo.Customers(CustomerID));

Now when you want to delete a customer, assuming you control these operations via a stored procedure, you can do it this way, first setting their Orders.CustomerID to NULL:

CREATE PROCEDURE dbo.Customer_Delete
  @CustomerID INT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE dbo.Orders SET CustomerID = NULL
    WHERE CustomerID = @CustomerID;

  DELETE dbo.Customers
    WHERE CustomerID = @CustomerID;
END
GO

If you can't control ad hoc deletes from the Customers table, then you can still achieve this with an instead of trigger:

CREATE TRIGGER dbo.Cascade_CustomerDelete
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE o SET CustomerID = NULL
    FROM dbo.Orders AS o
    INNER JOIN deleted AS d
    ON o.CustomerID = d.CustomerID;

  DELETE c
    FROM dbo.Customers AS c
    INNER JOIN deleted AS d
    ON c.CustomerID = d.CustomerID;
END
GO

That all said, I'm not sure I understand the purpose of deleting a customer and keeping their orders (or any indication at all about who placed that order).

I quite do not undestand your answer because if you say YES I should use them in my scenario but this answer breaks condition "3": I want delete a customer although some orders reference this customer. Please correct your solution. — Elisabeth, Jun 10 '12 at 18:23
@Elisa Your question was "Does a foreign key make sense in this scenario when I do not use referential integrity with On Delete/Update cascade?" If that is not the question you want people to answer, please correct your question. — Aaron Bertrand, Jun 10 '12 at 18:24
maybe my sample customer has orders was stupid. Its just a sample to illustrate a problem. I just want to allow the user to delete data from parent tables but the children data should still be able to read. With on delete cascade or a foreign key. I cant do this. the excption is the nullable/optional relation from 1 customer to *..N Order. This solution just prevents that I would get an foreign key constraint-exception deleting a customer. BUT when I do not use the overhead of a FK. I do not have this trouble at all. FK`s and index on it is only usefull in that case: — Elisabeth, Jun 12 '12 at 18:44
http://stackoverflow.com/questions/7329334/in-sql-server-2008-do-relationships-make-queries-faster — Elisabeth, Jun 12 '12 at 18:44
I thought again about the nullable FKey. There is still a good and bad point about it. An order needs a customer. But with the nullable an order can exist without customer. In the context of creating data that makes no sense. In my context where I delete a customer it makes sense to get no exception. The good thing about the nullable FK is you can show relations in a db diagramm else over time you forget the relations... — Elisabeth, Jun 12 '12 at 20:21

score 0 · Answer 2 · answered Jun 10 '12 at 18:30

So to be clear you have a FK from Customer to Orders presently. Cascade update/delete is not enabled on this relationship. Your plan is to delete customers but allow the orders to remain.

This would VIOLATE the foreign key constraint; and prevent the delete from occurring. If you disable the constraint execute the delete then re-enable you could make it work.

However, this will leave orphaned order records in the system; which might make it harder to support in the long run. What's the next guy who has to support this going to think?

Wouldn't it be better to keep the records and add a status for Active/inactive or created and inactive dates?

I'm struggling with violating the integrity of the database to reduce space...? Or what's the main reason to remove?

If you don't want to have to always filter out the no longer active records use a view or a package which creates a collection of active customers. Eliminating some but not all data seems just wrong to me.

You can delete a customer and set `Orders.CustomerID` to `NULL` (one of the options for cascade). This is still an orphaned row *in my definition* but does not violate anything if the column is nullable (e.g. if in general it is ok for an order to not belong to any customer). I tend to agree with you that it makes little sense to ever delete a customer who has had at least one transaction in your system. — Aaron Bertrand, Jun 10 '12 at 18:35

Does a foreign key make sense when I do not use Referential Integrity with On Delete/Update Cascade

2 Answers2