Surrogate database key and data duplication

Question

I was thinking about this problem. In database design most of the times surrogate keys are used, but how to prevent data duplication and inconsistent data? I mean one could have a customer table made of customer_id, name, surname. What would prevent me of inserting the same customer twice with a different customer_id? Sure I could add a unique index to name and surname, but if one does so than what's the purpose of the surrogate primary key?

Answer 1

You're asking a business question, not a technical one.

"How do I know whether two people with the same name are the same person or not?"

Well typically customers are not identified by a name alone, there is also one of:

1. An account number
2. An email address
3. A postal address
4. A credit card number
5. A passport number
6. A date of birth

... etc.

The name is simply not a uniquely identifying characteristic, it's just an attribute of a customer that is probably non-unique, so you need something else to help identify them. Within the database this is the primary key of the customer table, but for business purposes it could be any number of attributes.