How is data being removed from tables that are not defined in a delete or truncate statement?

Question

Given a database with many tables, I have a script populated with several delete and truncate statements. I do not have a delete statement or truncate statement for each table in my database. The script looks like the following (I have changed names and shortened the length):

TRUNCATE table db.dbo.table1
TRUNCATE table db.dbo.table2
TRUNCATE table db.dbo.table3

--cannot truncate table because it is being referenced by a FK constraint
--TRUNCATE table db.dbo.table4
--TRUNCATE table db.dbo.table5
--TRUNCATE table db.dbo.table6
--TRUNCATE table db.dbo.table7

DELETE FROM db.dbo.table4
DBCC CHECKIDENT ('db.dbo.table4',RESEED, 0)
DELETE FROM db.dbo.table5
DBCC CHECKIDENT ('db.dbo.table5',RESEED, 0)
DELETE FROM db.dbo.table6
DBCC CHECKIDENT ('db.dbo.table6',RESEED, 0)
DELETE FROM db.dbo.table7
DBCC CHECKIDENT ('db.dbo.table7',RESEED, 0)
DELETE FROM db.dbo.table8
DELETE FROM db.dbo.table9
DELETE FROM db.dbo.table10
DELETE FROM db.dbo.table11
DELETE FROM db.dbo.table12
DELETE FROM db.dbo.table13
DELETE FROM db.dbo.table14
DELETE FROM db.dbo.table15

After executing the script, I am finding that data is being removed from tables that are not included in the script.

To verify which tables have data, I am using the following query provided in a SO answer found here.

SELECT 
    t.NAME AS TableName,
    SUM(p.rows) AS [RowCount]
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    SUM(p.rows) DESC

I have a backup of the database. Next I:

1. restore the database
2. run 'table data' query: to double check that data does really exists in tables not included in the delete/truncate script
3. execute delete/truncate script
4. run 'table data' query

My findings are as such: data is being removed from tables that are not defined in my delete/truncate script.

My question: Given I am deleting or truncating from more than one and not all tables in a database, how is data being removed from the tables that are not defined in a delete or truncate statement?

Answer 1

A data row can be removed from a table that is not defined in a delete or truncate statement if the table contains a foreign key that has a cascading referential integrity constraint action value of CASCADE.

I found the following Microsoft documentation located here helpful.

Cascading Referential Integrity
By using cascading referential integrity constraints, you can define the actions that the Database Engine takes when a user tries to delete or update a key to which existing foreign keys point. The following cascading actions can be defined.
...
CASCADE
Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key. ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.
...

Next check to see if the table, data is being deleted from, has a foreign key cascading referential integrity constraint and what the action value is.

Method 1: Query it. I am using the following query provided in a SO answer found here.

SELECT name, delete_referential_action_desc
FROM sys.foreign_keys

Method 2: Using Object Explorer in Microsoft SQL Server Management Studio 2016:

1. navigate to the table that data is being deleted from
2. expand table object
3. expand 'Keys' folder
4. right click a foreign key and click modify
5. expand INSERT And UPDATE Specific section located in right lower window pane
6. identify Delete Rule value

I found that the table, data is being deleted from, has a foreign key cascading referential integrity constraint with a value of Cascade.

Furthermore, I tested to see if this was the actual cause. I updated the value to No Action, restored the database, executed the delete/truncate script, ran 'table data' query, and found the data persisted.