MySQL: keep always the same value in two fields of different tables

Question

Is there any way to keep always the same value in two fields of different tables?

Answer 1

You could use triggers so that if one of the fields is change, the other is synchronized to match.

Answer 2

It's usually best not to store a value twice. Instead you can store the value in just one of the tables and when you query you can join the two tables together on a foreign key so that you have access to values from both tables at the same time:

SELECT table1.foo, table2.bar
FROM table1
JOIN table2 ON table1.table2_id = table2.id

If you store the value twice it is called denormalization. This can lead to problems if the values ever get out-of-sync for one reason or another. Sometimes it is advantageous to denormalize to improve performance, but a single join is very fast so unless you have measured the performance and found it to be too slow, I'd advise against doing this.

Answer 3

INSERT INTO TABLE A (FieldInA) VALUES ('X')
INSERT INTO TABLE B (FieldInB) VALUES ('X')

Then simply never delete, nor update, these table rows, and voilà, you have always the same value in two fields of different tables.

Answer 4

Any reason why you couldn't normalize the design of your database so that you don't have the same data twice and don't have to worry about stuff like this anymore?

In case you can't change the design take a look at triggers

Answer 5

Why would you ever want to do this?

If one attribute of one entity is always the same as some attribute of another related entity, then you have a redundant data model.

Instead of trying to synchronize the attributes, refer to one attribute. Use a join to join the first table to the second, then get the value of the attribute from one table. E.g, if you currently have this:

 TableA.foo should always equal TableB.bar

drop column TableA.foo, and do this:

 select A.*, B.bar as foo
 from TableA A 
 join TableB B on (B.foreign_key = A.key);