Using a file path as primary key in MySQL

Question

I have table which will only contain information about files, so, as the file path is unique and can identify any entry, i thought i should use it as primary key.

Although, i'm having a few problems like needing to specify a key length (error 1170). What should i do? Use an integer as primary key and every time i need to access information on file 'x', do a "where FilePath=x"?

Thanks for the time dispensed reading my question.

Answer 1

In most databases, it's inefficient (or even impossible) to create an index for a string column that is long enough to store a deep file path. This is good situation to use a surrogate key.

Another option would be to compute a fixed-length hash from the file path using an inexpensive hash function such as MD5() (though MD5 is not strong enough to use for passwords, it's strong enough to ensure uniqueness given the input data in this case).