MariaDB主键ID配置问题：如何为两类医生分配指定范围ID并自动生成

Hey there! Let's work through this ID range restriction and auto-generation problem for your medical center database project. I’ve dealt with similar schema constraints before, so here’s how to tackle it step by step.

First, you’ll want to lock down the ID ranges for general practitioners (GPs) and specialists using CHECK constraints—this ensures no invalid IDs slip into your tables. Plus, since both tables reference the doctorID table, we’ll tie everything together with foreign keys.

Here’s the SQL to set up your tables (adjust syntax slightly if you’re using MySQL vs. PostgreSQL vs. SQL Server, but the core logic stays the same):

-- Create the base doctorID table (only holds the doctor ID as primary key)
CREATE TABLE doctorID (
    doctorID INT PRIMARY KEY
);

-- General Practitioners table with 0-100 ID range constraint
CREATE TABLE general_practitioners (
    ID INT PRIMARY KEY REFERENCES doctorID(doctorID),
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    -- Ensure ID stays between 0 and 100
    CONSTRAINT chk_gp_id_range CHECK (ID BETWEEN 0 AND 100)
);

-- Doctors-Specialists table with 101-200 ID range constraint
CREATE TABLE doctors_specialists (
    ID INT PRIMARY KEY REFERENCES doctorID(doctorID),
    name VARCHAR(100) NOT NULL,
    speciality VARCHAR(100) NOT NULL,
    -- Ensure ID stays between 101 and 200
    CONSTRAINT chk_specialist_id_range CHECK (ID BETWEEN 101 AND 200)
);

With these constraints, any attempt to insert a GP ID outside 0-100 or a specialist ID outside 101-200 will throw an error—perfect for keeping your data consistent.

Next, let’s handle auto-generating IDs for new specialists so you don’t have to manually assign them. The approach varies a bit by database system, so here are the most common solutions:

Option A: Sequences (PostgreSQL, SQL Server) #

Sequences are the cleanest way here—they let you define a starting value, step size, and max value directly.

PostgreSQL Example: #

-- Create a sequence that starts at 101, increments by 1, stops at 200
CREATE SEQUENCE specialist_id_seq
    START WITH 101
    INCREMENT BY 1
    MAXVALUE 200
    NO CYCLE; -- Don't loop back to 101 once we hit 200

-- Set the specialist table's ID column to use this sequence by default
ALTER TABLE doctors_specialists
ALTER COLUMN ID SET DEFAULT nextval('specialist_id_seq');

Now, when you insert a new specialist without specifying an ID, it’ll automatically grab the next available number in the 101-200 range. If you hit 200, the next insert will fail (which is exactly what you want—no overflows).

SQL Server Example: #

-- Create a sequence for specialists
CREATE SEQUENCE specialist_id_seq
    AS INT
    START WITH 101
    INCREMENT BY 1
    MAXVALUE 200
    NO CYCLE;

-- Set default value for the ID column
ALTER TABLE doctors_specialists
ALTER COLUMN ID SET DEFAULT NEXT VALUE FOR specialist_id_seq;

Option B: Auto-Increment + Trigger (MySQL) #

MySQL doesn’t support sequences natively, but we can use auto-increment plus a trigger to enforce the upper limit:

-- Create the specialists table with auto-increment starting at 101
CREATE TABLE doctors_specialists (
    ID INT AUTO_INCREMENT PRIMARY KEY REFERENCES doctorID(doctorID),
    name VARCHAR(100) NOT NULL,
    speciality VARCHAR(100) NOT NULL,
    CONSTRAINT chk_specialist_id_range CHECK (ID BETWEEN 101 AND 200)
);

-- Set the auto-increment starting point to 101
ALTER TABLE doctors_specialists AUTO_INCREMENT = 101;

-- Add a trigger to block inserts if ID exceeds 200
DELIMITER //
CREATE TRIGGER trg_block_invalid_specialist_id
BEFORE INSERT ON doctors_specialists
FOR EACH ROW
BEGIN
    IF NEW.ID > 200 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Specialist ID cannot exceed 200—max allowed is 200';
    END IF;
END //
DELIMITER ;

Since both general_practitioners and doctors_specialists reference doctorID, you’ll need to ensure every ID in those tables exists in doctorID. To avoid manual inserts, add a trigger that automatically adds the ID to doctorID when you insert a new GP or specialist.

Here’s a PostgreSQL example (adjust for your DB as needed):

-- Function to insert into doctorID when a new doctor is added
CREATE OR REPLACE FUNCTION insert_doctor_id()
RETURNS TRIGGER AS $$
BEGIN
    -- Insert the ID into doctorID, do nothing if it already exists
    INSERT INTO doctorID(doctorID) VALUES (NEW.ID) ON CONFLICT DO NOTHING;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to GPs table
CREATE TRIGGER trg_gp_add_doctor_id
BEFORE INSERT ON general_practitioners
FOR EACH ROW EXECUTE FUNCTION insert_doctor_id();

-- Attach trigger to specialists table
CREATE TRIGGER trg_specialist_add_doctor_id
BEFORE INSERT ON doctors_specialists
FOR EACH ROW EXECUTE FUNCTION insert_doctor_id();

This way, you never have to touch the doctorID table directly—it’s maintained automatically.

That should cover all your requirements: enforced ID ranges, auto-generated specialist IDs, and proper foreign key references to the doctorID table.

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