Hi Jeff, thanks for specifying a test case very clearly!

When I create your tables using Database Workbench 2.8 on Firebird 2.5
Alpha 1, I have no problem inserting the record as you describe.

What do you use to insert the record and which Firebird version are you
using (I don't know whether the latter question matters)? I know that if
you are using IBO and TIB_Cursors/TIB_Queries, then you have to either
set GeneratorLinks or specify that the field is not required (and if you
choose to set the field to not required, then you probably loose the
connection between your dataset and what is stored in the database
because your client has no knowledge of what value the server assigns as
the primary key, making it impossible to update the newly inserted
record - this is at least true for older Firebird versions, I think I've
heard that newer versions may have ways to return values from an insert
statement).

I'd be surprised if e.g. isql would object when you issue your statement
(if your trigger is active).

If your question is specific to some kind of tool (i.e. if isql
succeeds), then the question may be more appropriate for another list.

HTH,
Set

Jeff Dunlap 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.