Django ORM is my favorite ORM, as it comes with a complete database management solution out of the box. However, it doesn’t always adapt well in certain companies—for example, see how Django Migrate is used in your company. This time, let’s talk about foreign keys.
What is a foreign key
Relational databases are called “relational” because maintaining relationships between data is one of their key features. Foreign keys are the cornerstone of maintaining these relationships. For example, we create two tables: one is the students table, and the other is the enrollments table.
select * from students;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
select * from enrollments;
+----+------------+--------+
| id | student_id | course |
+----+------------+--------+
| 1 | 1 | 数学 |
| 2 | 2 | 语文 |
| 4 | 1 | 英语 |
+----+------------+--------+The student_id in the enrollments table is linked to student.id. So what does the foreign key do here?
The SQL for creating the enrollments table is as follows:
CREATE TABLE `enrollments` (
`id` int NOT NULL AUTO_INCREMENT,
`student_id` int NOT NULL,
`course` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `student_id` (`student_id`),
CONSTRAINT `enrollments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
)Here, CONSTRAINT enrollments_ibfk_1 FOREIGN KEY (student_id) REFERENCES is the foreign key. This ensures that the student_id in the enrollments table must come from an existing id in the students table. Otherwise, the database will throw an error to prevent inserting invalid data.
If we try to insert a non-existent student_id, the database will reject it:
INSERT INTO enrollments (student_id, course) VALUES (3, '英语');
(1452, 'Cannot add or update a child row: a foreign key constraint fails (`foreignkey_example1`.`enrollments`, CONSTRAINT `enrollments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`))')Advantages of using foreign keys:
- The database helps maintain data integrity—no orphan data, and programming mistakes won’t insert invalid data.
- It supports cascading deletes, such as ON DELETE CASCADE. In the above example, if we delete id=2 from the students table, related data in enrollments will also be deleted.
- Clear business logic representation—the database schema itself shows the relationship, which is easier to maintain semantically. Some tools can visualize these relationships based on foreign keys, which is very helpful when getting into a new project.
Why DBAs dislike foreign keys?
In many large companies, foreign keys are disabled. Executing DDL like FOREIGN KEY (student_id) REFERENCES will fail. This means database tables appear unrelated in structure—each table stands alone, and student_id in enrollments is just an INT with no links to other tables.
Why disable such a good feature?
The main reason is maintainability. Foreign keys add constraints when modifying table structures and managing operations. They also complicate sharding. With independent tables, DBAs can manage the database more easily.
Foreign keys also slightly reduce performance because the database checks constraints on every update.
Furthermore, data integrity can be enforced at the application level. Cascade deletions can also be handled in business logic. Thus, using foreign keys essentially hands off part of business logic to the database, similar to stored procedures.
As a result, internet companies often don’t grant REFERENCES permission.
To revoke REFERENCES permission:
REVOKE REFERENCES ON testdb1_nofk.* FROM 'testuser1'@'localhost';After this, running Django migrations may cause permission errors:
django.db.utils.OperationalError: (1142, "REFERENCES command denied to user 'testuser1'@'localhost' for table 'testdb1_nofk.django_content_type'")How to disable foreign keys in Django migrations
Set db_constraint=False when declaring a ForeignKey in the model. This removes the foreign key constraint in the generated migration.
How to globally disable foreign keys in Django
Setting db_constraint=False for each ForeignKey is tedious. Also, Django includes internal tables (like user and migration info) that are hard to modify.
Django built-in tables:
auth_group
auth_group_permissions
auth_permission
auth_user
auth_user_groups
auth_user_user_permissions
django_admin_log
django_content_type
django_migrations
django_sessionA GitHub project shows that Django ORM has a feature set declaration. By modifying the MySQL engine to declare that the database doesn’t support foreign keys, Django ORM will skip adding FOREIGN KEY REFERENCES in DDL.
The core idea is to inherit Django’s MySQL engine and override one line: supports_foreign_keys = False.
Steps:
Create a mysql_engine directory in your Django project at the same level as your apps, so it can be imported.
├── project_dir
│ ├── ...
├── manage.py
├── app_dir
│ ├── ...
└── mysql_engine
├── base.py
└── features.pyYou need to write your own MySQL engine. Why not use django_psdb_engine? Because it inherits from Django’s native engine, it cannot use django_prometheus. Since ORM extensions use inheritance, you can’t inherit from two parents—unless you reimplement one feature in the base of another. A chained plugin system like CoreDNS would be better. Django’s middleware also works this way.
Engine files:
base.py:
from django_prometheus.db.backends.mysql.base import DatabaseWrapper as MysqlDatabaseWrapper
from .features import DatabaseFeatures
class DatabaseWrapper(MysqlDatabaseWrapper):
vendor = 'laixintao'
features_class = DatabaseFeaturesfeatures.py:
from django_prometheus.db.backends.mysql.base import (
DatabaseFeatures as MysqlBaseDatabaseFeatures,
)
class DatabaseFeatures(MysqlBaseDatabaseFeatures):
supports_foreign_keys = FalseIn settings.py, set the ENGINE to your custom engine mysql_engine:
DATABASES = {
"default": {
"ENGINE": "mysql_engine",
"NAME": "testdb1_nofk",
"USER": "testuser1",
'HOST': 'localhost',
'PORT': '',
'OPTIONS': {
'unix_socket': '/tmp/mysql.sock',
},
}
}Now it’s done.
Running python manage.py makemigrations is unaffected.
Running python manage.py migrate will no longer generate FOREIGN KEY REFERENCES.
Django migration works fine—even built-in tables won’t contain REFERENCES.
python3 corelink/manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, meta, sessions
Running migrations:
Applying contenttypes.0001_initial... OK
Applying auth.0001_initial... OK
Applying admin.0001_initial... OK
Applying admin.0002_logentry_remove_auto_add... OK
Applying admin.0003_logentry_add_action_flag_choices... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
Applying auth.0004_alter_user_username_opts... OK
Applying auth.0005_alter_user_last_login_null... OK
Applying auth.0006_require_contenttypes_0002... OK
Applying auth.0007_alter_validators_add_error_messages... OK
Applying auth.0008_alter_user_username_max_length... OK
Applying auth.0009_alter_user_last_name_max_length... OK
Applying auth.0010_alter_group_name_max_length... OK
Applying auth.0011_update_proxy_permissions... OK
Applying auth.0012_alter_user_first_name_max_length... OKTo verify a table creation:
show create table auth_user_groups \G
CREATE TABLE `auth_user_groups` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`group_id` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_user_groups_user_id_group_id_94350c0c_uniq` (`user_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciYou can confirm there is no REFERENCE.
References
- ChartDB tool: https://app.chartdb.io/
- Other tools: https://dbdiagram.io/
- db_constraint=False docs: https://docs.djangoproject.com/en/5.2/ref/models/fields/#django.db.models.ForeignKey.db_constraint
- https://github.com/planetscale/django_psdb_engine
- https://github.com/korfuri/django-prometheus
- https://www.kawabangga.com/posts/4728
Note: The post is authorized to republish and translated from https://www.kawabangga.com/posts/6980
