Working with Foreign Keys: Writing Database Tables

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2017.x

Introduction

There are a number of ways to “let your database do the work”, we’ll take a look at the ways to use FME to perform a more effective write to Oracle.

When writing to a database using FME, errors can occur if there are constraints on the table that are being violated. Constraints are rules that restrict the possible values for a column in a database. Typical constraints include not null, unique, and keys (primary and foreign). The exercise outlined below will address a method to deal with a failure on load caused by writing to tables joined by a foreign key.

Although this exercise uses an Oracle database, the concepts and methods discussed can be applied to other databases such as PostgreSQL/PostGIS, and SQL Server.

 

Step-by-step Instructions

The goal of this exercise is to show the options you have for writing to database tables that have a constraint, such as a foreign key constraint. In the example, you will load a water pipe network (WMAINS) and also create an assets table (WASSETS) that has the pipe manufacturer attribute. The two tables are related through a foreign key constraint (called WMAIN_WASSETS).

 

Run the Workspace

1. Open the workspace: DatabaseWrite-Start.fmw. Run it.

The workspace is writing to two joined tables in Oracle. The parent table (WASSETS) has MANUFACTURERID as a primary key and the child table (WMAIN) has a primary key of MAINID with MANUFACTURERID as a foreign key.

The workspace fails throwing the following error:

|ERROR |Execution of statement `INSERT INTO "WMAIN" ( "MAINID", "ENABLED", ... ) VALUES ( :"MAINID", :"ENABLED", ...)' did not succeed; error was `ORA-02291: integrity constraint (WMAIN_WASSETS) violated - parent key not found'. (serverType=`ORACLE8I', serverName=`<server>', userName=`<user>', password=`***', dbname=`')

The error occurs because the foreign key constraint (called WMAIN_WASSETS) on the WMAIN table was violated. To satisfy the constraint, you have to write to the parent table before writing the child table that has the foreign key.


The following examples illustrate different ways you can address this problem and write to the related tables.

 

Example 1: Using FeatureHolder

The FeatureHolder is used to hold the features directed to the child table (WMAIN). Since there are only a few features being written to the parent table (WASSETS), it is able to complete the write before the FeatureHolder releases the features. This effectively stages the workflow.

 

1. Clean Up Tables 

Before running this example, you need to clean up the database tables. Drop and recreate the tables using the SQLCreator transformer. Open the transformer properties, select SQL Statement and select Run… Then Cancel to exit the transformer properties.

 

2. Hold Features

Add a FeatureHolder before the WMAIN table.

example1-featureholder.png

 

3. Run the workspace and inspect the log file and the results

The FeatureHolder is useful for workspaces with smaller numbers of features. If there are a large number of records being written to the table and held in the FeatureHolder, it will impact the overall performance.

 

Example 2: Using Connection Runtime Order

You can control the order of features exiting a transformer using the Connection Runtime Order option. This example uses connection runtime order to ensure parent records are inserted first.

 

1. Clean Up Tables 

Before running this example, you need to clean-up the database tables. Drop and recreate the tables using the SQLCreator transformer. Open the transformer properties, select SQL Statement and select Run… Then Cancel to exit the transformer properties.

 

2. Delete FeatureHolder

Delete the FeatureHolder transformer from the previous step.

example2-connectionruntime.png

 

3. Set Runtime Order

Set the Connection Runtime Order by right-clicking on one of the output connectors that exit the AttributeManager. Ensure the WASSET connection is first. This will ensure that the WASSET records are inserted before their corresponding WMAIN record, ensuring that the WMAIN constraint is satisfied.

example2-connectionorder.png

 

4. Set Transactions

Set the Features Per Transaction to 1. You have to use a transition interval of 1 to guarantee that the WASSETS records has been committed before the WMAIN record is inserted.

example2-featurespertransaction.png

 

5. Run the translation

As you can see, setting a transaction interval of 1 has a big impact on performance as a commit is carried out for every feature that is written. It does ensure that the records are written into the parent table prior to writing into the child table.

 

Example 3: Using FeatureWriter

The FeatureWriter transformer is an alternative way of writing data. Moving the write operation into the workspace workflow gives you more flexibility over the ordering that feature types are written and how you can pre- and post-process the data.

In this example you’ll use SQL calls to disable and then enable the WMAIN_WASSETS foreign key constraint. In between you’ll load the data.

 

1. Clean Up Tables

Drop and create the tables using the SQLCreator as described in Example 2 above so you start with a clean slate.

 

2. Delete Writer

Disable or delete the Oracle writer.

 

3. Add an SQLCreator

Add a SQLCreator for the Oracle database and add the following SQL:

FME_SQL_DELIMITER ;
ALTER TABLE "WMAIN" disable CONSTRAINT WMAIN_WASSETS;

The SQLCreator is executed before any of the FME readers are opened, so we’re guaranteed the constraint is dropped before any features start to get written.

 

4. Add a FeatureWriter

Add a FeatureWriter to the workspace canvas. Open the FeatureWriter parameters dialog. Select the Oracle Spatial format and select your dataset.

 

5. Import Tables

Import the Oracle tables, WMAIN and WASSETS using Oracle Non-Spatial as the format.

 

example3-featurewriterparameters.png

 

6. Connect FeatureWriter

Connect the FeatureWriter as shown below in step 7. The order of the features types is not important since we disabled the constraint.

 

7. Add an SQLExecutor

Add a SQLExecutor for your Oracle database and add the following SQL:

FME_SQL_DELIMITER ;
ALTER TABLE "WMAIN" enable CONSTRAINT WMAIN_WASSETS;

example3.png

 

8. Run Workspace

Run the workspace and inspect the results.

 

Discussion Topics

  • What are the pros & cons of each example?
    • Perhaps each approach is more appropriate for different scenarios and table constraints such as 1:M M:N etc.
  • Which gives the best performance?
    • Can you trade performance for simplicity?
  • Which example might work best for an update workflow?
  • Are there any other approaches?
  • What if there is an error, which approach has the easiest recovery?

 

Additional Resources

Making use of Oracle Sequences when working with Oracle Tables

Writing to an Oracle Table with Foreign Keys

The AttributeValidator can be used to check supported conditions prior to loading. This allows the user the chance to see and correct any features that may cause a failure on write.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.