The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maintaining disabled FK’s, wisdom or farce?

A while back, I wrote a post about having FKs (foreign keys) in your data warehouse.

Well, a similar question came up recently on an Oracle forum with the above title. It is a fair question and it does surface fairly regularly in a variety of contexts (not just data warehousing).

Of course, as The Data Warrior, I felt is was my duty to respond.

The Question

Is there any reason to maintain a permanently disabled FK in the data model?  I’m not envisioning a reason to do it.  If it is not going to be enabled, then from my perspective, it would not make any sense to have it defined.  If anything, provide the definition of the relationship in the comment of the child column.

My Answer

Yes, by all means keep the FK please!

I see three good reasons for doing so:

  1. It is valuable metadata (& documentation). If somebody reverse engineers the database (say with ERWin or Oracle Data Modeler), the FK shows up in the diagram (way better than having to read a column comment to find out)
    Data Vault 2.0 Example

    A picture is worth a thousand words!

    .

  2. BI Metadata – If you want to use any sort of reporting or BI tool against the database, most tools will import the FK definition with the tables and build the proper join conditions. Way better than having someone guess what the join will be and then manually adding it to the metadata layer in the reporting tool. Examples that can read the Oracle data dictionary include OBIEE, Business Objects, COGNOS, Looker, and many others.(Note here that since the FK is not enforced on the remote databases, you might want to make sure these are treated as outer joins, lest you lose some transaction in the reports).
  3. The Oracle optimizer will use disabled constraints to improve query performance of joins. Again, this is metadata in the data dictionary which the optimizer can read. This is documented in the Oracle Data Warehouse guide and I have validated it on multiple occasions with Oracle product management.

While #3 applies specifically to Oracle, for other databases like MS SQL Server and Snowflake, #1 and #2 still apply.

Even if only one of the above is true for a given database, that, in my opinion, still justifies keeping the disabled constraint around.

Final Answer = Wisdom

What do you think? Feel free to comment below.

And please share on your favorite social media platform!

Model on!

Kent

The Data Warrior

 

Advertisements

Single Post Navigation

10 thoughts on “Maintaining disabled FK’s, wisdom or farce?

  1. Liz Beechey on said:

    Hey Kent! Great tip! I’m usually of the mindset that if something is not used – get rid of it. But your post has convinced me that in this case it is more than worthwhile to keep the disabled FK around. Thanks again! Keep up the great work!

  2. Cesar Vinas on said:

    How do I contact you to ask about training?

  3. My immediate thoughts are that too many folks are willing to just accept the status quo. Thanks Kent for yet more documentation on why FK should remain.

  4. Jacco H. Landlust on said:

    Kent,

    Perhaps you could (should) have mentioned the rely option that Oracle provides on foreign key constraints? Also novalidate comes to mind. In combination with query_rewrite_integrity = TRUSTED you can benefit from all goodies that the optimizer has to offer without having to worry about loading or removing data in the order that the constraints dictate.

    Yes this can impact data integrity, but hey: that was not what you were keeping the contraints for anyway….

    Best,

    Jacco

    • Thanks for mentioning those other options Jacco. I am in the midst of a discussion with Tim Gorman about all these variants as well. In the case of Oracle there are indeed many possible combinations and options which affect the optimizer, I just did not want the post to get into the technical weeds too much but rather to get folks thinking about this a bit more seriously and seeing there are good reasons to not just drop constraints without a bit more analysis. Seems there are quite a few folks that did not really know that constraints were even taken into consideration during query optimization.

  5. Hi Kent, I fully agree that the disabled FK’s should stay, as implicit documentation, and as food for tools or scripts that read the physical datamodel to extract metadata.

  6. Walter Howard on said:

    Interesting you left off data quality. Sure ETL has to lookup the parent anyways, but the same ETL has to code around what to do when the parent is not found. And what should I do if the FK contributes to the PK? Before you know it, you can have a ton of suspect data in your warehouse.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: