0

I have a main table with many associated tables linked to it using an "id" foreign key.

I need to update a row in this main table.

Instead of updating all the fields of the row, one by one, it would be easier for me to simply deleting the whole row and recreating it with the new values (by keeping the original primary key!).

Is there a way, inside a transaction, to delete such row that has foreign key constraints if the row is recreated, with the same primary key, before the transaction is actually commited?

I tried it, and it doesn't seem to work...

Is there something I can do to achieve that other than dropping the constraints before my DELETE operation? Some kind of lock?

electrotype
  • 8,342
  • 11
  • 59
  • 96
  • 1
    While this is sort of an interesting question, I'm curious about your premise. What is it that makes an update harder than doing this transactional backbend? – Eric Brandt Apr 09 '18 at 18:38
  • See https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql – RnP Apr 09 '18 at 18:53
  • @EricBrandt Mostly by curiosity. But sometimes I find myself to already have some `delete` and `create` functions for an entity... I was wondering if I could use them to *modify* this entity, without creating another function. Also, I try to not repeat the code often shared by a `create` and a `modification` function. But in the end, I'll probably convert the `create` functions to `upsert` versions... Maybe you have a better suggestion? – electrotype Apr 09 '18 at 19:27

1 Answers1

1

No.

Without dropping/disabling the constraint, SQL Server will enforce the relationship and prevent you from the deleting the referenced row.

It is possible to disable the constraint, but you'll incur the overhead when enabling it that SQL Server must verify EVERY REFERENCE to that key before it will consider the relationships trusted again.

You are much better off taking the time to develop a separate update/upsert function than to incur that additional processing overhead every time you need to change a record.

You could alter the foreign key to use a CASCADE DELETE, but that has its own overhead and baggage.

Wes H
  • 4,186
  • 2
  • 13
  • 24