如何设置IntegerField自增且不可编辑？多用户文档序号独立自增问题

Hey Danilo, sounds like you ran into a classic pitfall with global auto-increment fields—they work great for unique IDs across the whole system, but they don't play nice when you need per-user sequential numbering. Let's fix this with a system that keeps each user's document sequence independent, conflict-free, and locked down from edits.

Core Idea #

Instead of a single global counter, we need user-specific counters that track the last used document number for each individual user. This way, User A's #1, #2, #3 don't collide with User B's #1, #2, #3.

1. Database Setup (The Most Reliable Approach) #

First, create a dedicated counter table to track each user's progress, then add safeguards to enforce uniqueness and prevent edits.

Step 1: Create a User Counter Table

This table will store the last document number each user has used:

CREATE TABLE user_document_counters (
    user_id INT PRIMARY KEY,
    last_used_number INT DEFAULT 0,
    -- Link to your existing users table
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Step 2: Atomic Counter Update (Avoid Race Conditions)

When a user creates a new document, use an atomic database operation to increment their counter—this ensures no two concurrent requests grab the same number:

-- Initialize or increment the counter in one step
INSERT INTO user_document_counters (user_id, last_used_number)
VALUES (123, 1) -- Replace 123 with the current user's ID
ON DUPLICATE KEY UPDATE last_used_number = last_used_number + 1;

-- Grab the newly incremented number
SELECT last_used_number FROM user_document_counters WHERE user_id = 123;

Step 3: Enforce Per-User Uniqueness

Add a unique composite index on your documents table to guarantee no duplicate numbers for the same user:

CREATE UNIQUE INDEX idx_doc_user_number ON documents(user_id, number);

Step 4: Lock the number Field from Edits

To make the field uneditable, use a database trigger to block any updates to the number column:

-- MySQL Example
DELIMITER //
CREATE TRIGGER prevent_document_number_edit
BEFORE UPDATE ON documents
FOR EACH ROW
BEGIN
    IF OLD.number != NEW.number THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Document numbers cannot be modified';
    END IF;
END //
DELIMITER ;

2. Application Layer Checks (Extra Safeguards) #

Even with database-level protections, add checks in your app to reinforce the rules:

• When rendering document forms, set the number field as read-only or hidden (don't let users input it at all).
• In your backend logic, ignore any incoming number values from create/update requests—always pull the value from the user's counter table.

Alternative: Per-User Sequences (For PostgreSQL Users) #

If you're using PostgreSQL, you can create a dedicated sequence for each user when they register. This works well but requires managing more database objects:

-- When a user registers (replace 123 with the new user's ID)
CREATE SEQUENCE doc_seq_user_123 START WITH 1 INCREMENT BY 1;

-- When creating a document, fetch the next number
SELECT nextval('doc_seq_user_123') AS new_document_number;

Key Notes #

• Atomic Operations Are Critical: Never use a "read-then-increment" pattern without wrapping it in an atomic database operation—this will cause race conditions where two users end up with the same number.
• Test Concurrent Scenarios: Simulate multiple users creating documents at the same time to ensure your counter system holds up.

内容的提问来源于stack exchange，提问作者Danilo Rodrigues