SQLite database: how to enforce foreign_keys.

Question

Hi everybody,

I need to write to a sqlite database containing tables with foreign keys constraints, but FME seems not to enforce the foreign_keys pragma directive when connecting to the database. I discovered this by running a

pragma foreign_keys;

query against the database with an SQLCreator (it returned 0).

This is obviously a problem, because in this way the SQLite Writer can insert records that violate referential integrity.

Is there a way to make the SQLite Writer execute the

pragma foreign_keys = 1;

directive before start writing?

Any suggestion is welcome!

Thanks

xilef · Accepted Answer

Hi @davtorgh​, have you looked into the "SQL  to run before Write" option in the Writer parameters? This option should do what you are asking. Also if you are writing to the table containing the primary key corresponding to the foreign key of that table in the same workspace, make sure it is being written first. Many ways can be done to respect the order of writing (ex: order of writers in the Navigator window,  using SQLExecutor transformers or FeatureWriter transformers in a specific sequence etc.).

davtorgh · Answer

Hi @xilef​,great answer! I didn't notice the parameter SQL to run before Write.I select your answer as best.Thanks

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded