Working with Foreign Keys: Writing Database Tables

Introduction

There are a number of ways to “let your database do the work.” We’ll look at ways to use FME to write more effectively to Oracle.

When writing to a database using FME, errors can occur if you violate constraints on the table you're accessing. 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 below outlines a method for handling a load failure caused by writing to tables joined by a foreign key.

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

Step-by-step Instructions

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

Starting in FME 2025.2, many formats that support both spatial and non-spatial data have been combined into a single reader or writer. For an up-to-date list of the formats this change has been applied to, please see Combined Spatial and Non-Spatial Readers and Writers.

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 the primary key, and the child table (WMAIN) has MAINID as its primary key, 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 must write to the parent table before writing to the child table with the foreign key.

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

Example 1: Using FeatureHolder

FeatureHolder holds the features directed to the child table (WMAIN). Since only a few features are written to the parent table (WASSETS), it can complete the write before the FeatureHolder releases the features. This effectively stages the workflow.

1. Clean Up Tables 

Before running this example, 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 click Cancel to exit the transformer properties.

2. Hold Features

Add a FeatureHolder before the WMAIN table.

3. Run the Workspace and Inspect the Log File and the Results

The FeatureHolder is useful for workspaces with fewer features. If a large number of records are being written to the table and held in the FeatureHolder, it will impact 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, 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, click Cancel to exit the transformer properties.

2. Delete FeatureHolder

Delete the FeatureHolder transformer from the previous step.

3. Set Runtime Order

Set the Connection Runtime Order by right-clicking one of the output connectors exiting the AttributeManager. Ensure the WASSET connection is first. This ensures WASSET records are inserted before their corresponding WMAIN record, satisfying the WMAIN constraint.

4. Set Transactions

Set the Features Per Transaction to 1. Use a transaction interval of 1 to ensure the WASSET records are committed before the WMAIN record is inserted.

5. Run the translation

As you can see, setting a transaction interval of 1 has a big impact on performance, as it commits after every feature is written. It ensures records are written to the parent table before writing to 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 provides more flexibility in the ordering of feature types written and in how you can pre- and post-process the data.

In this example, you’ll use SQL calls to disable, 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 runs before any FME readers open, so we’re guaranteed the constraint is dropped before any features start to be 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.

6. Connect FeatureWriter

Connect the FeatureWriter as shown below in step 7. The order of the feature types doesn't matter 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;

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

Writing to an Oracle Table with Foreign Keys

The AttributeValidator can check supported conditions before loading. This allows the user the opportunity to review and correct any features that may cause a failure during writing.