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:
- 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 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…
Comments
Post a Comment