The following sequence of SQL commands on a fresh FirebirdSQL file fails: CREATE TABLE "Lehrgangsliste"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY,"Tarifgruppe" INTEGER NOT NULL,"Pruefungsgruppe" INTEGER,"MitgliedsID" VARCHAR(10),"DatumsID" DATE NOT NULL,"Vorname" VARCHAR(50) NOT NULL,"Nachname" VARCHAR(50) NOT NULL,"Verein" INTEGER NOT NULL,"Grad" INTEGER) INSERT INTO "Lehrgangsliste" VALUES ( NULL, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 ) It returns the following error: 1: firebird_sdbc error: *validation error for column "Lehrgangsliste"."ID", value "*** null ***" caused by 'INSERT INTO "Lehrgangsliste" VALUES ( NULL, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )' This makes it impossible to insert rows into a table while having the ID column being auto generated. I consider this a very common use case. See also bug #104942 for other migration issues to FirebirdSQL.
This couldn't be a bug. It isn't allowed to insert NULL into a field for a primary key. Identity columns are implicitly NOT NULL (non-nullable). See: http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#rnfb30-ddl-identity You have to define a value for the field "ID" (which must be a unique value) or you have to write down a list of all fields in the INSERT-command.
Inserting NULL does not set the value to NULL in this case. Inserting NULL into an auto-value field makes the value increment automatically. At least it is like this with HSQLDB, and I think other database systems as well.
(In reply to Gerhard Schaber from comment #2) > Inserting NULL does not set the value to NULL in this case. Inserting NULL > into an auto-value field makes the value increment automatically. At least > it is like this with HSQLDB, and I think other database systems as well. HSQLDB supports this, MariaDB also - but are you shure Firebird will support it also? http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#rnfb30-ddl-identity : "An identity column is a column associated with an internal sequence generator. Its value is set automatically when the column is omitted in an INSERT statement." You haven't omitted "ID". You have send NULL with your command - and this isn't allowed. So an error of Firebird appears. This couldn't be our bug but an ask for an enhancement of Firebird. Could be my English isn't well enough for the release-notes of Firebird 3.0. So let us wait for other statements here.
Thanks. I tried that as well. Omitting the value would also work for me, but this also does not work. At least not with LibreOffice. Maybe I am doing something wrong.
INSERT INTO "Lehrgangsliste" VALUES ( 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 ) 1: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -804 *Count of read-write columns does not equal count of values caused by 'isc_dsql_prepare' INSERT INTO "Lehrgangsliste" VALUES (, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 ) 2: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -104 *Token unknown - line 1, column 38 *, caused by 'isc_dsql_prepare'
The following actually works, but causes problems, since in some generic code, I need to be aware of all column names: INSERT INTO "Lehrgangsliste" ("Tarifgruppe", "Pruefungsgruppe", "MitgliedsID", "DatumsID", "Vorname", "Nachname", "Verein", "Grad") VALUES (3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 ) But well, that apparently means I have to change more code.
(In reply to Gerhard Schaber from comment #5) > INSERT INTO "Lehrgangsliste" VALUES ( 3, NULL, 'YW045', '2016-10-15', 'First > name', 'Last name', 701, 12 ) This couldn't work, because you have to define the columns, if you won't add a value to every column. INSERT INTO "Lehrgangsliste" VALUES ( 15, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 ) will work, if "ID" = 15 doesn't exist.
I tried alternatively : ('',...) ("",...) ( ,...) and simply starting with a missing entry for the ID column. In each case, I got an error and the statement refused to complete. Confirming Tested on Version: 5.4.0.0.alpha0+ Build ID: b66b1e896c177df3c5de5d33037416152fc8a381 CPU Threads: 2; OS Version: Mac OS X 10.12.2; UI Render: default; Locale: fr-FR (fr_FR.UTF-8); Calc: group
I checked some Firebird docs and forums. I could not find any hint about whether this will be supported or not.
(In reply to Gerhard Schaber from comment #6) > The following actually works, but causes problems, since in some generic > code, I need to be aware of all column names: > INSERT INTO "Lehrgangsliste" ("Tarifgruppe", "Pruefungsgruppe", > "MitgliedsID", "DatumsID", "Vorname", "Nachname", "Verein", "Grad") VALUES > (3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 ) > > But well, that apparently means I have to change more code. Well, I'm afraid that explicitly inserting NULL and getting the AutoValue instead is (I guess) not part of the SQL standard. HSQLDB and MariaDB (and others) support it as an extension, but e.g. Firebird (you tell me) and PostgreSQL (I just tested) do not. For complete portability, you just have to do as you outline above. You may if you wish file a wishlist bug / enhancement request directly at the Firebird project; if they decide to implement this extension/feature, we will get it when we upgrade to whatever version has it.
This describes an alternative: http://www.firebirdfaq.org/faq29/ Anyway, I have created a ticket for Firebird in order to get this behavior by default for a not NULL identity column: http://tracker.firebirdsql.org/browse/CORE-5460