What is the best way to get a value from a reference table to update a foreign key relationship

I have a mapping table that maps an int to a string for example

I have another table that references this one with a foreign key relationship

create table products (
    product_name varchar (50),
    color_id int,
    constraint fk_products foreign key (color_id) references colors (color_id)
);

My question is the best way to get the color_id from the colors table when I am updating the products table – for example if I get a new product that is “red” and I want to enter that product into the product table with the color_id of 1.

The two ideas I have thought of are to

do a select from the colors table to get the color_id for the color “red”. But I don’t like the idea of needing to run a select every time I update the products table
hardcode the values in my code for the colors, but that seems very annoying from a maintenance perspective, because I would need to update the code every time a new color is added.

I am using postgres

Answer

You should look up the values. This is pretty easy in an insert. Something like:

insert into products p
    select v.name, c.color_id
    from (values ('name1', 'red'), ('name2, 'blue')) v(name, color) join
         colors c
         on c.color = v.color;

If you are concerned about performance, just be sure you have an index on colors(color).

Advertisement

Answer