SQLite3 sets PRAGMA FOREIGN_KEYS = OFF by default. This is, of course, an invitation to problems.

I know that I can turn on the PRAGMA permanently by placing the command in ~/.sqliterc, but the problem is that it would affect every invocation of SQLite3, not just my own. That could lead to unexpected results in other installed scripts. Thus, I want to avoid using ~/.sqliterc.

Note that I'm using SQLite3 in a Bash script (not typing from the terminal).

One way to solve this is to remember to add the PRAGMA in every invocation of SQLite3 where it's required, e.g.

sqlite3 -cmd 'PRAGMA FOREIGN_KEYS = ON' example.db <<-END_CMD

(I can find other ways that are significantly messier.)

The problem with this is that it's easy to forget, especially when maintaining older code.

So, is there a way to "temporarily-permanently" turn it on, by which I mean to turn it on for the entire duration of a script? I was wondering, for example, if it's possible to put it into an environment variable, e.g.

export PRAGMA_FOREIGN_KEYS=ON    # This doesn't work.

or something like that.

Is there a way to do this, or is using -cmd the simplest way?

  • Note that if you're considering scripts made by yourself (which should get the pragma enabled), and scripts made by someone else (which shouldn't), then there's really no way to tell them apart. E.g. that environment variable, if set in your login shell, would get passed down to all the scripts. So, you're going to need to put something in all your scripts. – ilkkachu Oct 18 '20 at 21:16
  • Yes, I understand that. I am happy to put something in my script, but a script might have a dozen separate calls to sqlite3. I want to put it in my script exactly once, at the beginning. I'm starting to think that there is no other way. – Paddy Landau Oct 19 '20 at 9:46
  • Oh, me of little faith! The edited answer does the trick :) – Paddy Landau Oct 19 '20 at 14:18
    sqlite3 -cmd 'PRAGMA FOREIGN_KEYS = ON' "$@"
sqlite3pragma example.db <<-END_CMD
  • Thanks. That's good if you're using the terminal, but I'm using a script. My bad for not saying so; I've edited my document to say so. – Paddy Landau Oct 18 '20 at 20:41
  • That could work for a script as well if you put it into ~/.bashrc – Artem S. Tashkinov Oct 18 '20 at 21:30
  • Aliases don't work in scripts, so no. – Paddy Landau Oct 19 '20 at 9:45
  • Made it a function then - I've changed the answer. – Artem S. Tashkinov Oct 19 '20 at 10:23
  • 2
    You can also make the function have the same name, sqlite3() { command sqlite3 -cmd 'PRAGMA...'; } (using command suppresses function lookup) – ilkkachu Oct 19 '20 at 10:33

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.