I have a product that includes a MySQL database with some defined countries (very few, not all of them). The user has some control over this table - they can add / edit / delete countries. When we update their software, we run an SQL text file that updates the database, including this table. In this case, for a new version upgrade, we might add a few more default countries.
We use one SQL text file for upgrades that is simply appended to over time, so that the user can upgrade from version X to version Y without going in some specific order. To avoid stomping over user data, I think INSERT IGNORE INTO is the best method to add these default countries. However, this will also have the effect of re-inserting countries that the user may have deleted.
What could be a better method of adding default rows to a table that the user actually has full control over? There's no option of a separate "defaults" table because the user needs control over the table.
Is it possible to check what primary keys no longer exist, and if they don't, do not insert into that table?
Let's say my INSERT statement goes
INSERT IGNORE INTO myTable ('id', 'countryName') VALUES (1, USA), (2, Canada);
If the primary key is at 3, and 2 is missing, how can I avoid re-inserting it?