Django中基于外键设置关联模型主键范围的可行性与实现方法

First, let's break down whether this approach makes sense, then dive into how to implement it.

Should You Use This Scheme? #

This approach is workable but comes with critical tradeoffs:

Pros #

• Instant author identification: You can immediately tell which author a post belongs to just by looking at the PK (e.g., 34000001 maps to author 34).
• Avoid extra queries: If you only have a post ID, you can calculate the author ID with simple math (post_id // 1000000) instead of joining the Author table.
• Ample capacity: With 6 digits for the sequence, each author can have up to 999,999 posts—way more than most apps will ever need.

Cons #

• Immutable PK constraint: You can't reassign a post to a different author without changing its PK, which is a big no-no. PKs should be permanent identifiers; altering them can break relationships, indexes, and cached data.
• Concurrency complexity: You need to handle race conditions when creating posts for the same author (to avoid duplicate sequence numbers).
• Non-sequential PKs: Unlike auto-incrementing IDs, these PKs aren't sequential, which might slightly reduce database index efficiency (though this is negligible for most use cases).

Verdict: Use this scheme only if you're 100% sure posts will never change authors. If post reassignment is even a remote possibility, stick with the default auto-increment PK.

Implementation (Django Example) #

Assuming you're using Django (your model syntax matches Django's), here's how to build this:

Step 1: Define the Models #

Override the Post model's PK to use a BigIntegerField (since the maximum possible ID is 100999999, which fits in a big integer):

from django.db import models
from django.db import transaction

class Author(models.Model):
    name = models.CharField(max_length=100)

class Post(models.Model):
    # Override default auto-increment PK
    id = models.BigIntegerField(primary_key=True)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    title = models.CharField(max_length=200)

Step 2: Override the Save Method #

Add logic to generate the PK when creating a new post, with concurrency protection:

class Post(models.Model):
    id = models.BigIntegerField(primary_key=True)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    title = models.CharField(max_length=200)

    def save(self, *args, **kwargs):
        if not self.id:  # Only generate PK for new posts
            with transaction.atomic():
                # Lock existing posts for this author to prevent race conditions
                existing_posts = Post.objects.filter(author=self.author).select_for_update()
                latest_post = existing_posts.order_by('-id').first()

                if latest_post:
                    # Extract sequence from the last 6 digits of the PK
                    sequence = (latest_post.id % 1000000) + 1
                    # Prevent exceeding the 999,999 post limit per author
                    if sequence > 999999:
                        raise ValueError("This author has reached the maximum number of posts (999,999)")
                else:
                    # First post for this author starts at sequence 1
                    sequence = 1

                # Calculate the new PK: author_id * 1,000,000 + sequence
                self.id = self.author.id * 1000000 + sequence

        super().save(*args, **kwargs)

Key Details: #

• Concurrency protection: transaction.atomic() and select_for_update() lock the author's existing posts, ensuring only one request can generate a new sequence at a time.
• Math-based sequence extraction: Using modulo (% 1000000) avoids error-prone string manipulation and works for author IDs of any length (1-3 digits in your case).
• Limit check: We prevent creating more than 999,999 posts per author to avoid overflowing the sequence digits.

内容的提问来源于stack exchange，提问作者Amin Ba