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:

"Big" applications use their sophisticated higher level frameworks and platforms (like SAP's ABAP) that are responsible for data validation. Those applications need to be open for change and elastic as possible. 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.