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

• Vérace: Have you tried a VIEW?
• PaulMcF87: @Vérace-СлаваУкраїні A view wont work becasue the software I have used to create the app requires all referenced tables to contain a primary key. With views not having a primary key, I would not be able to reference anything from it.

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.

• PaulMcF87: Thanks Bill, I will need to research this before trying it as Id be sacred incase I caused an issue with the data that is already int he table. Is there no way to use a javascript command within the expression to chieve the desired result?
• PaulMcF87: I meant to add, names are not changing, only new data being added to the table, also, no data will be deleted as redundant entries might be needed further down the line, hopefully that means that the second query would not be necessary and I can use the 1st one as a building block if JS is not possible
• ViaTech: Off topic given tags, but I looked this up given Django 5's GeneratedField not being able to handle Foreign Key calculations, if someone reading this needs this in Django, you would currently use post_save() typically to calculate those values. It is not a DB trigger, rather python code to execute after a save, so likely less performant, but it offers the same functionality
• Bill Karwin: @ViaTech, In addition to the performance penalty, it is also susceptible to creating data inconsistency. In the rare event that the Python code exits during the post_save(), or fails to update the data for whatever reason, it would leave your data in an inconsistent state. Whereas database-enforced constraints are guaranteed to never do that.
• ViaTech: @BillKarwin yes +1 (I was just posting generals), this is possible during full deployments (split seconds of downtime, as I've experienced it), in that case you'd need to implement a [framework-dependent] check to ensure validity. Again, using Django, I have for this instance defined checks using celery tasks to ensure data is consistent...haha feature request to all DBs please allow FK related Generated/Computed Columns by default