SQLTeam.com | Weblogs | Forums

Moving Data That Has Foreign Keys

Hi experts,
I'm tasked with archiving many tables, some of which have foreign keys. (Fortunately, no FKs reference other databases)
One option I'm considering is:

  1. On the destination tables, dropping all foreign keys in all tables until after I insert the rows for all tables, then recreate all FKs.

Does this seem feasible? Can you think of a better technique?
Thanks for any tips.

Archiving tables or archiving data? Also you say "some of which have foreign keys." but then you say fk in all tables. Can you clarify? Also do you have archive tables where you dump data to? Also what is the reason behind this effort?

Thanks yosiasz.
I need to archive data older than 2 years. the reason is the tables contain 10 year old data causing them to be too large.
Only some of the tables have FKs.
Yes I have created an Archive database but the tables have the same FKs as the source/production tables have..

This is what I'm thinking of doing:
-- Turn off referential integrity on the Archive tbales

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

--load data here

-- Turn ON referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

Is this a good option?

Why do you need ref integrity or triggers in archive database?

1 Like

Good question. This is really a 2 part effort.
Build a test db which will need FKs/ref integrity
and build an Archive db which I suppose may not need FKs if the data is static.

Where will these be hosted in relation to server hosting the bloated db ?

The archive db better be static otherwise it is not archive

The archive db will be hosted on a separate server. And yes, the archive database will be static, read-only..

so this might be no longer an option?

What will you do with the data in the source tables? what is the sequence you are going to use?
#1 move data to archive tables
#2 delete from source tables making sure row has been moved to archive

I'm building the Test data and so far this technique is working:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

--load data here

As for the Archive db which I've not started yet, yes I will:
#1 copy data to archive tables
#2 delete from source tables making sure row has been moved to archive

Why are you doing that for the test database? What will that db be used for? Is that your QA/TEST version?

If you are INSERTing data table by table, then you don't need to disable FKs. Just copy the tables in an order that will allow FKs to be kept consistent.

That means basically that you have to copy all the parent tables before child tables, including any multiple levels. For example, table x has fk to table g which has fk to table d. Then you load table d first, then g, then x.

2 Likes

76 tables have foreign keys. This is a nightmare but I'm working thru them.

Here's a function that will return table names with their FK dependency level. You just need to copy level 1 tables first, then level 2s, etc..

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.Get_Tables_With_Dependency_Level()
RETURNS TABLE
AS
/* SELECT * FROM dbo.Get_Tables_With_Dependency_Level() */
RETURN (
    WITH cte_tables AS
    (
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
            OBJECT_NAME(t.object_id) AS table_name,
            t.object_id AS object_id,
            1 AS dependency_level
        FROM
            sys.tables AS t
        WHERE
            t.is_ms_shipped = 0
        UNION ALL
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id),
            OBJECT_NAME(t.object_id),
            t.object_id,
            ct.dependency_level + 1
        FROM
            sys.tables AS t
        INNER JOIN sys.foreign_keys AS f ON 
            f.parent_object_id = t.object_id AND
            f.parent_object_id != f.referenced_object_id
        INNER JOIN cte_tables AS ct ON 
            f.referenced_object_id = ct.object_id
        WHERE
            t.is_ms_shipped = 0
    )
    SELECT dependency_level, schema_name, table_name, object_id
    FROM (
        SELECT 
            ct.dependency_level,
            ct.schema_name,
            ct.table_name,
            ct.object_id
            , ROW_NUMBER() OVER(PARTITION BY ct.schema_name, ct.table_name ORDER BY ct.dependency_level DESC) AS row_num
        FROM
            cte_tables AS ct
    ) AS derived
    WHERE
        row_num = 1
)
/*end of function*/
GO
2 Likes

Thanks, ScottPletcher,
I've been using this script which gives me both table names:

SELECT C.TABLE_CATALOG [DatabaseName],
C.TABLE_SCHEMA [PKTABLE_OWNER],
C.TABLE_NAME [PKTABLE_NAME],
KCU.COLUMN_NAME [PKCOLUMN_NAME],
C2.TABLE_CATALOG [FKTABLE_QUALIFIER],
C2.TABLE_SCHEMA [FKTABLE_OWNER],
C2.TABLE_NAME [FKTABLE_NAME],
KCU2.COLUMN_NAME [FKCOLUMN_NAME],
RC.UPDATE_RULE,
RC.DELETE_RULE,
C.CONSTRAINT_NAME [FK_NAME],
C2.CONSTRAINT_NAME [PK_NAME],
CAST(7 AS SMALLINT) [DEFERRABILITY]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'
--Order By [PKTABLE_NAME];
Order By [FKTABLE_NAME];

Mine returns something called 'Deferability' but your function gives the Dependency level which is very useful. Thanks

I am not sure I see the need to move the foreign keys or triggers or anything like that to archive but to test environment, I agree. Also for test environment can you not just restore the prod database over? Why are you attempting to use the archive method for the test database?

I guess I've confused things by mixing the Archiving With Test.

My first priority is to build a test db that contains only 1 year of data. *Typically yes I would simply restore the entire prod db but that's not appropriate for this test db.

The Archiving project has not begun yet.

what will this test db be used for? and how big is the production database backup? You could have been finished by now with a restore and delete anything above a year. much simpler

@ScottPletcher, this is the script I'm using:
SELECT C.TABLE_CATALOG [DatabaseName],
C.TABLE_SCHEMA [PKTABLE_OWNER],
C.TABLE_NAME [PKTABLE_NAME],
KCU.COLUMN_NAME [PKCOLUMN_NAME],
C2.TABLE_CATALOG [FKTABLE_QUALIFIER],
C2.TABLE_SCHEMA [FKTABLE_OWNER],
C2.TABLE_NAME [FKTABLE_NAME],
KCU2.COLUMN_NAME [FKCOLUMN_NAME],
RC.UPDATE_RULE,
RC.DELETE_RULE,
C.CONSTRAINT_NAME [FK_NAME],
C2.CONSTRAINT_NAME [PK_NAME]

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' AND C2.TABLE_NAME = 'tblEstimate'
Order By [FKTABLE_NAME];

I restricted it to return info for table tblEstimate. That table has NO foreign keys but 11 other tables have FKs referencing tblEstimate.
Somehow I need to know in which sequence those 11 tables need to be copied. The script above doesn't give that cross-reference info. Without that, this effort is almost undoable.
Do you know of a way of extracting the sequence for all tables?
As I understand it, SQL Compare may be able to do that but it costs' I'm a contractor and can't buy anything.
Thanks for any tips.

I think the function above I gave you does that.

Use a WHERE condition to restrict my view results to those 11 tables. I think you can JOIN it to the query you've shown. I never use INFORMATION_SCHEMA tables in SQL Server -- because of the overhead and the potential for misreporting schema/owner info -- so I can't help you with the I_S query itself.

1 Like