Skip to main content
Solved

SQLite database: how to enforce foreign_keys.

  • September 21, 2020
  • 2 replies
  • 38 views

davtorgh
Contributor
Forum|alt.badge.img+10

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

View original
Did this help you find an answer to your question?

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+10
  • 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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings