- I realize the need for primary secrets.
- I realize the need for indexes.
Should every MySQL table come with an auto-incremented primary key (ideally with INT area type)?
@Raj More's answer appears most effective. The problem after i consider it, however, is when this auto-incremented primary key ID will connect with other tables. For instance:
ID | firstname | lastname | email ---------------------------------------- 1 | john | doe | email@example.com 2 | sarah | stow | firstname.lastname@example.org 3 | mike | bro | email@example.com
ID | memberid | display | address -------------------------------------------- 1 | 1 | funtime zone | 123 street 2 | 3 | silly place llc | 944 villa dr
Within the example above, someone will come towards the site and select to join up for any free product/service. When the consumer selects, they could give more information (saved in table 2) for further mailing, etc. The issue I see is within how these tables connect with the 'primary key auto-incremented field'. In table 2, the 'memberid' pertains to table 1's ID but this isn't 'extremely' obvious. Any new information place into table 2 will increment by 1 whereas not all customers will decide to have fun playing the table 2 needed data.
I'm not an enormous fan of surrogate secrets. I haven't yet visit a scenario where I would rather play one for each table of the database. I'd say No. Educate yourself about this answer: surrogate-vs-natural-business-keys
The above mentioned might be viewed as sarcastic or flaming (regardless of the remarkably many upvotes) therefore it is erased.
Within the general situation, there has been many questions and solutions on surrogate and natural secrets and so i felt this is a lot more like a replica. My view is the fact that surrogate secrets are fine and incredibly helpful, due to the fact natural secrets can result in very large primary secrets within the low finish of the chain of connected tables - which is not handled well by many people RDBMS, clustered indexes get large, etc. But stating that "every MySQL table must have a car-incremented primary key" is an extremely absolute statement and i believe you will find times when they offer little or free.
Because the OP up-to-date the question, I'll attempt to discuss that subject.
I believe this really is a situation where an autoincrementing primary key isn't just useless but adds negative value. Assuming that
table2 have been in
1:1 relationship, the
memberid could be both
Primary Key along with a
Foreign Key to
Adding an autoincrementing id column adds one index and when it is a clustered one (like InnoDB PK indexes) increases how big the
memberid index. Much more, for those who have this kind of auto-incrementing id, some JOIN of table2 with other tables must be done by using this
id (the JOINs to tables in
1:n regards to table2) plus some using
memberid (the JOINs to tables in
1:n regards to
table1). Should you have only
memberid both these kinds of JOINs could be
I'm a huge fan of surrogate secrets. I haven't yet visit a scenario where I'd prefer not play one.
I'd say Yes.
Educate yourself about this answer Surrogate vs. natural/business keys
Yes, with one exception:
A table which implements a n:m relationship between two other tables, a pure link table, doesn't need this type of area whether it has two fields only, referencing the 2 primary secrets from the linked tables. The main key from the link table then consists of these two fields.
The moment the hyperlink table has extra information, it requires an easy single-area primary key.
With that said, there might be more exceptions database design is an extremely broad area...
EDIT: Added more in the earlier sentence.