Should Database Tables Have Foreign Keys in System Development?

At a project delivery site in 2026, whether to add foreign keys to data tables often splits opinions between frontend and backend teams. Based on experience range: for internal management systems and low-concurrency admin panels, adding foreign keys can save a lot of maintenance logic; for public-facing high-concurrency write scenarios, the lock and validation overhead caused by foreign keys is not worth it, and dropping them is more common. The key is not "can you use them" but "how to weigh the costs and benefits."

What Is the Foreign Key Debate Really About?

A foreign key is a referential integrity constraint at the database layer. It ensures that the relationship between two tables is not broken. Opponents most often point out: every insert or update requires an extra lookup on the related table, which amplifies lock contention under high concurrency; foreign keys become almost useless after database sharding; and when parent table data is accidentally deleted, foreign keys block the operation, making production failures harder to recover from. Supporters value the data consistency it brings—once data is written, there will be no bad references like "an order pointing to a nonexistent product."

In essence, the debate centers on "which layer should hold the constraint": the database layer or the application layer. Database-level foreign keys are declarative constraints that simplify development; application-level validation is proactive control that can adapt to more complex business rules. A common practice in 2026 is: keep foreign keys for core accounting and basic master-data tables, but try to avoid them for peripheral business tables.

• What foreign keys bring: data integrity, index utilization in join queries, and ORM-generated foreign key constraint errors
• What foreign keys cost: write performance degradation, obstacles to sharding, difficult DDL changes, and blocking on accidental deletes
• What you really need to compare: whether your team can bear the consequences of "data inconsistency"

Before Adding Foreign Keys, Run This Check: The Five-Question Checklist

Foreign key decisions are fundamentally trade-offs and must be grounded in your own data flow. Based on my experience in enterprise project delivery, I suggest walking through the following five questions one by one. In Xiyue Company's projects, this checklist has helped us reduce rework by more than half.

1. Write frequency: How many rows per second? Experience range: For public-facing tables with concentrated writes exceeding a hundred per second, be cautious about adding foreign keys.
2. Consistency requirements: For accounting, orders, inventory, and other cases that must be strictly consistent, foreign keys are worth it; content and log tables do not need them.
3. Sharding plans: If you plan to shard within the next year, give up foreign keys early, otherwise migration will be painful.
4. Team maintenance capability: Do you have a dedicated DBA? Without someone monitoring the database, too many foreign keys become a hidden hazard.
5. Legacy baggage: Existing systems with foreign keys incur costs to remove arbitrarily; new tables can be decided by new logic.

Note that the first question should be based on write peak, not average; the fourth question asks about operational capability, not development capability. What counts as passing? If most of the five questions point to "yes, we can add," then add them; if either question 2 or question 3 acts as a veto, do not force it.

How to Compensate for Consistency When Not Using Foreign Keys

Abandoning foreign keys does not mean giving up data consistency; it means moving the constraint elsewhere. Common alternatives in 2026 include: checking related records first in application-layer transactions; using optimistic locks or state machines to guarantee concurrency order; scheduling tasks to scan for orphaned data; running reconciliation scripts for timely alerts; and using database triggers when necessary (though triggers also affect performance).

• Option A: Database foreign keys – low development cost, high performance overhead, difficult operations; suitable for low-concurrency core tables
• Option B: Application-level validation – moderate development cost, controllable performance, requires writing reconciliation logic; suitable for high-concurrency business tables

Experience range: For a typical CRUD module, application-level validation requires about 20% more development effort than using foreign keys, but the resulting write-throughput elasticity is often greater. The exact cost depends on the number of joins and exception-handling branches.

In a recent inventory management project, the client required strict consistency between inventory records and purchase orders, but concurrency was not high and the budget was limited. After running the five-question check, we decided to keep foreign keys on the core transaction tables and not add them to peripheral report tables. As a result, we had far fewer dirty-data problems during integration testing, and no obvious performance bottlenecks after launch. Conversely, in another public-facing e-commerce project, because we blindly followed the "no foreign keys" approach at the start, the application-layer validation missed the return scenario, leading to orphaned data; patching the reconciliation script took two iterations.

Applicable Scenarios and Boundaries

Scenarios suitable for adding foreign keys: internal management systems, backend content platforms, core business tables with low-frequency writes and high consistency requirements; teams with dedicated database maintenance staff; single-database, single-table scale, with no plans to split the database in the next three years.

Scenarios not suitable for adding foreign keys: C-facing high-concurrency write tables; tables that have been sharded or are explicitly planned to be sharded; large tables with over 100 million rows; teams without a DBA where application-layer development dominates; and cases having frequent DDL changes on large tables after launch.

Foreign keys are neither a silver bullet nor a scourge; they are suitable for scenarios where "consistency benefits outweigh lock overhead." If the write volume is stable at a few hundred per minute and the business is core, adding foreign keys is usually cost-effective.

FAQs

Do foreign keys affect query performance?

During queries, foreign keys generally do not affect read performance, but they add extra validation on writes; if no index is created, the lock scope can increase. It is recommended to build an index on the foreign key column in the child table.

Can foreign keys still be used after sharding?

Currently, mainstream sharding middleware does not support cross-database foreign keys. After sharding, foreign key constraints become ineffective, and consistency is generally ensured at the application layer.

What if a foreign key error occurs when deleting data?

This is normal feedback from the foreign key protecting references. First check whether the child table has related records, confirm the strategy, and then delete; do not simply drop the foreign key to avoid the problem.

Is using foreign keys over-engineering?

For low-concurrency core tables, no; for high-concurrency tables, blindly adding foreign keys without consideration becomes a burden. The judging criteria are write frequency and consistency requirements, not the number of tables.

Without foreign keys, how do you avoid dirty data?

In application-layer transactions, check the parent table first, then write to the child table; use scheduled reconciliation scripts to scan for orphaned data; and add unique indexes as a fallback when necessary.

First, print out the five-question checklist and tick off each item before deciding. If you are working on an internal system or a low-concurrency core database, adding foreign keys is usually the safe choice; if it is a high-concurrency public-facing business, immediately plan application-layer validation and reconciliation. When in doubt, pick a non-core table, run a trial for one iteration, and let production data speak.