Handling Foreign Keys – ETL

Issue:

About to delete 1Billion record from the table. Goal is to improve ETL process time. Foreign keys are good way to maintain data integrity , there are occasions like this one where you want to disable foreign keys in-order to avoid data integrity check and improve overall performance.

Solution:

Disable foreign key :

Alter table MyTable2 nocheck constraint FK_MyTable2_MyTable1

Enable Foreign Key:

Key word ‘check’ is used to enable the constraint, this does not validate existing data but will enforce data integrity for future data changes.

Disable:

Alter table MyTable2  nocheck constraint FK_MyTable2_MyTable1

–is_disabled = 1 and is_not_trusted =1

Enable:

This will enable FK and validate existing data hence is_disabled = 0 and is_not_trusted =0 .

Alter table MyTable2 with check check constraint FK_MyTable2_MyTable1

–is_disabled = 0 and is_not_trusted =0

Enable without validation:

This will enable FK and without validating existing data hence is_disabled = 0 and is_not_trusted =1 .Optimizer will ignore these types of FK,this is just to maintain future data modifications

Alter table MyTable2 with nocheck check constraint FK_MyTable2_MyTable1

–is_disabled = 0 and is_not_trusted =1

 

 

 

Leave a Reply