Skip to main content
Solved

SQLite database: how to enforce foreign_keys.

  • September 21, 2020
  • 2 replies
  • 70 views

davtorgh
Contributor
Forum|alt.badge.img+12

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

Best answer by xilef

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.).

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

2 replies

xilef
Contributor
Forum|alt.badge.img+5
  • Contributor
  • Best Answer
  • September 21, 2020

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
Contributor
Forum|alt.badge.img+12
  • Author
  • Contributor
  • September 21, 2020

Hi @xilef​ ,

 

great answer! I didn't notice the parameter SQL to run before Write.

I select your answer as best.

 

Thanks