CODEWITHSUNDEEP
sql-server
Levi Lenaers
Levi Lenaers

Reputation: 11

SQL Server : delete everything from the database for a specific user

Just playing around in SQL Server to get better with query writing. I'm using the Northwind sample database from Microsoft.

I want to delete 'Robert King', EmployeeID = 7.

So normally I would do:

DELETE FROM Employees 
WHERE EmployeeID = 7

but it's linked to another table and throws

The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Employees". The conflict occurred in database "Northwind", table "dbo.Orders", column 'EmployeeID'

So I have to delete the rows from the Orders table first, but I also get an error because the order ID are linked to yet another table [Order Details].

How can I delete everything at once?

I have a query what shows me everything for EmployeeID = 7, but how can I delete it in one go?

Query to show all data for EmployeeID = 7:

SELECT 
    Employees.EmployeeID,
    Orders.OrderID, 
    Employees.FirstName,
    Employees.LastName 
FROM 
    Employees
INNER JOIN
    Orders on Employees.EmployeeID = Orders.EmployeeID
INNER JOIN
    [Order Details] on orders.OrderID = [Order Details].orderID
WHERE
    Employees.EmployeeID = 7

Upvotes: 1

Views: 818

Answers (2)

StuartLC
StuartLC

Reputation: 107237

Don't do Physical Deletes of important data on a Source Of Truth RDBMS

If this an OLTP system, then what you are suggesting, i.e. deleting OrderId rows linked to an employee, looks dangerous as it could break the data integrity of your system.

  • An OrderDetails row is likely also foreign keyed to a parent Orders table. Deleting an OrderDetails row will likely corrupt your Order processing data (since the Order table Totals will no longer match the cumulative line item rows).
  • By deleting what appears to be important transactional data, you may be destroying important business records, which could have dire consequences for both yourself and your company.
  • If the employee has left service of the company, physical deletion of data is NOT the answer. Instead, you should reconsider the table design, possibly by using a soft delete pattern on the Employee (and potentially associated data, but not likely important transactional data like Orders fulfilled by an employee). This way data integrity and audit trail will be preserved.
  • For important business data like Orders, if the order itself was placed in error, a compensating mechanism or status indication on the order (e.g. Cancelled status) should be used in preferenced to physical data deletion.

Cascading Deletes on non-critical data

In general, if DELETING data in cascading fashion is a deliberate, designed-for use case for the tables, the original design could include ON DELETE CASCADE definitions on the applicable foreign keys. To repeat the concerns others have mentioned, this decision should be taken at design time of the tables, not arbitrarily taken once the database is in Production.

If the CASCADE DELETE triggers are not defined, and your team is in agreement that a cascading delete is warranted, then an alternative is to run a script or better, create a stored procedure) which simulates the cascading delete. This can be somewhat tedious, but providing all dependency tables with foreign keys ultimately dependent on your Employee row (@EmployeeId), the script is of the form (and note that you should define a transaction boundary around the deletions to ensure an all-or-nothing outcome):

    BEGIN TRAN
        -- Delete all Nth level nested dependencies via foreign keys
        DELETE FROM [TableNth-Dependency]
        WHERE ForeignKeyNId IN
        (
            SELECT PrimaryKey
            FROM [TableNth-1 Dependency]
            WHERE ForeignKeyN-1 IN
            (
                SELECT PrimaryKey
                FROM [TableNth-2 Dependency]
                WHERE ForeignKeyN-3 IN
                (
                    ... Innermost query is the first level foreign key
                    WHERE
                        ForeignKey = @PrimaryKey;
                )
            )
        );

        -- Repeat the delete for all intermediate levels. Each level becomes one level simpler

        -- Finally delete the root level object by it's primary key
        DELETE FROM dbo.SomeUnimportantTable
            WHERE PrimaryKey = @PrimaryKey;

    COMMIT TRAN

Upvotes: 1

vahidrobati
vahidrobati

Reputation: 81

can you change the design of database? if you have access to change, The best way is to set "cascade" type for delete operation for employee table. like This

Upvotes: 1

Related Questions