Computed Columns using Foreign Key

Question

I was wondering if it was possible to populate a computed/generated column using the foreign key to get information from the linked cell/table

I have a relationship set up between TableA and TableB.

TableA is where I enter all of the job information and TableB gives the name of all workers, so when a job is entered,the worker name is added to the app from TableB via the foreign key.

the problem is though, via the app, when I search via the workers name, it does not give any results, instead I need to search via the Foreign Key number which is the ID from TableB

So TableA looks like

Name	fk_TableA_TableB
Job 1	2
Job 2	1

TableB

id	Name
1	Joe
2	John

The Result I would linke to acheive is an extra row at the end of TableA which gives the value of TableB based on the foreign key

Name	fk_TableA_TableB	TableB
Job 1	2	John
Job 2	1	Joe

I am looking for an expression that will allow me to acheive this using a generated/computed column

I have tried using an SQL Expression in HeidiSQL

SELECT 'Name' from TableB where id = fk_TableA_TableB

But no joy

Everything I see on this subject talks about using joins However, the tables are constantly updated and I need a solution that will automatically pull the name from TableB when a new entry is input in TableA

Answer 1

The direct answer to your question is no, a generated column can't pull data from another table.

A generated column's expression can reference only other columns in the same table, or other generated columns defined earlier in the same table. A generated column cannot reference columns from other table, nor can it reference a subquery or a stored function. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

A workaround could be to design a trigger that runs before insert or update on tableA, to copy the respective value from tableB and stores it in a normal column in tableA.

Something like the following, but I haven't tested it:

CREATE TRIGGER myAwesomeTrigger BEFORE INSERT ON tableA
FOR EACH ROW BEGIN
  SET NEW.name = (SELECT name FROM tableB WHERE id = NEW.fk_TableA_TableB);
END

CREATE TRIGGER myAwesomeTrigger2 UPDATE INSERT ON tableA
FOR EACH ROW BEGIN
  IF NEW.fk_TableA_TableB <> OLD.fk_TableA_TableB THEN
    SET NEW.name = (SELECT name FROM tableB WHERE id = NEW.fk_TableA_TableB);
  END IF;
END

But if names are constantly changing in tableB, you would also need to "push" such changes:

CREATE TRIGGER myAwesomeTrigger3 AFTER UPDATE ON tableB
FOR EACH ROW BEGIN
  IF NEW.name <> OLD.name THEN
    UPDATE tableA
    SET name = NEW.name
    WHERE fk_TableA_TableB = NEW.id;
  END IF;
END

You'd also have to decide what you want to do in tableA if the referenced row in tableB is deleted.

---

Re your comment:

You can't use JavaScript in SQL expressions.

But you could simply update either table using queries from your JavaScript app. This is totally up to you to keep them in sync.

That's true of any kind of denormalization: You can store data redundantly, but then you take on the responsibility for maintaining data integrity.

The point of data normalization is so you don't have to do that. By storing data once, you eliminate the risk of data getting out of sync.