技术咨询：含姓名、手机号等字段的数据表应选择什么作为primary key？

Let’s break this down step by step—none of your existing fields are ideal for a primary key, and here’s why, plus what you should use instead:

Why your current fields don’t work: #

• First/Last Name: Way too prone to duplicates. Even unique-sounding names can overlap, and people might change their names (marriage, legal name changes) later—primary keys should never be modified once set.
• Mobile Number: While it seems unique at first glance, there are big issues here:
  • Users can change their phone numbers, which would force you to update the primary key (a huge no-no, as it ripples through any related tables).
  • Phone numbers can be recycled by carriers over time.
  • You might encounter users who don’t have a mobile number (edge case, but it breaks the "non-null" requirement for primary keys).
• Graduation Date: Hundreds (or thousands) of people could share the same graduation date—completely non-unique.
• Entry Date: Same problem as graduation date; multiple users could join on the exact same day, so it can’t guarantee uniqueness.

The Best Solution: Use a Surrogate (Proxy) Primary Key #

Instead of tying your primary key to business data, create a dedicated, non-business-related field for this purpose. The two most common options are:

• Auto-incrementing Integer: Something like user_id INT AUTO_INCREMENT (in MySQL) or user_id SERIAL (in PostgreSQL). It’s lightweight, fast to query, and easy to manage.
• UUID/GUID: A 128-bit unique identifier (e.g., user_id UUID DEFAULT gen_random_uuid() in PostgreSQL). Perfect if you’re working across distributed systems or need to generate IDs outside the database without worrying about collisions.

Why this works: #

• It’s completely independent of your business logic—changes to names, phone numbers, or dates won’t affect the primary key.
• Guarantees uniqueness and non-nullability (as long as you set the field to be required).
• Makes joining tables (if you add related tables later) simpler and more efficient.

If You Must Use a Business Key (Not Recommended) #

If for some reason you have to use an existing field, the only semi-viable option is mobile number—but you’ll need to enforce strict rules:

• Add a unique constraint to ensure no duplicates.
• Make the field non-nullable.
• Be prepared to handle edge cases (like users needing to update their number, which would require cascading changes to any related records—this gets messy fast).

But honestly, save yourself the headache and go with a surrogate key. It’s the industry standard for a reason!

内容的提问来源于stack exchange，提问作者Chandana Ramesh