Enabling foreign keys with SQLite

Foreign keys are not working in iOS (tested only with the simulator so far). I found that I have to execute PRAGMA foreign_keys = ON; I did this in a transaction, like this:

 db.transaction((tx: SQLite.Transaction) => {
  tx.executeSql(
    "PRAGMA foreign_keys = ON;",
    [],
    (_, { rows: { _array } }) => {
      console.log(`Enable foreign keys result: ${JSON.stringify(_array)}`)
    },
    ({}, error) => {
      console.log(`Enable foreign keys error: ${error}`)
    }
  )
})

It doesn’t seem to work. Cascade delete doesn’t work, and if I log the setting in a new transaction, it says it’s still 0:


tx.executeSql(
  "PRAGMA foreign_keys;",
  [],
  (_, { rows: { _array } }) => {
    console.log(`foreign keys result: ${JSON.stringify(_array)}`) // prints {"foreign_keys":0}
  },
  ({}, error) => {
    console.log(`foreign keys error: ${error}`)
  }
)

I found this: Pragma foreign_keys ON doesn't work on IOS · Issue #45 · storesafe/cordova-sqlite-storage · GitHub
There the solution seems to be to execute PRAGMA foreign_keys = ON; “directly” on the db, i.e. without a transaction, but I don’t see an api in Expo to do that.

How do I enable foreign keys?

1 Like

Nobody knows how to use foreign keys with Expo? This is an essential feature of SQLite. Without this the database can get easily in an inconsistent state.

2 Likes

Same problema here…

I know it’s not directly in the Expo docs, but have you tried calling db.executeSql() to see if it works anyway? These various SQLite libraries are typically different interfaces to the same base SQLite implementation, so it might work anyway.

Hi,
did you manage to use foreign keys with Expo?

Hi there,

const db = SQLite.openDatabase('test.db');

db.exec([{ sql: 'PRAGMA foreign_keys = ON;', args: [] }], false, () =>
  console.log('Foreign keys turned on')
);

Source : SQLite - Expo Documentation