1

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?

  • One possible way is adding a is_deleted tinyint column, with 0 as default. Instead of deleting, you can set it to 1, and then filter these out in the queries. This way you always have all countries that ever got into the table. – dezso Apr 30 '15 at 14:25
  • Ah, that's a really great way to do it. Thank you. – Jd-582 Apr 30 '15 at 14:30
0

One possible way is adding an is_deleted tinyint column, with 0 as default. Instead of deleting, you can set it to 1, and then filter these out in the queries. This way you always keep all countries that ever got into the table.

In some previous projects of mine we had quite a lot of these, setting them using ON DELETE triggers. In turn, the queries nearly all went to views that were defined with a condition in the WHERE clause like

...
AND is_deleted = 0 -- well, it was Postgres, so originally AND NOT is_deleted
...

The downside of this is an additional level of dependencies (or two). The other solution is changing the queries to filter out the rows that are marked as deleted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.