Piotr Kononow
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.
