Tuesday, March 06, 2007

How to insert data in table refrenced by Foreign Key Constraints!!

Problem: Table in question is referenced by Foreign Key constraint by another tables.
Workaround: Drop the foregin key constraints from the tables and insert the data and then recreate the constraint.Solution: Following steps to be followed:
1. Create the table structure script of all the tables from which Foreign Key constraint to be dropped.
2. Drop the foreign Key constraint from all the tables which refers to the questioned table.
3. Copy data to the table without any changes to the structure.
4. Create the Foreign key Constraint back to the tables used in step 1.
Script to alter the Foreign Key Constraint:
1. To drop the constraint
ALTER TABLE [dbo].[Table_Name]DROP CONSTRAINT [Constraint_Name]
2.To create back the constraint
ALTER TABLE [dbo].[Table_Name1] ADD CONSTRAINT [Constraint_Name] FOREIGN KEY ( [Field_Name1] ) REFERENCES [dbo].[Table_Name2] ( [Field_Name2] )GO


Any other Ideas...keep posting

1 comment:

Anonymous said...

You write very well.