SQL Server中如何基于主键递增整数？实现剧院tID按影院唯一递增

Hey there! Let's break down your two SQL Server questions step by step— I’ve got you covered.

There are a few common, reliable ways to handle auto-incrementing integer primary keys in SQL Server, depending on your needs:

• Use the IDENTITY property (most common)
  This is the go-to method for auto-incrementing integer primary keys. When defining your table, mark the primary key column as IDENTITY(seed, increment), where seed is the starting value and increment is the amount to add each time. SQL Server handles the rest automatically—you don’t need to specify the column value during inserts.

  CREATE TABLE cinema (
      mID INT IDENTITY(1,1) PRIMARY KEY, -- Starts at 1, increments by 1
      -- Other columns here
  );
  

• Use a SEQUENCE object (for more flexibility)
  If you need shared auto-increment values across multiple tables, or want control over things like cycling values or custom increments, create a sequence object and use it as a default value for your primary key:

  -- Create the sequence
  CREATE SEQUENCE seq_pk_start
      START WITH 1
      INCREMENT BY 1;
  
  -- Use it in your table
  CREATE TABLE example_table (
      id INT PRIMARY KEY DEFAULT NEXT VALUE FOR seq_pk_start,
      -- Other columns here
  );
  

• Manual increment (not recommended for most cases)
  You can calculate the next value using MAX(id) + 1, but this is prone to concurrency issues (duplicate keys if multiple inserts happen at the same time). If you must use it, wrap the logic in a transaction with locking hints:

  BEGIN TRANSACTION;
  DECLARE @next_id INT = (SELECT ISNULL(MAX(mID), 0) + 1 FROM cinema WITH (UPDLOCK, HOLDLOCK));
  INSERT INTO cinema (/* other columns */) VALUES (/* column values */);
  COMMIT TRANSACTION;
  

First, I notice a small typo in your theater table creation script: your cinema table’s primary key is mID, but you’re referencing cinema(cID) in the foreign key, and setting the primary key as (tID,cID). I’ll assume this is a mistake, and you meant mID instead of cID everywhere— I’ll base solutions on that correction.

The problem here is that SQL Server’s IDENTITY property is global to the table, not partitioned by another column (like mID). To get a tID that resets to 1 for each new cinema, here are two solid approaches:

Option 1: Use an INSTEAD OF INSERT trigger #

Triggers let you override the default insert behavior and calculate the correct tID per cinema automatically.

First, adjust the theater table to remove the IDENTITY property from tID (since we’ll manage it manually):

CREATE TABLE theater (
    tID INT,
    mID INT FOREIGN KEY REFERENCES cinema(mID),
    PRIMARY KEY (tID, mID) -- Corrected composite primary key
);

Then create the trigger to handle tID calculation:

CREATE TRIGGER trg_theater_auto_tID
ON theater
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Handle single or multiple inserts, even for the same cinema
    INSERT INTO theater (tID, mID)
    SELECT
        -- Get the highest existing tID for the cinema, add row number to handle bulk inserts
        ISNULL(theater_max.max_tID, 0) + ROW_NUMBER() OVER (PARTITION BY inserted.mID ORDER BY (SELECT NULL)),
        inserted.mID
    FROM inserted
    LEFT JOIN (
        SELECT mID, MAX(tID) AS max_tID
        FROM theater
        GROUP BY mID
    ) theater_max ON inserted.mID = theater_max.mID;
END;

Now when you insert into theater, the trigger will automatically assign the next sequential tID for each cinema.

Option 2: Calculate tID during insertion #

If you prefer not to use triggers, you can compute the tID directly in your insert statement.

For single inserts:

DECLARE @target_mID INT = 1; -- Replace with your cinema's mID
INSERT INTO theater (tID, mID)
VALUES (
    -- Get the highest tID for the cinema, or start at 1 if none exist
    (SELECT ISNULL(MAX(tID), 0) + 1 FROM theater WITH (UPDLOCK, HOLDLOCK) WHERE mID = @target_mID),
    @target_mID
);

The WITH (UPDLOCK, HOLDLOCK) hints prevent concurrency issues by locking the relevant rows during the calculation.

For bulk inserts (e.g., from a temp table):

WITH bulk_data AS (
    -- Replace this with your source data
    SELECT mID FROM #temp_theater_data
)
INSERT INTO theater (tID, mID)
SELECT
    ISNULL(theater_max.max_tID, 0) + ROW_NUMBER() OVER (PARTITION BY bulk_data.mID ORDER BY (SELECT NULL)),
    bulk_data.mID
FROM bulk_data
LEFT JOIN (
    SELECT mID, MAX(tID) AS max_tID
    FROM theater
    GROUP BY mID
) theater_max ON bulk_data.mID = theater_max.mID;

Key Note on Concurrency #

Whichever method you choose, always use transaction locking hints (like UPDLOCK and HOLDLOCK) when calculating the next tID to avoid duplicate values if multiple sessions insert into the same cinema at the same time.

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