How to update 2 columns in 2 tables with foreign key

Question

I know that the question of how to update multiple tables in SQL has been asked before, and the general answer seems to make them separately in the transaction.

However, the two columns that I need to update have a foreign key, so they cannot be updated separately.

eg.

Table1.a is a foreign key for Table2.a

One of the entries in the tables is invalid, for example. both columns are "xxx" and must be "yyy"

How to update Table1.a and Table2.a to be "yyy"?

I know that I could temporarily remove the key and replace, but, of course, a different way.

thank

Answer 1

, SQL . , ON UPDATE CASCADE

.

ALTER TABLE YourTable
ADD CONSTRAINT FK_YourForeignKey
FOREIGN KEY (YourForeignKeyColumn) 
REFERENCES YourPrimaryTable (YourPrimaryKeyColumn) ON UPDATE CASCADE

Answer 2

: http://msdn.microsoft.com/en-us/library/ms174123%28v=SQL.90%29.aspx

, table_constraint ON UPDATE CASCADE

          CREATE TABLE works_on1
         (emp_no INTEGER NOT NULL,
          project_no CHAR(4) NOT NULL,
          job CHAR (15) NULL,
          enter_date DATETIME NULL,
          CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),
          CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no) REFERENCES employee(emp_no) ON DELETE CASCADE,
          CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADE)

,

. :

ON DELETE ON UPDATE, CASCADE, , . SQL Server Compact Edition , .

, , A B, . A B: A.ItemID foreign B.ItemID.

UPDATE B ON UPDATE CASCADE A.ItemID, SQL Server Compact A. , A , .

, NO ACTION, SQL Server Compact Edition B, A , .

Answer 3

, .

First, you do not update the parent table, you add a new record with the value you want (with the same data as the other record for all other fields). Then it is not difficult for you to update child tables to use this value instead of this value. In addition, you now have the opportunity to do the work in batches to avoid blocking the system while changes are propagated through it. When all child tables are updated, you can delete the original bad record.