We linted 6,008 real database schemas. 45% of foreign keys have no index.
Study
We linted 6,008 real database schemas. 45% of foreign keys have no index.
mcdview.dev · 8 min read
We took 6,008 database schemas from 6,006 public repositories (SQL dumps, Prisma, Rails, Drizzle, DBML, pgModeler) and ran each one through mcdview's linter. This is what real schemas look like, as opposed to what best-practice posts say they should look like.
Updated on September 27, 2026. The first version of this study, published earlier the same day, used mcdview 0.34.2. Its linter missed index declarations in Rails and Drizzle schemas, UNIQUE and KEY clauses written inside CREATE TABLE, and the primary keys that phpMyAdmin exports add with ALTER TABLE. mcdview 0.34.3 reads all of them, so every figure below is recomputed: foreign keys without an index went from 48.0% to 45.1% (Rails and Drizzle are now included), and tables without a primary key from 11.2% to 5.0%.
The corpus
The raw material is mcdview's own test corpus: 15,197 schema files collected from public GitHub repositories to make sure the parser survives real-world input. Once duplicates (backups, forks, identical copies) and the hand-made test files were removed, 10,789 distinct files remained. We kept every file that parsed and held at least three tables, and left out Mermaid diagrams, which carry no constraints or indexes. That leaves 6,008 schemas from 6,006 repositories: almost exactly one schema per project.
Public repositories are not all production apps, there is coursework and tutorials in there. To see whether that skews the picture, we looked at the 435 schemas with 50 tables or more separately. It does not, as you will see below.
45% of foreign keys have no index
Of the 73,520 foreign keys we could check, 45.1% are not covered by any index: the column is neither the first column of the primary key nor the first column of an index.
On PostgreSQL, SQLite and SQL Server this is not cosmetic, because the database does not create that index for you. Every time a parent row is deleted, or its key updated, the engine has to find the child rows that point to it, and without an index that means scanning the whole child table. Joins from parent to child pay the same price. MySQL's InnoDB does create the index automatically, which is why MySQL is left out of this figure.
DBML (dbdiagram)81.0 %
pgModeler77.4 %
Drizzle50.8 %
PostgreSQL SQL49.1 %
SQLite SQL42.2 %
Prisma36.8 %
Rails3.5 %
Share of foreign keys not covered by an index, by source. MySQL excluded: InnoDB indexes foreign keys itself.
The worst scores come from design tools. With DBML (dbdiagram.io) and pgModeler, close to four foreign keys in five have no index. You draw a line between two boxes and the relationship exists; nothing in the drawing reminds you that the column behind it needs an index.
Among ORMs the gap is huge, and it comes down to defaults. In Rails, `t.references` creates the index unless you opt out: only 3.5% of foreign keys are left bare. Drizzle creates nothing for you, and 50.8% have no index. Prisma, which does not index foreign keys on PostgreSQL either, sits at 36.8%. Hand-written PostgreSQL lands at 49.1%. The convention does the work that discipline often does not.
For contrast, GitLab, the largest schema in the corpus with 1,066 tables, leaves 42 of its 1,919 foreign keys without a covering index: 2.2%. A schema that size stays usable because someone checks. Explore GitLab's schema on mcdview.
One schema in four declares no foreign key at all
24.5% of the schemas declare no foreign key, and 43.0% of all tables have no relationship in or out. The relationships exist in the application code; the database simply does not know about them.
MySQL SQL46.6 %
Drizzle26.6 %
PostgreSQL SQL23.7 %
Rails23.1 %
SQLite SQL21.2 %
Prisma5.8 %
pgModeler4.2 %
DBML (dbdiagram)0.0 %
Share of schemas (3 tables or more) that declare no foreign key, by source.
MySQL stands out: 46.6% of its schemas without a single foreign key, 72.2% of its tables isolated. Some of that is history (MyISAM, the old default engine, ignored foreign keys) and some is habit. At the other end, the tools that write the constraint for you produce the most connected schemas: pgModeler (4.2%), Prisma (5.8%) and DBML (0.0%) declare a relationship as soon as you model one.
Rails sits in between at 23.1%. A `belongs_to`in a model does not create a database constraint; that takes `add_foreign_key` or `foreign_key: true`. LinuxFr.org ran that way until 2018, then added 29 foreign keys in a single commit: we told that story as a time-lapse.
Bigger is not cleaner
| Size | Schemas | No foreign key | Isolated tables | Tables without PK | | ----------------- | ------- | -------------- | --------------- | ----------------- | | 3 to 9 tables | 3,126 | 29.1% | 38.6% | 5.8% | | 10 to 49 tables | 2,447 | 18.8% | 37.9% | 5.0% | | 50 tables or more | 435 | 23.4% | 50.6% | 4.6% |
If the corpus were dominated by toy projects, the large schemas would look healthier. They do not: past 50 tables, 23.4% still declare no foreign key and 50.6% of the tables are isolated. A schema grows by adding tables around an existing core faster than anyone wires them in.
Tables without a primary key
5.0% of tables have no primary key, and 18.3% of schemas contain at least one such table. Without a primary key a row cannot be addressed reliably, PostgreSQL's logical replication refuses updates and deletes on the table until you set a replica identity, and most ORMs struggle. Hand-written SQL sits between 5.6% and 6.9% whatever the engine. The tools that enforce a key do best: Rails gives every table an `id` unless told otherwise (1.8%), and Prisma requires every model to have a unique identifier (1.0%).
Check your own schema
On PostgreSQL, this query lists the foreign keys whose first column does not lead any index:
SELECT c.conrelid::regclass AS table_name,
a.attname AS fk_column
FROM pg_constraint c
JOIN pg_attribute a
ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);Or drop your schema on mcdview.dev: isolated tables show up at a glance on the diagram, and `mcdview --lint` flags missing primary keys, isolated tables and uncovered foreign keys, which you can run in CI.
Method, in detail
- Linter: mcdview 0.34.3 with `--lint`, run in the same container as mcdview.dev, with the same Prisma, DBML and pgModeler converters.
- A foreign key counts as covered when its column is the first column of the primary key or of an index: that is what PostgreSQL needs to find the child rows through an index.
- MySQL and MariaDB are left out of the index figure because InnoDB indexes foreign-key columns automatically.
- One schema per repository (the largest), so that a repository with many files does not outweigh the others.