Auto Update Foreign Key in SQL

Question

I'm relatively new to relational databases and I'm not sure if what I want to do is possible. If someone could please tell me how to do this or at least point me in the right direction,

I have this table_1

NameId       SelectedName 
1                 A
3                 C
6                 F

NameId is a FOREIGN KEY that REFERENCES table_2 (Id)

and I have this table_2

Id              Name 
1                A
2                B
3                C
4                D
5                E
6                F

And I want to set up my database so that anytime an entry in table_2 is updated, table_1 will change the value of it's Name column if the entry from table_2 is in it. For example if I change this value in table_2

Id              Name 
3             C_EDITED

That change will also be reflected in table_1

Which rdbms are you using? You have tagged multiple and the answer could be different for each. Most likely you need `ON UPDATE CASCADE` set as a column property though. See this post: https://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade/ — Jacob H, Feb 22 '18 at 16:35
I guess what Jacob means is `CREATE TEMP TABLE table_1 (id INT REFERENCES table_2 ON UPDATE CASCADE, name TEXT); ` (postgres). This will however only update the ID, not the name. Can you maybe elaborate more on why you need it? I suspect there are other ways to depict what you need by means of modelling the tables a bit differently. — Jim Jones, Feb 22 '18 at 17:07
@JacobH sorry I deleted the postgres tag as I am using mysql — Naji, Feb 22 '18 at 19:22
@JimJones I need it because the program i'm working on has a multitude of tables that are related to each other and this is just a simplified example of the one i'm working on. basically `table_2` holds a list of all of our information and `table_1` is holding which bits of `table_2` we are using at this specific point in production — Naji, Feb 22 '18 at 19:24
Um. Generally speaking you shouldn't be updating keys used as FK references. To help avoid this for some data models, often a _surrogate key_ is used (which is often numeric). It sounds like what you really want is a new table that has `name` in it. In lieu of that, you _might_ be able to do something with an `INSTEAD OF` trigger — Clockwork-Muse, Feb 22 '18 at 22:12

score 1 · Answer 1 · answered Feb 22 '18 at 21:16

You probably don't want to do it this way ...

The tables don't look normalized, so you could have issues with data redundancy and integrity if you are just repeating the values of table_2 in table_1. So, table_1 would probably be better without the SelectedName column since you can always look up the value of Name from table_2 whenever you need it e.g.

SELECT t1.NameId, t2.Name
FROM table_1 t1 INNER JOIN table_2 t2 ON (t1.NameId = t2.Id);

However, if we assume that Id and Name are necessary as your primary key (i.e. that you have records such as (1,'A'),(1,'B'),(1,'Anything') in table_2 then the PK would be defined on both columns, as would the FK that references that table. This SQLfiddle shows how it could work but it does require that you change the PK on table_2 and the FK on table_1 and use the ON UPDATE CASCADE method mentioned in some of the other comments.

http://sqlfiddle.com/#!9/80daee/1

score 1 · Answer 2 · answered Feb 22 '18 at 22:27

create table table_2(
    id int,
    name varchar unique,
    primary key (id)
);

create table table_1(
    nameid int ,
    selectedname varchar foreign key references table_2(name) on update cascade,
);

insert into table_2 values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'),
(6,'F')

insert into table_1 values
(1,'A'),
(3,'C'),
(6,'F')

select * from table_1;
select * from table_2;

update table_2 set name = 'X' where id = 1;

select * from table_2;
--Note X

score 0 · Answer 3 · answered Feb 22 '18 at 17:17

0

i don't understand why are you keeping selected column in table_1.But Still

update table_1 inner join table_2
    on tabel_2.id=table_1.nameid
    set selectedname =table_2.name

answered Feb 22 '18 at 17:17

Kedar Limaye

1,041
8
15

Auto Update Foreign Key in SQL

3 Answers3