Insert/delete with Foreign Key
-
I am having problems writing records to an archive db due to foriegn key constraint. I am tring
to move old records off one db to another but the 2 tables are connected by a foreign key.
The parent table (Notify) has Notify_id as a primary key and the child table (Notify_attempt) has
Attempt_id as the key and a Notify_id as a Foreign Key.
CONSTRAINT [PK_tblNotify_Attempt] PRIMARY KEY CLUSTERED
(
[Attempt_Id]
 
WITH FILLFACTOR = 90 ON [MD2archUserData] ,CONSTRAINT [FK_tblNotify_Attempt_to_tblNotify] FOREIGN KEY
(
[Notify_Id]
 
REFERENCES [tblNotify] ([Notify_Id]
 
I want to move the fields and delete those records but can't get the insert to work on the
2nd table NOR can I get the delete to work.
any ideas?
-
So you'll need to copy the parent row first to the target parent-table, then copy the child row to the target-child-table and then delete the child row from the source-child-table. Then only perform the delete of the source-parent-table-row if there are no more source-child-rows for that parent-row. You may want to do this using a while-loop.
e.g.-delete
delete from source-parent-table
where not exists (select * from source-child-table C where FK_key = source-parent-table.key)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi
Remember that FK is relational integrity enforcement.
In this case, try to delete all records in child table before try to delete records in parent table.
Seen in BOL.
Hildevan O Bezerra
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply