Constraint that value in foreign row matches another value?

Question

I'm trying to accomplish the following:

// table_a
{
    "id": "aaa",
    "name": "abc"
}
// table_b
{
    "id": "bbb",
    "firstName": "abc"
}
//table_c
{
    "table_a_id": "aaa",
    "table_b_id": "bbb"
}

// constraint - table_a name matches table_b firstName

Answer 1

This is a foreign key constraint. Or a constraint that can be implemented using triggers.

As a foreign key constraint, you need to store the name in c. In pseudo-code, this looks like:

create table a (
    id primary key,
    name,
    unique (name, id)
);

create table b (
    id primary key,
    firstname,
    unique (namename, id)
);

create table c (
    table_a_id references a(id),
    table_b_id references b(id),
    name,
    foreign key c_a_name_id (name, id) references (name, id),
    foreign key c_b_name_id (name, id) references (firstname, id),
);

The names are necessarily the same because the name is stored in c only once.