how to update the same column in more than one table

Question

i want the statment of how to update the same column in more than one table in the same sql database but these table are related to each other through that column who is primary key in one and forgin key in the other tables with C# please help me

Answer 1

Changing a primary key is never fun: that's why many people prefer synthetic to natural keys. There is no single-statement standard SQL way of doing it; instead, you have to INSERT a copy of the master table row with the new primary key, UPDATE the child table(s) to refer to the new row, and DELETE the original row.

Answer 2

What you want can be specified on the foreign key constraint. Check here e.g. the MySQL syntax.

Something like this should work for your sql-dialect too.

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
  REFERENCES tbl_name (index_col_name, ...) ON UPDATE CASCADE

CREATE TABLE Parent(
  PID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  Name VARCHAR(20),
  PRIMARY KEY (PID)
);

CREATE TABLE Child(
  CID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
  PID SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY (PID) REFERENCES Parent (PID)
    ON UPDATE CASCADE
);

INSERT INTO PARENT (Name) VALUES ('Joe');
INSERT INTO PARENT (Name) VALUES ('Max');
INSERT INTO Child (CID, PID) VALUES (1, 1);
INSERT INTO Child (CID, PID) VALUES (2, 2);

SELECT * FROM Parent;
SELECT * FROM Child;

Parent             Child
+------+------+    +------+------+
| PID  | Name |    | CID  | PID  |
+------+------+    +------+------+
| 1    | Joe  |    | 1    | 1    |
+------+------+    +------+------+
| 2    | Max  |    | 2    | 2    |
+------+------+    +------+------+

UPDATE Parent SET PID='5000' WHERE PID='1';
SELECT * FROM Parent;
SELECT * FROM Child;

Parent             Child
+------+------+    +------+------+
| PID  | Name |    | CID  | PID  |
+------+------+    +------+------+
| 5000 | Joe  |    | 1    | 5000 |
+------+------+    +------+------+
| 2    | Max  |    | 2    | 2    |
+------+------+    +------+------+

In this sample there might be a problem depending on your DB-System. If the DB system doesn't check if a PID is already assigned when in "AUTOINC Mode" at somepoint the autoincremnt value may reach 5000 and the insert could fail as there is already a row with this PID. Depends on how your DB-System handels changing of the primary key column when there is auto increment specified

Answer 3

What you want to answer your question is a foreign key with a cascading update, as mentioned in the answer from jitter. However, like most design considerations, you wouldn't have to ask this question if you went back and analyzed your underlying need to perform this kind of operation.

Although this works, it means that the primary key on your database entities is not reliable over time, and it also means that the database might have to do a tremendous amount of work doing the cascade (and it will have to maintain more locks in performing such an update, which increases the likelihood of blocking).

With regard to the reliability over time, say you have a warehouse which maintains historical balances on an account: AcctPK, EffectiveDate, Balance, now you potentially have a large number of rows to cascade the update to if you add this as yet another cascading FK relationship in your DB. If the data warehouse is in a separate database, you cannot enforce referential integrity, so no cascade occurs, and now you no longer have continuity of the account from before AcctPK was changed.

Suppose you export the data to a vendor who supplies a service of some kind of information for each row. Now, when the vendor returns the results, you cannot match some of the rows, because the PK you sent no longer exists in your data.

In both these cases, you can work around this issue by keeping change records of the PK changes over time, but ultimately, it comes down to working around a (probably) poor choice of PK.

I strongly suggest you reconsider your choice of primary key if it's going to frequently be changing. One alternative would be to use a surrogate IDENTITY or autonumber as the PK (with the FK relationship based on that instead). It will never need a cascading update, and the other field you are currently using as a "natural" primary key can be changed in a normal UPDATE without having to do a cascade.