Clever Geek Handbook

Problem with automatic Firebird installation

I have created a customer table through the 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 insert the second line like:

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

It gets below the error:

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


source to share


2 answers


id

is the primary key with a default value 1

.

In the first record, since you did not explicitly specify a value id

, it is inserted from 1. But you cannot have any other records with id = 1

, since it id

is the Primary key.

Use the instruction:



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

This should insert a record. If you don't want to hardcode the ID value for each row, suggest createsequence

and then during insert use,

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


source


Since your startup code is

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


and as posted by @Orangecrush you set the default to 1, the unique id is never generated. Therefore, you should try to omit the default in ddl.

+4


source






More articles: