Tuesday, June 12, 2012

Truncating tables which are bounded with foreign keys


Truncating a table having primary key and the field is referenced to the some other field of some other table for the foreign key relationship is not possible by using ‘Truncate’ command alone.  This will gives the following error

Cannot truncate table 'tbl_Parent' because it is being referenced by a FOREIGN KEY constraint.


Here I’ll explain how to do this with an example

Steps:
  1.   Delete all the foreign key constraints
  2.  Truncate the tables
  3.  Again create all the foreign key constraints

These are the steps for clearing the database values using the truncate command while using foreign keys.
Example:
Lets create a database as in the following diagram



Here the ParentId in the tbl_Child is the foreign key for the field ParentId in the tbl_Patrent which is a primary key.


Not for truncating all the tables the procedure will be as follows


CREATE PROC sp_DropDB

AS
BEGIN

      BEGIN TRY
     
            BEGIN TRAN
                 
                  ALTER TABLE tbl_Child
                  DROP CONSTRAINT fk_Child_ParentId
                 
                  TRUNCATE TABLE tbl_Child
                  TRUNCATE TABLE tbl_Parent
                 
                  ALTER TABLE tbl_Child
                  ADD CONSTRAINT fk_Child_ParentId FOREIGN KEY(ParentId) REFERENCES tbl_Parent(ParentId)
                 
            COMMIT TRAN
     
      END TRY
     
      BEGIN CATCH
            ROLLBACK TRAN
      END CATCH

END

By executing the above procedure we can clear all the values in the database


Happy Coding…