0

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

2
  • Have you tried a VIEW? Jun 15 at 19:40
  • @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.
    – PaulMcF87
    Jun 15 at 19:42

1 Answer 1

0

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.

2
  • 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
    Jun 15 at 20:50
  • 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
    – PaulMcF87
    Jun 15 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.