Create a field in Firebird which displays data from another table

Question

I didn't find a working solution for creating a "lookup column" in a Firebird database.

Here is an example:

Table1: Orders [OrderID] [CustomerID] [CustomerName]

Table2: Customers [ID] [Name]

When I run SELECT * FROM ORDERS I want to get OrderID, CustomerID and CustomerName....but CustomerName should automatically be computed by looking for the "CustomerID" in the "ID" column of "Customer" Table, returning the content of the "Name" column.

is there no way to do this by creating a table, so that there is no need to join tables later in every sql command? sorry for this stupid question, but i'm an absolute sql beginner — Chris
You would create a view which can be used like a table for selects statements. — Reallyethical

Mark Rotteveel Mark Rotteveel · Accepted Answer · 2017-11-04T07:57:04

Firebird has calculated fields (generated always as/computed by), and these allow selecting from other tables (contrary to an earlier version of this answer, which stated that Firebird doesn't support this).

However, I suggest you use a view instead, as I think it performs better (haven't verified this, so I suggest you test this if performance is important).

Use a view

The common way would be to define a base table and an accompanying view that gathers the necessary data at query time. Instead of using the base table, people would query from the view.

create view order_with_customer
as
select orders.id, orders.customer_id, customer.name
from orders
inner join customer on customer.id = orders.customer_id;

Or you could just skip the view and use above join in your own queries.

Alternative: calculated fields

I label this as an alternative and not the main solution, as I think using a view would be the preferable solution.

To use calculated fields, you can use the following syntax (note the double parentheses around the query):

create table orders (
    id integer generated by default as identity primary key,
    customer_id integer not null references customer(id),
    customer_name generated always as ((select name from customer where id = customer_id))
)

Updates to the customer table will be automatically reflected in the orders table.

As far as I'm aware, the performance of this option is less than when using a join (as used in the view example), but you might want to test that for yourself.

FB3+ with function

With Firebird 3, you can also create calculated fields using a trigger, this makes the expression itself shorter.

To do this, create a function that selects from the customer table:

create function lookup_customer_name(customer_id integer) 
    returns varchar(50)
as
begin
    return (select name from customer where id = :customer_id);
end

And then create the table as:

create table orders (
    id integer generated by default as identity primary key,
    customer_id integer not null references customer(id),
    customer_name generated always as (lookup_customer_name(customer_id))
);

Updates to the customer table will be automatically reflected in the orders table. This solution can be relatively slow when selecting a lot of records, as the function will be executed for each row individually, which is a lot less efficient than performing a join.

Alternative: use a trigger

However if you want to update the table at insert (or update) time with information from another table, you could use a trigger.

I'll be using Firebird 3 for my answer, but it should translate - with some minor differences - to earlier versions as well.

So assuming a table customer:

create table customer (
    id integer generated by default as identity primary key,
    name varchar(50) not null
);

with sample data:

insert into customer(name) values ('name1');
insert into customer(name) values ('name2');

And a table orders:

create table orders (
    id integer generated by default as identity primary key,
    customer_id integer not null references customer(id),
    customer_name varchar(50) not null
)

You then define a trigger:

create trigger orders_bi_bu 
    active before insert or update
    on orders
as
begin
    new.customer_name = (select name from customer where id = new.customer_id);
end

Now when we use:

insert into orders(customer_id) values (1);

the result is:

id  customer_id  customer_name
1   1            name1

Update:

update orders set customer_id = 2 where id = 1;

Result:

id  customer_id  customer_name
1   2            name2

The downside of a trigger is that updating the name in the customer table will not automatically be reflected in the orders table. You would need to keep track of these dependencies yourself, and create an after update trigger on customer that updates the dependent records, which can lead to update/lock conflicts.