CODEWITHSUNDEEP
sqlsqlite
Yiyuan Lee
Yiyuan Lee

Reputation: 679

Referencing a specific column in foreign row

TableA:
ID     NAME

0      Thomas
1      Johnson
2      Harry

TableB:

ID     IDA     ANAME
0      2       Harry
1      0       Thomas
2      1       Johnson

I've created a foreign key in "TableB" on column "IDA" which referenced "ID" in "TableA", with the following:

FOREIGN KEY (IDA) REFERENCES TableA(ID)

For a given row in "TableB", I'm able to get the corresponding row in "TableA" as defined by the above constraint. How then do I make sure that the "ANAME" of a particular row in "TableB" takes on the value of "NAME" in this corresponding row in "TableA"? I've tried the following, but unfortunately it turns out to be invalid (Missing Logic near "ANAME" error)

FOREIGN KEY (IDA) REFERENCES TableA(ID) ON UPDATE SET ANAME = TableA.Name

Upvotes: 0

Views: 28

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

Short answer - duplicating data across tables is almost never a good idea, so just don't do that.

Use your foreign key on the ID column to fetch your names from TableA whenever you need them, so instead of 

  SELECT id, ida, aname 
  FROM TableB;

use

  SELECT b.id, b.ida, a.name AS aname
  FROM TableB b
  JOIN TableA ON b.ida = a.id;

Upvotes: 3

Related Questions