How to update 2 columns in 2 tables that have foreign key

Question

I know the question of how to update multiple tables in SQL has been asked before and the common answer seems to be do them separately in a transaction.

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

e.g.

Table1.a is a foreign key to Table2.a

One of the entries in the tables is wrong, e.g. both columns are 'xxx' and should be 'yyy'

How do I update Table1.a and Table2.a to be 'yyy'?

I know I could temp remove the key and replace but surely there's another way.

Thanks

Answer 1

You can't do the update simultaneously, however you can force SQL to do the update. You need to make sure your foreign keys have the referential triggered action ON UPDATE CASCADE

e.g.

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

Answer 2

my answer is based on the following link: http://msdn.microsoft.com/en-us/library/ms174123%28v=SQL.90%29.aspx

You need to make sure that your table_constraint will be defined as 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)

and then when you will change the value of your primary key

see the following quote:

For ON DELETE or ON UPDATE, if the CASCADE option is specified, the row is updated in the referencing table if the corresponding referenced row is updated in the parent table. If NO ACTION is specified, SQL Server Compact Edition returns an error, and the update action on the referenced row in the parent table is rolled back.

For example, you might have two tables, A and B, in a database. Table A has a referential relationship with table B: the A.ItemID foreign key references the B.ItemID primary key.

If an UPDATE statement is executed on a row in table B and an ON UPDATE CASCADE action is specified for A.ItemID, SQL Server Compact Edition checks for one or more dependent rows in table A. If any exist, the dependent rows in table A are updated, as is the row referenced in table B.

Alternatively, if NO ACTION is specified, SQL Server Compact Edition returns an error and rolls back the update action on the referenced row in table B when there is at least one row in table A that references it.