0

I am trying to map a table with an identity key field. When I try to save I get the error SQL0803 Duplicate key value specified

INSERT INTO libpjk/Audit (AuditId, AuditDate, UserId, Keys, ValBefore, ValAfter, FieldId) VALUES (default, ?, ?, ?, ?, ?, ?)

I'm thinking the AuditId should not appear in the field list and the value of default should not be there either. I just don't know how to do this.

SQL for the table creation:

CREATE TABLE libpjk.Audit (
AuditId integer not null GENERATED ALWAYS AS IDENTITY 
   (START WITH 1, INCREMENT BY 1),
AuditDate timestamp not null,
UserId char(10)    not null,
FieldId integer    not null,
Keys varchar(50)   not null,
ValBefore varchar(50),
ValAfter  varchar(50),
CONSTRAINT libpjk.pk_Audit PRIMARY KEY(FieldId))

Here's how the AuditId is defined in my Audit class:

<Required()> Public Overridable Property AuditId As Integer

here's my mappings:

MyBase.New()
Table("libpjk/Audit")
LazyLoad()
Id(Function(x) x.AuditId).Column("AuditId").GeneratedBy.Identity()
References(Function(x) x.AuditField).Column("FieldId")
Map(Function(x) x.Timestamp).Column("AuditDate").Not.Nullable()
Map(Function(x) x.UserId).Column("UserId").Not.Nullable()
Map(Function(x) x.Keys).Column("Keys").Not.Nullable()
Map(Function(x) x.Value_Before).Column("ValBefore")
Map(Function(x) x.Value_After).Column("ValAfter")

Thanks for your help

D. Kermott
  • 1,613
  • 17
  • 24
  • I figured out my first problem -- in the table creation I created the primary key on the wrong field (FieldId) instead of (AuditId). Now that I've corrected that the Identity is working but I still get an error **SQL5016 Qualified object name SYSDUMMY1 not valid** and FNH give the error **could not retrieve generated id after insert** – D. Kermott Apr 01 '13 at 17:14
  • 1
    Well... I haven't gotten this to work. Unless someone has a better idea I'm using a guid for a pk. – D. Kermott Apr 01 '13 at 21:53

0 Answers0