> On 4 Feb 2025, at 15:27, Rich Shepard <rshepard@xxxxxxxxxxxxxxx> wrote: > > Should lookup tables have a numeric FK column as well as the description column? > > If so, how should I add an FK to the two lookup tables in my database? I’ve read the whole thread and the reasoning for having (numeric) autogenerated surrogate key is: a) performance b) no cascading updates I would like to add another dimension to this discussion: logical consistency. Imagine the following simplified schema with surrogate PK: create table restaurant ( restaurant_id int not null primary key generated always as identity, name text not null unique ); create table restaurant_visit ( visit_id int not null primary key generated always as identity, guest_username text not null, when date not null, restaurant_id not null foreign key restaurant(restaurant_id), rating smallint not null ); Let’s say users on your website register restaurant visits and rate them. The query to register a visit is: insert into restaurant_visit select $user, current_date, restaurant_id, $rating from restaurant where name = $restaurant_name It is now completely unclear what it means to change the name of the restaurant for already registered visits. Is it still the same restaurant with a different name or a different restaurant? Or let say someone swaps names of two restaurants. That means a user that goes to the same restaurant every day would register visits to two different restaurants! Using the name of a restaurant as primary key gets rid of these logical anomalies because the database model now reflects facts from reality. Having surrogate keys makes your relational database more like a network/object oriented database where rows don’t represent facts but rather some entities that have identity independent from their attributes. Thanks, Michal
