SQL Server – Delete Records in Tables with Foreign Key Reference

deleteforeign keysql server

Sorry if the title to this question makes no sense. I really wasn't sure how else to word what I needed to do.

So I have the following tables in my database:

customers
    customer_id (PK)
    customer_name
    website

customer_address
    customer_id (FK) (PK-A)
    street (PK-B)
    city
    region
    code

customer_contact
    contact_id (PK)
    contact_name
    customer_id (FK)

customer_contact_phone
    contact_id (FK) (PK-A)
    phone_number (PK-B)
    extension

I am trying to set up a procedure which would allow me to delete all the data pertaining to a specific customer. Now I know how to delete any or all of the addresses pertaining to the customer whose data i am trying to delete. However, I am not know how to properly delete all of the contact data.

My issue is that there can be multiple contacts for a customer, and each contact can have multiple phone numbers. I am not sure how to go about setting up these records for deletion because of how many foreign keys are set up.

Best Answer

You just have to traverse the relationship backwards, deleting from the last child first, then the next child, and so on.

Let's say you want to delete all the data for a specific customer, you could have this procedure:

CREATE PROCEDURE dbo.ObliterateACustomer
  @delete_me int
AS
BEGIN
  SET NOCOUNT ON;

  DELETE dbo.customer_contact_phone 
    WHERE contact_id IN
    (
       SELECT contact_id 
         FROM dbo.customer_contact 
         WHERE customer_id = @delete_me
    );

  DELETE dbo.customer_contact WHERE customer_id = @delete_me;

  DELETE dbo.customer_address WHERE customer_id = @delete_me;

  DELETE dbo.customer WHERE customer_id = @delete_me;
END
GO

For the delete from a relation, there are several ways to do it. IN is arguably the most straightforward, but you can also delete from a join:

DELETE p
FROM dbo.customer_contact_phone AS p
INNER JOIN dbo.customer_contact AS c
  ON c.contact_id = p.contact_id
  WHERE c.customer_id = @delete_me;

Or using EXISTS:

DELETE p
FROM dbo.customer_contact_phone AS p
WHERE EXISTS
(
  SELECT 1 FROM dbo.customer_contact AS c
  WHERE c.contact_id = p.contact_id
    AND c.customer_id = @delete_me
);

You could even use MERGE, but I'm not going to show you how, because I think it's often a terrible idea. :-)

Related Question