why drop constraints before truncate

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Thomas Scheiderich (tfs_at_deltanet.com)
Date: 04/29/04 

Date: Wed, 28 Apr 2004 23:03:14 -0700

I have a SP that removes the foreign keys, then truncates some files and
then adds back the keys.

Why do this?

For example:

**********************************************************************************
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesCustomer
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesProduct
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesTime
GO
TRUNCATE TABLE dbo.SalesFact
TRUNCATE TABLE dbo.SalesStage
TRUNCATE TABLE dbo.CustomerDim
TRUNCATE TABLE dbo.ProductDim
GO
ALTER TABLE dbo.SalesFact ADD
    CONSTRAINT FK_SalesCustomer FOREIGN KEY
       (CustomerKey) REFERENCES dbo.CustomerDim (CustomerKey)
    ,CONSTRAINT FK_SalesProduct FOREIGN KEY
       (ProductKey) REFERENCES dbo.ProductDim (ProductKey)
    ,CONSTRAINT FK_SalesTime FOREIGN KEY
       (TimeKey) REFERENCES dbo.TimeDim (TimeKey)

*********************************************************************************

Also, if I am going to do a large amount of inserts (say about 500,000
or more), would it be better to do the inserts first and then put the
keys back at that time?

Thanks,

Tom.

Relevant Pages