How to update primary key

sql-server sql-server-2008 foreign-keys composite-primary-key
Slavisa picture Slavisa · Mar 23, 2010 · Viewed 169.5k times · Source

Here is my problem - I have 2 tables:

  1. WORKER, with columns |ID|OTHER_STAF| , where ID is primary key
  2. FIRM, with columns |FPK|ID|SOMETHING_ELSE| , where combination FPK and ID make primary key, and also ID is a foreign key referenced to WORKER.ID (not null, and must have same value as in WORKER).

I want to make stored procedure UPDATE_ID_WORKER, where I would like to change the value of specific ID in WORKER, and also in all instances of specific value of ID in FIRM.

Answer

kevchadders picture kevchadders · Mar 23, 2010

You shouldn't really do this but insert in a new record instead and update it that way.
But, if you really need to, you can do the following:

  • Disable enforcing FK constraints temporarily (e.g. ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)
  • Then update your PK
  • Then update your FKs to match the PK change
  • Finally enable back enforcing FK constraints

Related questions

SQL Multiple Foreign Keys as Primary Keys

If I declare the table below does it implicitly imply that both the foreign keys make a unique primary key or do I need to do something more to make both attributes as a primary key? CREATE TABLE Report_has_…

Read More
How to disable Constraints for all the tables and enable it?

I have 60 tables. I want to drop 10 tables where these 10 tables are Constraints(PK,FK) to other 20 tables. While dropping these 10 tables, I need to truncate or delete data from the other 20 tables. Finally I want to disable all 60 table …

Read More
How to put foreign key constraints on a computed fields in sql server?

Table A has a computed field called Computed1. It's persisted and not null. Also, it always computes to an expression which is char(50). It's also unique and has a unique key constraint on it. Table B has a field RefersToComputed1, …

Read More