Major Applications Do Not Use Foreign Key Constraints In Their Databases (Oracle, Microsoft, SAP)

Piotr Kononow - Dataedo Team Piotr Kononow 2017-08-09 2019-02-14 ERP · CRM · Application · Foreign Keys

Table of Contents:


    Recently, I had a number of discussions in LinkedIn groups about the lack of foreign key constraints in the databases and most architects, DBAs, and developers were surprised or even outraged that this happens. This got me thinking about the problem and I wanted to dig deeper into this matter and I did a lot of research. One of my findings is that many flagship applications of major ERP/CRM vendors, like Microsoft, Oracle or SAP, don't have foreign key constraints in their databases.

    Applications and FKs: The Data

    Application Type Vendor Has FKs?
    Oracle e-Business Suite ERP Oracle No
    JD Edwards EnterpriseOne ERP Oracle No
    JD Edwards World ERP Oracle No
    Siebel CRM Oracle No
    PeopleSoft ERP Oracle No
    Primavera PPM Oracle Yes
    Dynamics CRM CRM Microsoft Yes
    Dynamics NAV ERP Microsoft No
    Dynamics AX ERP Microsoft
    Dynamics GP ERP Microsoft No
    SAP ERP SAP No
    Infor ERP LN (formerly Baan) ERP Infor No

    Help me complete the data

    I compiled this data from information I found on the Internet - on forums, documentation etc. Some of it might be incorrect - please let me know (in the comment to this article or write me at: dataedo@dataedo.com).

    If you work with any other popular application and know whether they use foreign key constraints in their database, please share that too.

    Legend

    Here is what acronyms used in this article mean:

    • CRM - Customer Relationship Management
    • ERP - Enterprise Resources Planning
    • PPM - Project Portfolio Management

    Reasons Why

    I haven't seen any official statement of architects of any of those applications so I can only speculate about the reasons why it is so based on my experience and opinions I read out there. In my opinion, those reasons are:

    1. "Big" applications use their sophisticated higher level frameworks and platforms (like SAP's ABAP) that are responsible for data validation.
    2. Those applications need to be open for change and elastic as possible.
    3. Legacy data is often inconsistent.

    Read more about reasons why databases don't have foreign key constraints in this article.

    Applications Have Their Own Metadata

    Most of those applications keep metadata on data schema in their own proprietary structures. Such structures are called data dictionaries that keep information about table relationships and foreign keys.

    Learn more

    More articles about foreign keys.

    0
    There are no comments. Click here to write the first comment.