How to delete a record while a foriegn key is there
-
Hi
, I wants to delete a single record from
my master table and again insert it with
the same primary key values
but a related child table is also there so
what can i do, that i can delete
the record from master table than again
insert it...
Plz Help me out
Thanks & Regards,
Raj - 1.Disable the foreign key constraint,2. delete the records3. insert the records4. enable the foreign key contraint.
-
Hi Newbie,
Just adding to wt Deepak has mentioned. Disable the constraint first, then copy the row into one more table. Delete this row.
Incase your primary key field happens to be an 'Identity column', then you need to run this command before inserting the values into the master table. This will allow you to explicitly specify values for your identity field.
Set Identity_insert <table name> On
go
run the above two lines. Once this is executed sucessfully, you can insert the record with the original value which you have.
Scorp
-
It is hard to make a recommendation without knowing what you are trying to accomplish.
You could DELETE the matching records from the child table first, this would allow you to DELETE the row from your master table.
OR, since you want to DELETE and then reinsert the same keyed value, you can use UPDATE to modify all the fields EXCEPT the primary key field, this would accomplish the same effect with less work.
-
I agree with Ryan, why not just do an UPDATE on the table?
-
I also agree.. Insert into a dummy table, then do an update on your main table joining to the dummy table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply