database design - Should I have a dedicated primary key field?

Question

I'm designing a small SQL database to be used by a web application.

Let's say a particular table has a Name field for which no two rows will be allowed to have the same value. However, users will be able to change the Name field at any time.

The primary key from this table will be used as a foreign key in other tables. So if the Name field was used as the primary key, any changes would need to be propagated to those other tables. On the other hand, the uniqueness requirement would be handled automatically.

My instinct would be to add an integer field to act as the primary key, which could be automatically populated by the database. Is there any point in having this field or would it be a waste of time?

Answer 1

I would use a generated PK myself, just for the reasons you mentioned. Also, indexing and comparing by integer is faster than comparing by strings. You can put a unique index on the name field too without making it a primary key.