2

I'm getting an error

Violation of PRIMARY or UNIQUE KEY constraint

when I try to run this insert statement (from a C# interface): 

INSERT INTO descript (PROPID, OBJID, NAME, CLASSID, LASTUPDATE,
                      HISTORY, VIVDRULE, CAPTION, PROMPT, DESCRIPTION)
VALUES (@PROPID, //110
        @OBJID,  //2109900055
        @NAME,  //Custom10
        @CLASSID,  //3
        @LASTUPDATE,  //4/29/2016
        @HISTORY,  // ''
        @VIVDRULE,  //1
        @CAPTION,  //test10
        @PROMPT,  //yo
        @DESCRIPTION  // ''
)

The specific error is:

Violation of PRIMARY or UNIQUE KEY constraint "DESCRIPTOR$OBJID" on table "DESCRIPT"*.

OBJID is the primary key for that table.

However, if I run 

select * 
from descript 
where objid = 2109900055

no rows are returned, so I know that there isn't actually a duplicate.

I've seen this, this, this, and this, but none of them seem to have an applicable solution, or at least, not one that works. I'm also using Firebird SQL, if that makes a difference.

Does anyone have any ideas?

Community
  • 1
  • 1
senschen
  • 794
  • 10
  • 27
  • Why insert into the primary key anyway? – Mihai Apr 29 '16 at 17:55
  • 1
    @Mihai I have to. This is part of a very large and complicated project/database, and none of our primary keys can auto-increment. So they have to be set in the insert. – senschen Apr 29 '16 at 17:58
  • Are multiple inserts happening by any chance? Also, datatype is integer? – I_am_Batman Apr 29 '16 at 18:09
  • @I_am_Batman No. Only the one insert. – senschen Apr 29 '16 at 18:11
  • What happens if you use `where objid+0 = 2109900055`. There are cases where an entry is in the table, but not in the index; using `+0` will ignore the index for the lookup. Another possibility is that you have a limbo transaction that also inserted this value. Limbo transactions are prepared, but not yet committed. – Mark Rotteveel Apr 30 '16 at 10:48
  • @MarkRotteveel using `objid+0 = 2109900055` makes no difference-- no rows are returned. It does not seem to be a limbo transaction. – senschen May 02 '16 at 12:13
  • Have you tried creating a backup and restoring the database? – Mark Rotteveel May 02 '16 at 14:59
  • @MarkRotteveel No, but that's my next step. Another similar question said that their issues were due to corruption, so... – senschen May 02 '16 at 15:01

1 Answers1

0

Check the data type for OBJID and its limitations. I suspect with that large of a number you are exceeding the max size of the data type.

Atoadaso
  • 143
  • 4