December 22nd, 2012, 11:19 AM
Insert Query Fails
Running the query in IB_SQL succeeds, but perhaps too well:
insert into contact
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?
February 13th, 2013, 02:03 AM
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
IF (NEW.kCont=-1) THEN
NEW.kCont = GEN_ID(GENCONTACTNO, 1);
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
Originally Posted by jwcane