Posts

Showing posts from June, 2012

Truncating tables which are bounded with foreign keys

Image
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:    Delete all the foreign key constraints   Truncate the tables   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 T