Firebird 2.5向TABLE1插入多行时主键约束冲突问题求助

Let's work through this confusing issue step by step—you're inserting a row with ID=91, but Firebird is throwing a duplicate key error for ID=95, even though you don't see that ID in TABLE1. Here are the most likely fixes:

1. Verify Which Table the Constraint Belongs To #

First, note the error mentions constraint "PK_TABLE1" on table "FIRMY"—but you're inserting into TABLE1. This could be a typo in the constraint name, or FIRMY might be an alias for TABLE1. Run this query to confirm the constraint's target table:

SELECT 
  RDB$RELATION_NAME AS TABLE_NAME,
  RDB$CONSTRAINT_TYPE AS CONSTRAINT_TYPE
FROM RDB$RELATION_CONSTRAINTS 
WHERE RDB$CONSTRAINT_NAME = 'PK_TABLE1';

If it points to FIRMY instead of TABLE1, you might be accidentally inserting into the wrong table elsewhere in your script.

2. Check for Uncommitted or Hidden Rows #

Firebird's transaction isolation rules can hide rows that are in uncommitted transactions. Even if you think ID=95 doesn't exist, another open transaction could have inserted it but not committed yet.

• First, commit all your open transactions with:

  COMMIT;
  

• Then run this to check for ID=95:

  SELECT ID FROM TABLE1 WHERE ID = 95;
  

• If you still don't see it, use a read-committed with record versioning transaction to view all committed rows:

  SET TRANSACTION READ COMMITTED RECORD_VERSION;
  SELECT ID FROM TABLE1 WHERE ID = 95;
  

3. Audit Your Full Insert Script #

The error references ID=95, but the single INSERT you shared uses ID=91. It’s possible your full batch script includes another INSERT trying to add ID=95—double-check all rows in your script for duplicate IDs or values that clash with existing data.

4. Fix Sequence/Generator Misalignment (If Using Auto-Generated IDs) #

If your ID column uses a Firebird generator (sequence) for auto-values, the generator’s current value might be out of sync with your table’s max ID. For example, if the generator is set to 95 but your table’s max ID is 94, the next auto-generated ID would conflict.

• Check your generator’s current value (replace GEN_TABLE1_ID with your actual generator name):

  SELECT GEN_ID(GEN_TABLE1_ID, 0) AS CURRENT_GENERATOR_VALUE FROM RDB$DATABASE;
  

• If the value is ≤ 94, reset it to be higher than your table’s max ID:

  SELECT GEN_ID(GEN_TABLE1_ID, (SELECT MAX(ID) FROM TABLE1) - GEN_ID(GEN_TABLE1_ID, 0) + 1) FROM RDB$DATABASE;
  

  This will bump the generator to one value higher than your table’s current maximum ID.

5. Test the Insert in Isolation #

Try running just the single INSERT statement you shared (ID=91) in a fresh transaction. If it succeeds, the problem is definitely in another part of your script or an uncommitted transaction. If it still fails, double-check that you’re connecting to the correct database instance—sometimes we accidentally target a staging or backup DB instead of the intended one.

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