How can I embed calculations on foreign keys in MySQL?

Question

I am creating two tables in MariaDB, one of which has a column that is a calculation of a foreign key. The simplified version of the script follows this format:

CREATE TABLE a {
pk     INT     PRIMARY KEY,
value  INT
}
CREATE TABLE b {
pk     INT     PRIMARY KEY,
value  INT,
calc   INT,
CONSTRAINT fk
  FOREIGN KEY (value)
  REFERENCES a (value)
}

It turns out that 'calc' is always equal to 'value'+1. When querying 'b', b.value is never referred to, but b.calc is frequently. Should I remove 'calc' to keep third normal form, or is there a way to remove b.calc's dependence on b.value? Something like:

CREATE TABLE b {
pk     INT     PRIMARY KEY,
calc   INT,
CONSTRAINT fk
  FOREIGN KEY (calc)
  REFERENCES a (value+1)
}

Thank you for your help.

Answer 1

A foreign key has to have the actual value of the referenced column.

You can use a virtual (AKA generated) column to make a computed column.

CREATE TABLE b (
    pk INT PRIMARY KEY,
    value INT,
    calc GENERATED ALWAYS AS value + 1,
    FOREIGN KEY (value) REFERENCES a (value)
);