If you use a surrogate key, you still need a unique constraint in the table (probably the same columns you would otherwise call your natural PK). If your unique constraint isn't sufficient to capture the difference you mention, you need to add more columns.
However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.
> However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.
Foreign keys referencing surrogate key has different semantics than fk referencing natural key - it is a can of worms actually and can lead to unexpected anomalies.
Lets take the example from the article (with surrogate key):
We have a procedure to register visits to a restaurant:
register_visit(restaurant_name, user_name, date_of_visit) {
INSERT INTO visit SELECT id, user_name, date_of_visit FROM restaurant WHERE name = restaurant_name
}
I very much enjoy spending time in "Polish Kielbasa" restaurant in Warsaw and I visit it everyday - I don't visit any other restaurant at all.
Now changes of a restaurant name will lead to the database containing misinformation:
register_visit('Polish Kielbasa', 'mkleczek', 2024-6-4);
update restaurant set name = 'Old Kielbasa' where name = 'Polish Kielbasa' and city = 'Warsaw';
insert into restaurant ('Polish Kielbasa', 'Warsaw');
register_visit('Polish Kielbasa', 'mkleczek', 2024-6-4);
Question: what restaurants did I visit this year?
This kind of anomalies are avoided using _natural_ keys and - first of all - defining proper _predicate_ for _each_ relation.
The predicate of relation visit(restaurant_name, city, user, date) is quite obvious: "User [user] visited restaurant [restaurant_name] in [city] on [date]"
Question: What is the predicate of relation visit(restaurant_id, user, date)?
However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.