At 02:20 AM 22/02/2009, you wrote:

>Summary: In order to add a record into a child table (with a foreign key constrain), Must I specify the child's primary key even though I have a before insert trigger/generator?
>
>If I don't include the child's primary key within the insert statement, an error occurs.
>
>
>
>Here are my tables, a parent and a child.
>
>Table: ARTICLE
>PRIMARYKEY (primary key constraint)
>TITLE
>
>Table: COMMENTS
>PRIMARYKEY (primary key constraint)
>ARTICLE_FK (foreign key constraint to parent's primary key)
>USER_ALIAS
>COMMENT
>
>I am able to add child records when I specify its subsiquent primary key ID like this:
>INSERT INTO COMMENTS (PRIMARYKEY, ARTICLES_FK, USER_ALIAS, COMMENT) VALUES (36, 1, 'TED', 'HELLO')
>
>I am unable to add child records if I don't specify the primary key ID:
>INSERT INTO COMMENTS (ARTICLES_FK, USER_ALIAS, COMMENT) VALUES (1, 'TED', 'HELLO')
>
>Error Message:
>
>Invalid insert or update value(s): object columns are
>constrained - no 2 table rows can have duplicate column values.
>Violation of PRIMARY or UNIQUE KEY constraint "INTEG_6" on table "COMMENTS".
>
>
>Here is my child's generator:
>CREATE GENERATOR COMMENTS_PRIMARYKEY_GEN;
>
>Here is my child's trigger:
>SET TERM ^ ;
>
>CREATE TRIGGER BI_COMMENTS_PRIMARYKEY FOR COMMENTS
>ACTIVE BEFORE INSERT
>POSITION 1
>AS
>BEGIN
> IF (NEW.PRIMARYKEY IS NULL) THEN
> NEW.PRIMARYKEY = GEN_ID(COMMENTS_PRIMARYKEY_GEN, 1);
>END^
>
>SET TERM ; ^
>
>
>Although I have no problem in specifying the child's next primary key, I would really like to know why the child's trigger/generator is not working in this case.

The trigger's working fine! :-) The error you got here was a primary key/uniqueness violation, not a referential integrity violation. The first record went in with a manually-determined PK value of 36. But your generator was sitting there on 35 - with the next value to be generated as 36. So, when the trigger fired, you got a PK violation.

Perhaps you thought that poking that value in manually would somehow cause the generator to synchronise itself with the table by some kind of internal magic. It doesn't happen. In applications, if you're generating keys you must make them unreachable by humans.

./heLen