Insert Query Fails

Running the query in IB_SQL succeeds, but perhaps too well:

insert into contact
(kCont,fkres,emFirst)
Values(-1,26,'Julie')

Primary key value setting is taken literally, when in fact what is desired is to generate the next key value, 1 in this case.

Hosting package developer gave me the default value for PK of -1, which is intended to activate the generator to provide the next available PK value.

From creation script for this table:

CREATE GENERATOR GENCONTACTNO;
SET GENERATOR GENCONTACTNO TO 0;

CREATE TABLE CONTACT (
KCONT INTEGER NOT NULL, /*etc.*/

UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'pk="autoincrement" label="##" type="hidden"' WHERE (RDB$RELATION_NAME = 'CONTACT') AND (RDB$FIELD_NAME = 'KCONT');

ALTER TABLE CONTACT ADD CONSTRAINT RESIDENTS_CONTACT
FOREIGN KEY (FKRES) REFERENCES RESIDENTS (KRES);

=============

Running the query in Delphi generates following error

Violation of Primary or Unique key constraint "INTEG_17" on table "CONTACT".

Don't know how to interpret that. Does INTEG_17 refer to the PK or FK contraint?

Comments appreciated.

Well, i think there is a trigger missing which activates the generator on input of kCont=-1 value. We usually use NULL but -1 is ok too....your trigger should be like this:

CREATE OR ALTER TRIGGER contact_bi FOR contact
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.kCont=-1) THEN
NEW.kCont = GEN_ID(GENCONTACTNO, 1);
END


this will increase the generator and give you the next available value.
if you have records in your table then take care to set a proper generator value (the biggest kCont value) at beginning