MySQL – Importance of Primary and Foreign Keys When Truncating Tables Daily

foreign keyMySQL

I'm fairly inexperienced at database design, so I am trying to plan my database beforehand as much as possible. This database is to hold information about a VMware environment.

The database has 9 tables currently. Currently the idea is to truncate most of the tables daily and then use LOAD DATA LOCAL INFILE to populate the tables from CSVs scraped from each VMware vCenter. I'm using auto-increment columns for primary keys, though I could use composite keys consisting of the object's ID and the vCenter name (the object's ID is only unique per vCenter) if I were more confident in my database knowledge.

Since most of the tables will be truncated daily, is there any benefit in using foreign keys rather than just using WHERE logic? Are there other questions I should be asking that I might not know to ask?

Best Answer

PRIMARY KEYs are important; use them. Since there seems to be a 'natural' PK, use it instead of a surrogate AUTO_INCREMENT.

FOREIGN_KEYs are not a requirement. They do, however, create helper INDEXes, but you could create those without FKs`.

If the data is changing every day, what would you do if an FK failed?

Using TRUNCATE, then LOAD leaves the table empty for a brief time; if that is a problem, do this:

CREATE TABLE x LIKE real;  -- or use a regular CREATE.
LOAD DATA ... INTO x;
RENAME TABLE real TO old, x to REAL;
DROP TABLE old;
Related Question