violation of PRIMARY or UNIQUE KEY constraint "UNQ1" on table "Table1"

[inageib]

Hello,
I get this error when I try to insert new record although I can do the same without errors inside my admin tool.

violation of PRIMARY or UNIQUE KEY constraint "UNQ1" on table "Table1"

here is the SQL

Code: Select all

CREATE TABLE Table1 (
    MN_ID               INTEGER NOT NULL,
    POS                SMALLINT NOT NULL

);

ALTER TABLE Table1 ADD CONSTRAINT UNQ1 UNIQUE (MN_ID, POS);

/* Trigger */
CREATE OR ALTER TRIGGER Table1_BI0 FOR Table1
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  select max(POS)+1 from table1 where Table1.MN_id = new.mn_id into new.pos;
end

I enter the "MN_ID" value but for "POS" I leave the trigger "Table1_BI0" to do that. The Insert is working inside my admin tool but in my app I get the above error, please advise

Thanks

[inageib]

any response ?

[ZEuS]

Unfortunately, we can not reproduce the "violation of PRIMARY or UNIQUE KEY constraint" error you have reported.
Please create a small sample that demonstrates the problem, and send it to eugeniyz*devart*com.

[inageib]

This is very strange, I tried again and it work without error. What can cause that behavior !!?

[ZEuS]

We can not give a definite answer to such a general question. The problem may be caused by different reasons, and is specific to the code. If you can reproduce the problem and provide us with a test project - we will try to help.