Inspired by Power Up Your Database with Firebird Server & LibreOffice Base - YouTube and the official Firebird language reference, I try to create a simple list with a text and an auto-incrementing integer. Until now, I need to use Base’s table editor. The following SQL successfully creates the table, the sequence and the trigger.
CREATE TABLE "TBL" (
"N" VARCHAR(32) NOT NULL,
"ID" INTEGER NOT NULL PRIMARY KEY
);
CREATE SEQUENCE "GID_TBL" START WITH 0 INCREMENT BY 1;
CREATE TRIGGER "SET_TBL_ID" FOR "TBL"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."ID" IS NULL) THEN
NEW."ID" = NEXT VALUE FOR "GID_TBL";
END
First problem: You have to execute the 3 statements one by one.
Second problem: This will not create a table where you can enter new records. In order to add a new list entry you have to issue the following statement:
INSERT INTO "TBL" SET VALUES ('First entry', NULL)
The trigger inserts a new ID value with ID = 1 (one above start value 0) (forgot PRIMARY KEY)
The resulting field does auot-increment but Base does not mark the field as <Auto Value> as it does when I used the table editor.
What are the statements to make an integer field auto-incrementing like Base’s table editor does?