CODEWITHSUNDEEP
mysqldatabase-design
The Coder
The Coder

Reputation: 4027

Is it a good practice to use surrogate primary key even if the table contains only one column and that itself contains the unique values?

I am having a gender table as: gender_id | gender_label
Will it be a bad practice if I remove the gender_id column, as gender_label is also unique and can be used as a primary key?
I know surrogate keys should be used wherever possible as they are efficient and fast to search. But will it make any difference for above scenario? How?

Upvotes: 1

Views: 777

Answers (1)

Rick James
Rick James

Reputation: 142296

I have written hundreds of tables. 2/3 of them have a "natural" key; 1/3 have a "surrogate key".

From that, I deduce that the answer to your question is "it depends".

OK, so what does it depend on?

  • Is there a reliable natural key -- one that is truly UNIQUE? If so, probably you should use it.
  • Is performance an issue -- Well, this goes both ways.
  • Is the natural key "small"? A 2-character country_code taken from an international standard is, in my opinion, better than a 4-byte INT.
  • If the natural key is "big" and there are multiple secondary keys, then keep in mind that the PK is included in every secondary key. (In InnoDB)
  • A lookup by a secondary key involves (in InnoDB) first a drill-down in the secondary key's BTree, then a drill-down in the PK's BTree. So, there is a slight-to-large performance benefit in having a PK that you use a lot. (That might be surrogate or it might be natural.)

In your simple case, is gender_id strings like M/F/etc? That's what I would use. Actually, I might not have the table at all; instead have gender in other tables as ENUM('unknown', 'male', 'female', 'other')

Upvotes: 1

Related Questions