MySQL relational tables and Primary Keys data types

Question

I'm working on building a MySQL database that will have many related tables. I know I can create a primary key that is unique and auto increments, then reference that Primary Key as a Foreign Key in the related table. My question is what are best practices for Primary/Foreign key data types (int, varchar, etc..). I want to make sure I get this right the first time.

Thank you

Answer 1

In MySQL most commonly used datatype is INT UNSIGNED. It's 4 bytes long and can store numbers up to 4 billion which is more than enough for majority of applications. Using BIGINT (8B - 18446744073709551615 range) is usually an overkill when just starting with your application. Before your database gets too large for INT primary key, you'll need to hire a professional DBA anyway to help you with other issues.

CHAR or BINARY datatypes can be used, when GUID type Primary key is used - such keys make it easier to shard data across multiple database instances, however with MySQL a multi master replication is more often seen.

When using InnoDB tables it's good to remember, that PK will be implicitly the last column in all other indexes created on specific table. This makes it important to use reasonably short datatype for PK (again 4B INT usually does its job well).