Can A Foreign Key Be Uploaded From An External Source

This commodity is a office of a longer discussion about the lack of foreign key constraints in databases. Article about reasons why many databases don't take FK constraints sparkled a deep and passionate discussion on LinkedIn groups about how those keys are of import to brand databases work. I mostly agree with such argument, merely my experience told me that practice is far from theory. So I started a inquiry and found out that near packaged ERP/CRM applications don't accept foreign keys. Now, I collected data from 40 databases I have worked with for the by 10+ years (that I still take admission to or a recollection of). Here are results of my research. I hope this is just an introduction to more vast research that you will contribute to also.

Data

This is the data from all the databases I still have admission to or its schema, or I remember their design. Those are databases designed by many entities - pocket-size teams and large global organizations.

The final column shows whether the database has strange key constraints.

Analysis

Let's have a look at the results - it turns out that 75%! of databases I worked with don't accept foreign key constraints. For something that about consider a must have this is surprising.

Awarding Type

Let'southward starting time break this downwards past application type:

Custom and packaged applications, ERP/CRM

In that category I put all the databases of transactional applications such as CRM, ERP, HRMS, billing, practice management that assistance companies in their daily operations, both web and desktop. Bespoke and off-the-shelf. I believe most DBAs and architects would say this is the category that needs FK constraints the most - they have many users and data nerveless there is critical for whatsoever organization. And notwithstanding a majority of them seem to handle referential integrity lightly. What is virtually surprising is that packaged off-the-shelf applications even more than so.

Data warehouses

Data warehouses are databases designed specifically for reporting and analytical purposes of vast amounts of information and loaded from external sources. Most people agreed that data warehouses are among exceptions as their data consistency is managed in the source databases. The results of 0% of FK constraints is what I expected.

Tools

In this category fall all kind of tools - forum and blog engines, modeling and reporting tool repositories. They are either open source or proprietary and don't carp much about consistency.

Other

I put into reporting applications category databases that are somewhere betwixt transactional applications and data warehouses - they are used for reporting but populated past users. Interfaces are databases that are used to feed some other database. Those two categories were a scrap surprising for myself every bit I would bet those databases would exist the worst. Somewhen, they turned out to be all implementing referential integrity checks.

Custom vs. Packaged

Split between custom/bespoke databases that are designed for item client and packaged/off-the-shelf databases looks like this:

What is surprising, databases designed for a multi-use purpose, tend to care less near such pattern details than custom ones. It's odd since designers of such applications should take more fourth dimension and put more than stress to design database schema properly. In the latter case, where teams demand to act more than quickly, deadlines are short, and budgets always seem as well modest (I know considering this is what I was doing over 15+ years of my career) results were better.

At that place are some clues why packaged databases don't use strange keys equally much.

Transactional vs. Reporting

Carve up past transactional databases and those whose purpose is reporting is a little unexpected. Reporting databases in my data set have more FKs but it might be acquired by its small-scale size (and those ii reporting applications).

DBMS

When we look at particular DBMS, you lot tin can see big differences. Oracle databases seem to exist much meliorate than SQL Server ones, and MySQL appears non to support FKs at all. This might exist caused by specific data set. Nigh a half of those case SQL Server databases are in one surroundings, and this just might be the crusade of specific blueprint "style". If I exclude this surround, and then the results are very close to the Oracle.

I have a very pocket-sized data set regarding MySQL. This category is mostly open source tools. Information technology might be the case that open source teams don't care about FKs.

Not surprisingly, cross-platform databases (Due north/A) don't take strange keys, and this might exist explained by the fact that designers wanted to be as lilliputian dependent on the database platform as possible.

Share Your Data!

As y'all noticed, my inquiry was based on a modest information set - xl databases - and that it was not too representative. I am very interested in your experience and the databases you lot work with. Please accept some fourth dimension to compile this information and share the results with me. Send me your data at dataedo@dataedo.com or:

Accept a poll

In one case I collect enough data, I will compile it into similar report and share on our weblog.

Give thanks you!