Menu
Each entry in my 'projects' table has a unique 32 characters Hash identifier stored using a varchar(32).
Would that be considered a bad practice to use this as the primary key ? Is there a recommended size, datatype for Primary keys ?
758
The primary key should be short and consist of one column whenever possible. Data type. The data type of a primary key should be a numeric, an integer or a short, fixed-width character.
Integer (number) data types are the best choice for primary key, followed by fixed-length character data types. SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
A table can have only one primary key, and a primary key field cannot contain a null value. By default, primary keys are a requirement in all MySQL database tables. This ensures reliable data replication. However, you can turn off a database's primary key requirement by making a configuration request via our API.
I would say yes, it's a bad idea to use such a large column for a primary key. The reason is that every index you create on that table will have that 32 character column in it, which will bloat the size of all the indexes. Bigger indexes mean more disk space, memory and I/O.
Better to use an auto-increment integer key if possible, and simply create a unique index on your hash identifier column.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
