1

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.

I read about "ON CASCADE DELETE" but i think that only works for children tables.

Example table:

Example table

I had one where there was only one FK and i used the following query:

DELETE  

FROM    guide_category

WHERE   

id_guide NOT IN

        (
        SELECT  id_guide
        FROM    guide
        )

This could work but i don't its feasible with many FKs.

The data importance is actually zero (test DB) i just want small DB and all tables to be trusted for better performance.

Am i missing an easy solution to this problem? or maybe even seeing a problem that isn't there.

2
  • 1
    What's the actual problem you're facing? You want to delete some records - I can see that part. What errors have you received? – George.Palacios Sep 27 '19 at 13:33
  • You might find this helpful. – Vérace Sep 27 '19 at 14:24
1

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:

  1. Drop all Referential Integrity constraints
  2. Truncate all tables (this is why we disable the check constraints)
  3. Recreate all Referential Integrity constraints
  4. 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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.