There's a lot going on in your question, so I'm going to break down my response into sections.
I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable FKs to do that, i fear they will become untrusted and the parent tables will be in conflict.
You do NOT need to disable Foreign Keys in order to delete data from tables that have Referential Integrity defined; you just need to delete data from the child table(s) BEFORE you delete referenced data from the parent table. When you disable the Foreign Key before issuing the DELETEs and then delete data only from a parent table, you will most certainly run the risk of having orphaned records. Trying to re-enable the FKey constraint would also throw an error in this scenario. This MSSQL Tips article does a decent enough job walking through why this is important, so I won't rehash it here.
I read about "ON CASCADE DELETE" but i think that only works for children tables.
The cascaded deleted would identify if the table you're currently trying to delete data from is a parent table and issue appropriate deletes from underlying child tables prior to removing the records from the parent table. All of this activity is nested within an implicit transaction, so depending on how much child-level data references said parent records, you may find yourself running a long-running transaction that generates a lot of blocking. There are a number of issues with cascaded deletes (and updates), but taking this approach is a personal preference. I think David Spillet's answer to this dba.se question does a good job of touching some of the risks/rewards with cascaded statements.
Am i missing an easy solution to this problem?
Yes, if this is a test environment and you don't care about the data, the fastest approach would be what I would consider the nuclear option, as follows:
- Drop all Referential Integrity constraints
- Truncate all tables (this is why we disable the check constraints)
- Recreate all Referential Integrity constraints
- Insert any default data back into the tables of your choosing
The following script will accomplish this without much effort (or any checking). Be careful with this; it is indiscriminate and will wipe all user data from your database without a second thought:
DECLARE @objectName NVARCHAR(1024), @fkeyName NVARCHAR(256), @refName NVARCHAR(1024), @colSelf NVARCHAR(MAX), @colReference NVARCHAR(MAX), @dSQL NVARCHAR(MAX)
-- Walk through the tables dropping RI
DECLARE fkeyDefinitions CURSOR STATIC
FOR
-- Based on query lifted from RJB's SO Post: https://stackoverflow.com/a/36818102/944748
SELECT '[' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + OBJECT_NAME(t.object_id) + ']' AS objectName
, fk.name
, '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + OBJECT_NAME(fk.referenced_object_id) + ']' AS refName
, STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_self
, STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_reference
FROM sys.tables t
LEFT JOIN sys.foreign_keys fk
ON t.object_id = fk.parent_object_id
WHERE t.type = 'U'
--AND t.name LIKE 'blah%' -- for only a subset of tables, uncomment/add more conditions to the predicate
-- go through the fkey cursor and drop all RI
OPEN fkeyDefinitions
FETCH NEXT FROM fkeyDefinitions
INTO @objectName, @fkeyName, @refName, @colSelf, @colReference
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dSQL = 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT [' + @fkeyName + ']'
PRINT @dSQL
EXEC(@dSQL)
FETCH NEXT FROM fkeyDefinitions
INTO @objectName, @fkeyName, @refName, @colSelf, @colReference
END
-- go through new cursor truncating all the tables
DECLARE truncUserTables CURSOR
FOR
SELECT '[' + OBJECT_SCHEMA_NAME(object_id) + '].[' + OBJECT_NAME(object_id) + ']' AS objectName
FROM sys.tables
WHERE type = 'U'
--AND t.name LIKE 'blah%' -- for only a subset of tables, uncomment/add more conditions to the predicate
OPEN truncUserTables
FETCH NEXT FROM truncUserTables
INTO @objectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dSQL = 'TRUNCATE TABLE ' + @objectName
PRINT @dSQL
EXEC(@dSQL)
FETCH NEXT FROM truncUserTables
INTO @objectName
END
CLOSE truncUserTables
DEALLOCATE truncUserTables
-- go through the fkey cursor a second time re-creating RI
FETCH FIRST FROM fkeyDefinitions
INTO @objectName, @fkeyName, @refName, @colSelf, @colReference
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dSQL = 'ALTER TABLE ' + @objectName + ' ADD CONSTRAINT [' + @fkeyName + '] FOREIGN KEY (' + @colSelf + ') REFERENCES ' + @refName + '(' + @colReference + ')'
PRINT @dSQL
EXEC(@dSQL)
FETCH NEXT FROM fkeyDefinitions
INTO @objectName, @fkeyName, @refName, @colSelf, @colReference
END
CLOSE fkeyDefinitions
DEALLOCATE fkeyDefinitions
I even setup a dbfiddle.uk demo if you want to see it in action.
