Database design advice

I have a working SQLite database that holds information about video files. The current design is as pictured below. However, the boss has decided to make some changes.

The FileProperties table currently uses the file name as the primary key. However, the PK now must be a compound key of both fileName and (file) location, which makes more sense anyway.

If this is done, what would be the best way to reference this compound key as a foreign key in the other tables? I was thinking of either creating a separate table that holds an auto-incrementing primary key, fileName and location. Then the PK can be used as a foreign key reference with all the other tables.

Or, make fileName and location a composite key in the current FileProperties table and add a new field that can be used as a reference and this fie开发者_StackOverflow中文版ld must be auto-incrementing and unique in the table.

I haven't had much practical experience with designing databases so any advice with my problem or my current design would be very welcome.

---

Absolutely use an auto-incrementing primary key. To ensure data integrity, create a unique index across the (filename,location) columns.

The following wiki article talks briefly about the pros and cons of a natural key. A natural key is a key taken directly from the data. In your case, that would be the composite key of (filename,location). In short, a natural key reduces physical space required by the data, at the cost of propagating changes to the key across all relations.

I (nearly) always have an auto-incrementing id on a table, even if there is a natural key available to be used.

---

Add auto-incremented FileId primary key.
Add unique constraint for Location + FileName.
Avoid using compound primary keys.