CREATE TABLE MYTABLE (
COLUMN1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
COLUMN2 VARCHAR(20));
ALTER TABLE MYTABLE ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (COLUMN1);
Then, is I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN1, COLUMN2) VALUES (NULL, 'TEST')
The message is:
Validation error for column "MYTABLE"."COLUMN1", value "*** null ***".
And if I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN2) VALUES ('TEST')
The message is:
UPDATE OR INSERT field list does not match primary key of table MYTABLE.
There is some solution? Or it is impossible to use an IDENTITY column with
an UPDATE OR INSERT?
Greetings.
Walter.
On Thu, Nov 19, 2020 at 8:55 PM Walter R. Ojeda Valiente <
[email protected]> wrote:
> I forget to say that my IDENTITY column is the Primary Key of MyTable,
> therefore if I don't write it an error happens because...the table needs a
> Primary Key.
>
> On Thu, Nov 19, 2020 at 8:52 PM Walter R. Ojeda Valiente <
> [email protected]> wrote:
>
>> Hello everybody
>>
>> A long, long time without writing neither reading this group, mostly
>> because all that I need about Firebird I knew.
>>
>> But now, I have a doubt.
>>
>> With Firebird 2.5.x I can have an auto-incremental column and use it in
>> an UPDATE OR INSERT, but such thing is not possible with Firebird 3.
>>
>> With Firebird 2.5.x a generator and a trigger are created and the value
>> of the column is put automatically. So, if I write:
>> UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2) VALUES (NULL, 12345);
>>
>> and MyColumn1 is auto-incremental its value is set for the Firebird
>> engine.
>>
>> but...if I use the new IDENTITY type in Firebird 3 I always have an
>> error, writing MyColumn1 in the UPDATE OR INSERT or not writing.
>>
>> So, my question is:
>>
>> Is it possible to use UPDATE OR INSERT with an IDENTITY column?
>>
>> Thanks in advance.
>>
>> Greetings.
>>
>> Walter.
>>
>
