On 2017-12-20 19:38, 'Zoran' zoran...@gmail.com [firebird-support] wrote: > Hello Helen > > I understand that we shouldn't mess up with IDENTITY column, as you > said. I have the same problem as original poster and I'm using this > code > > SELECT G.RDB$GENERATOR_NAME > > FROM RDB$RELATION_FIELDS AS G > > WHERE G.RDB$RELATION_NAME = 'CUSTOMER' > > AND G.RDB$FIELD_NAME = (SELECT F.RDB$FIELD_NAME > > FROM RDB$INDEX_SEGMENTS AS F > > LEFT JOIN RDB$RELATION_CONS TRAINTS AS RC ON > (RC.RDB$INDEX_NAME = F.RDB$INDEX_NAME) > > WHERE RC.RDB$RELATION_NAME = 'CUSTOMER' > > AND RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') > > to obtain IDENTITY sequencer name. Then I increase it to get the next > IDENTITY for master table, which I then populate into master-key > columns of the detail table. > > Is this approach 'legit'? Will I run into problems in the future > (assuming that RDB$ table structures don't change)?
No, that approach is not legit. If you are using an identity column, then you should not mess with its underlying sequence, that is an implementation detail, and therefore something you shouldn't rely on as it might change from one version to the next (eg if Firebird developers decide to implement identity columns differently, or make those 'behind the scenes' sequences inaccessible, etc). If you want to do something like that, then just create an real sequence, and use the 'classic' trigger approach. Given you are talking about master-detail, you should insert the master record first anyway (or at least, I hope you are using a foreign key constraint), so use INSERT .. RETURNING <id-column-name> to insert the master record and obtain the generated value of the id column, then use that value to insert your detail records. Mark
