Firebird autoIncrement issue

Question

I've created Customers Table through following code :

CREATE TABLE CUSTOMERS (
ID INTEGER DEFAULT 1 NOT NULL,
"NAME" VARCHAR(30) CHARACTER SET UTF8 COLLATE UTF8,
"LASTNAME" VARCHAR(30) CHARACTER SET UTF8 COLLATE UTF8);


ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID);


SET TERM ^ ;

CREATE TRIGGER BI_CUSTOMERS_ID FOR CUSTOMERS
ACTIVE BEFORE INSERT
POSITION 1
AS
BEGIN
 IF (NEW.ID IS NULL) THEN
  NEW.ID = GEN_ID(CUSTOMERS_ID_GEN, 1);
END^

SET TERM ; ^

But when I inserting second row like :

insert into Customers(Name,LastName) values('Hamed','Kamrava');

It gets below error :

Violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on table "CUSTOMERS".

Answer 1

id is a primary key with default value 1.

In the first record, since you have not explicitly mentioned the value of id, it has inserted with 1. But you cannot have any other records with id = 1 since id is a Primary Key.

Use the statement:

insert into Customers(id, Name, LastName) values (2, 'Hamed', 'Kamrava');

This should insert the record. If you do not want to hardcode the value of ID for each row, suggest you to create a sequence and then during the insert, use,

insert into Customers(id, Name, LastName) values (nextval('<seq_name>'), <name>, <lastname>);

Answer 2

Since your trigger code is

 IF (NEW.ID IS NULL) THEN
  NEW.ID = GEN_ID(CUSTOMERS_ID_GEN, 1);

and, as @Orangecrush posted, you set a default value of 1, a unique id is never generated. So you should try to omit the default value in the ddl.