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

Use an int as a primary key and put Unique in the field for the file path.

CREATE TABLE `cms`.`test` (
    `id` INT( 32 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `filepath` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
    `filepath`
)
) ENGINE = MYISAM

Answer 2

Can you specify a maxlength for a filepath and change the column to a varchar?

This article explains the error. Basically, you can't put a key on a text or blob column because a key needs a length of which to check uniqueness against. But text and blob columns don't support lengths. So you can't use they in a key.

Varchar columns can go up to 65K characters in later versions of MySQL

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

• http://dev.mysql.com/doc/refman/5.0/en/char.html

Answer 3

Don't use a natural key (file path/name) use a surrogate key (int)

I would make the primary key an INT, and put any path & file into in a different column. It would be slow to have a long string as a key, and/or as a foreign key in other tables.

You might want to split the file name from the path, and/or only store part of the path. The main root of the path could be a config setting that you append onto the database partial path+file name.

When we store file info, we just store the name, the path is build out based on other data: related item (order, incident, job, etc) and then the ID of that other data, etc...

Answer 4

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).