change primary key value

问题:

I have 10 tables. Each table referenced by foreign keys of other 5 tables.

I need to change the primary key value of those 10 tables. Is there any way to change it so that it will change automatically all the foreign keys?

I am using sql server 2008 and have the management studio.

回答1:

You need to set ON UPDATE CASCADE for those foreign keys:

ALTER TABLE bar
ADD CONSTRAINT FK_foo_bar
FOREIGN KEY (fooid) REFERENCES foo(id)
ON UPDATE CASCADE

Then you simply update the FKs and referring fields will also be updated as part of the transaction:

UPDATE foo SET id = id + 1000

Note that to alter constraints they need to be dropped.

回答2:

Here is a sample how you can do it using the ON UPDATE CASCADE foreign key option. The part you'll be interested in are the two ALTER TABLE statements.

If you are using IDENTITY columns for your primary keys then this becomes more difficult as you can't update an IDENTITY column.

CREATE TABLE Parent
(
    ParentId INT NOT NULL CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED,
    Name VARCHAR(10) NOT NULL
)

CREATE TABLE Child 
(
  ChildId INT NOT NULL CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED,
  ParentId INT NOT NULL CONSTRAINT [FK_Child_ParentId] FOREIGN KEY REFERENCES Parent (ParentId),
  Name VARCHAR(10) NOT NULL
)

INSERT INTO Parent (ParentId, Name) VALUES (1, 'Bob')
INSERT INTO Parent (ParentId, Name) VALUES (2, 'Sue')

INSERT INTO Child (ChildId, Name, ParentId) VALUES (1, 'Alice', 1)
INSERT INTO Child (ChildId, Name, ParentId) VALUES (2, 'Billy', 2)

SELECT * FROM Child 

-- Drop foreign key constraint and re-add 
ALTER TABLE Child 
  DROP CONSTRAINT [FK_Child_ParentId]

ALTER TABLE Child
 ADD CONSTRAINT [FK_Child_ParentId] 
    FOREIGN KEY (ParentId) REFERENCES Parent (ParentId) ON UPDATE CASCADE 

UPDATE Parent SET ParentId = ParentId + 100 

SELECT * FROM Child --shows the new ParentIds 

DROP TABLE Child 
DROP TABLE Parent 

回答3:

I have never done this myself, and it sounds like it might be a bad idea. That said, I did find this article which goes over two methods for doing this:

http://support.microsoft.com/kb/142480

One uses stored procs and the other triggers. Both seem like a bit of a pain.

回答4:

It's a significant disadvantage of an IDENTITY column that it can't be directly updated.

A workaround is not to use IDENTITY in the target table but put it in an extra table instead. Insert to the table with the IDENTITY column first, then insert the generated IDENTITY value to your target table.

SQL Server 2012 introduces table-independent Sequences, which are a better solution to the same problem. A sequence doesn't require the extra table.

回答5:

I know this is not really the answer to your question but I came here while looking for how to simply set (write, insert, update or else) a PK (primary key) column.

So you have to disable the PK constraint, insert your value, and enable it afterward, updates will not work.

SET IDENTITY_INSERT IdentityTable ON
INSERT IdentityTable(TheIdentity, TheValue) VALUES (3, 'First Row')
SET IDENTITY_INSERT IdentityTable OFF