1

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
JackAce
  • 1,407
  • 15
  • 32
Levi Lenaers
  • 11
  • 1
  • Since you can only ever delete from **one table** in a statement, you won't be able to do this "in one go" - you need separate deletes for each table – marc_s Oct 05 '21 at 10:27
  • 2
    @marc_s, Or, redefine the referential integrity constraint (foreign key) with the option CASCADE DELETE. Though, I personally almost never use it in my database. It is rather dangerous. I prefer to explicitly delete child rows. – Vladimir Baranov Oct 05 '21 at 10:32
  • 1
    @VladimirBaranov: absolutely - but that's something you typically have to do **upfront** - when building / designing the database – marc_s Oct 05 '21 at 10:39
  • You could create a stored procedure which dynamically builds SQL statements using sys.foreign_keys, sys.foreign_key_columns and sys.tables and then executes the statement. However, I would very much recommend against it. If you were to build a system where it is safe to delete foreign keys along with a record, then create a delete script specific to that situation. Don't create one that is generic to any database/table. – CMGeek Oct 05 '21 at 11:18

2 Answers2

1

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

vahidrobati
  • 71
  • 5
  • 2
    Why is the the best way? CASCADE option has several limitations, considering all of these still make it the best? – Pred Oct 05 '21 at 11:36
  • Hi again. Because it is understood from the text of the question that the person asking the question does not know the structure of the database well and does not know where the end of these dependencies and foreign keys. but if you are sure about the structure of the database, It's better to find dependencies first and delete data Hierarchical. – vahidrobati Oct 06 '21 at 08:35
  • 1
    Don't you need to understand the structure to add the cascade rule? Not mentioning, that you can't add CASCADE rules in all cases. (Side note: Isn't the image you are using was copied from the answer on another question? https://stackoverflow.com/a/4454994/3469391) – Pred Oct 06 '21 at 09:19
1

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
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Yeah, I know the OP is fiddling around in Northwind, but remember that many future readers will stumble onto this question and find great weapons of mass destruction. – StuartLC Oct 12 '21 at 11:17