#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    24
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    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








    Originally Posted by jwcane
    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.

IMN logo majestic logo threadwatch logo seochat tools logo