How to clear data from tables with foreign key contraints in SQL Azure

Question

I need to delete all the data from some tables. DELETE FROM TableTwo, DELETE FROM TableOne etc works (it doesn't reseed but I can learn to live with that).

I would like to do this by truncating the tables (because it's faster and I'm certain that when I'm done emptying these tables the data integrity will fine), however TableOne is a dependency of TableTwo, so a naive approach would give me errors "cannot truncate... FOREIGN KEY constraint".

I looked in the constraints folder and tried this:

ALTER TABLE [TableOne] DROP CONSTRAINT [DF__Blahblah__38EE7070]
GO
TRUNCATE TABLE [TableOne]
GO
ALTER TABLE [TableOne] ADD  DEFAULT ((0)) FOR [Something]
GO

However it gives the same error. Also 38EE7070 is unknown to me unless I manually check so I would have run into problems actually using this code anyway.

I wonder if I'm leaving other unknown constraints untouched with, but, confusingly enough, when I try to check EXEC sp_fkeys 'TableOne' it shows empty results.

Given the limitations of both sql server and azure a lot of the solutions in other questions don't seem to be workable. Does anyone know how I could proceed? Ie how do I drop and recreate these keys?

Answer 1

This is not my original work (I would like to give credit to the author but don't remember from where I snagged it) but I am posting it because it has found FK constraints on tables for me in SQL 2012 as well as Azure SQL Database:

SELECT OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME (f.referenced_object_id) = 'YourTableNameHere' order by TableName

Answer 2

Nathan,

There are a couple of things I should point out...

1) The example code you posted is dropping and creating a DEFAULT CONSTRAINT (i.e. something that sets the default value for a column if you do not specify a value during an insert). A default constraint has no affect on the ability to truncate a table (as you rightly point out, a table that has Foreign Key constraints cannot be truncated).

2) I think sp_fkeys was for SQL 2000 and therefore may not work anymore in later versions of SQL Server (even though it still exists).

Below I have attached a script I use to identify all Foreign Key constraints on a table, and generate Create, Drop and Check statements. Although I haven't ever used it on SQL Azure, I have used it many times in a Production enivronment for SQL Server 2008 R2.

I hope it helps. Let me know if you have any questions.

Ash

CREATE FUNCTION [utils].[uf_ForeignKeyScripts]
(
@PrimaryKeyTable varchar(128), @PrimaryKeyTableSchema varchar(32)
)
RETURNS @Scripts TABLE 
    (
    ForeignKeyName          varchar(128)
,   IfExistsStatement       varchar(1000)
,   DropStatement           varchar(1000)
,   IfNotExistsStatement    varchar(1000)
,   CreateStatement         varchar(1000)
,   CheckStatement          varchar(1000)
,   NoCheckStatement        varchar(1000)
    )
    AS

/*
This function returns statements used to create, drop, and check all Foreign Key constraints that reference a given table.
These statements can be then added to T-SQL scripts.

Example usage (ensure selection of the Results to Text option in SSMS) :

1) To create statements to check all foreign keys

SELECT
    IfExistsStatement + CHAR(13) +
    CHAR(9) + CheckStatement + CHAR(13)
FROM
    utils.uf_ForeignKeyScripts('t_Dim_Date','dbo')      
    ;

This will return a formatted statement to check the existence of a foreign key and if it exists, check that data does not violate the key.

*/

BEGIN
    INSERT INTO
        @Scripts
        (
        ForeignKeyName
    ,   IfExistsStatement
    ,   DropStatement
    ,   IfNotExistsStatement
    ,   CreateStatement
    ,   CheckStatement
    ,   NoCheckStatement
        )
    SELECT
        FK.name AS ForeignKeyName
    ,   'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' + SFK.name + '.' + FK.name + ''') ' + 
        'AND parent_object_id = OBJECT_ID(N''' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + '''))'
        AS IfExistsStatement
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' + 
        'DROP CONSTRAINT ' + FK.name + CHAR(13) + ';' 
        AS DropStatement
    ,   'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' + SFK.name + '.' + FK.name + ''') ' + 
        'AND parent_object_id = OBJECT_ID(N''' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + '''))'
        AS IfNotExistsStatement 
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' +
        'WITH CHECK ADD CONSTRAINT ' + FK.name + ' ' + 
        'FOREIGN KEY (' + C.FKColumns + ') ' + 
        'REFERENCES ' + ST.name + '.' + OBJECT_NAME(fk.referenced_object_id) + ' ' + 
        '(' + C.FKColumns + ')' + CHAR(13) + ';' 
        AS CreateStatement
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' +
        'CHECK CONSTRAINT ' + FK.name + CHAR(13) + ';'
        AS CheckStatement
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' +
        'NOCHECK CONSTRAINT ' + FK.name +   CHAR(13) + ';'
        AS NoCheckStatement
    FROM
        sys.foreign_keys AS FK
    INNER JOIN
        sys.schemas AS SFK -- schema of foreign key table
    ON
        FK.schema_id = SFK.schema_id
    INNER JOIN
        sys.tables AS T -- primary key table
    ON
        FK.referenced_object_id = T.object_id
    INNER JOIN
        sys.schemas AS ST -- schema of primary key table
    ON
        T.schema_id = ST.schema_id
    CROSS APPLY
        (
        /* Get all columns to handle composite keys */
        SELECT 
            SFKC.constraint_object_id
        ,   utils.uf_ConcatanateStringWithDelimiter(COL_NAME(SFKC.referenced_object_id, SFKC.referenced_column_id),', ') AS FKColumns
        FROM
            sys.foreign_key_columns AS SFKC
        WHERE
            SFKC.constraint_object_id = FK.object_id
        GROUP BY
            SFKC.constraint_object_id
        )
        AS C
    WHERE
        OBJECT_NAME(T.object_id) = @PrimaryKeyTable
    AND ST.name = @PrimaryKeyTableSchema
    ;
    RETURN 
END

Answer 3

The only way is to drop the foreign key, truncate the table, and then re-create the foreign key. For an in-house solution, you would probably take this approach in order to account for the transaction log’s size and performance. In SQL Azure, however, you’re not concerned with the transaction logs.