Question

Insert into multiples tables and manage foreign keys

  • 16 April 2019
  • 7 replies
  • 23 views
A
Badge

Hi,

I need to insert data into different tables and some of them are linked by a relationship (example : a table that contains buildings and a table with the parcels). I need all the parcels to be insert into the database before to start the insert of the building as I want to know for each building the unique ID (generated by a PostgreSQL serial type) of the related parcel.

All my Readers are linked to a single FeatureWriter. Is there a way to specify that this feature writer must process the data one after the other (the order is defined by the list in featureTypes) ? Or any other possibility ?

 

Thanks

7 replies

david_r
Rank
Userlevel 4

There are several ways of doing this, but personally I like using multiple FeatureWriters and chain them with a FeatureMerger set on a dummy merge criteria (e.g. 1 = 1).

The pattern looks like this:

This will ensure that the Master features have all been written and committed before the Detail features.

Here's also an article from Safe with some alternative strategies:

https://knowledge.safe.com/articles/44622/working-with-foreign-keys-writing-database-tables.html

A
Badge

Hi @david_r,

Thanks for your solution.

 

Could you please just show me a picture of the content of the dialog box of FeatureMerger please ?

 

I dont't understand very well where to put the dummy merge criteria.

Thanks

david_r
Rank
Userlevel 4

This is what I had in mind, simply typing the same hard-coded value in the Requestor and Suplier join criteria:

This would be what's often called an "unconditional merge", meaning that every requestor is joined with every supplier.

A
Badge

This is what I had in mind, simply typing the same hard-coded value in the Requestor and Suplier join criteria:

This would be what's often called an "unconditional merge", meaning that every requestor is joined with every supplier.

I'll try it and I'll let you know if it is working for me !

 

Thanks
A
Badge

There are several ways of doing this, but personally I like using multiple FeatureWriters and chain them with a FeatureMerger set on a dummy merge criteria (e.g. 1 = 1).

The pattern looks like this:

This will ensure that the Master features have all been written and committed before the Detail features.

Here's also an article from Safe with some alternative strategies:

https://knowledge.safe.com/articles/44622/working-with-foreign-keys-writing-database-tables.html

Hi @david_r,

I've finally been able to find some time to test your solution.

 

Something is probably wrong with my settings as the process stops with this error raised: "EXTRA_REFERENCEE_FEATURE"

 

Do you have any idea on what is wrong ?

 

 

Thanks for your help
A
Badge

Hi @david_r,

I've finally been able to find some time to test your solution.

 

Something is probably wrong with my settings as the process stops with this error raised: "EXTRA_REFERENCEE_FEATURE"

 

Do you have any idea on what is wrong ?

 

 

Thanks for your help

I may have found a solution by activating "Process Duplicate Suppliers" and set Build Aggregates and the just after add a Deaggregator process.

 

It seems to work...

 

Would you have done it like that?
david_r
Rank
Userlevel 4

Hi @david_r,

I've finally been able to find some time to test your solution.

 

Something is probably wrong with my settings as the process stops with this error raised: "EXTRA_REFERENCEE_FEATURE"

 

Do you have any idea on what is wrong ?

 

 

Thanks for your help

The message means that you have more than one supplier for a requestor in the FeatureMerger. You can try enabling the option "Process duplicate suppliers" in the FeatureMerger. Try leaving the associated options as-is.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.