insert row without set primary column

Question

I have that query :

INSERT INTO GOST (ASSORTMENTID, ROZMIAR, GOST) 
VALUES ( 54,'S','MjgwMzktODkgMTc0LTk2') 

I want insert new row in table GOST, but I don't want to specify column with primary key - GOSTID. I want that database set next id value. When I run this code I have that error:

validation error for column GOSTID, value "* null *"

I understand that I should set GOSTID column in INSERT query, yes ?

It is possible to run this without this parameter ?

Answer 1

Obviously, your primary key is a NOT NULL column, which means, it's always required. You cannot insert a row without giving a value for the primary key (unless it were an "auto-number" column which gets automatically set by the database system).

Answer 2

Use "before insert" trigger to set value for primary key. Firebird doesn't have "auto-increment" field type, so you need take care of it by yourself.

See http://www.firebirdfaq.org/faq29/ for tutorial how to do this. Some DB applications (eg Database Workbench) can create the trigger and generator automatically.

Answer 3

I think a sample script worths more than 1000 words:

Go to a shell interface in the firebird server machine, cd to a folder where you have read/write permissions, start isql or isql-fb (depends on your system and firebird version) and run this script:

create database 'netmajor.fdb' user 'sysdba' password 'masterkey';
set autoddl off;

create table netmajor_example (
    netmajor_id     integer not null
  , str_data        varchar(200)
  , int_data        integer
  , constraint pk_netmajor_example
      primary key (netmajor_id)
);

create generator netmajor_gen;

set term ^;

create trigger netmajor_pkassign
   for netmajor_example
active before insert position 1
AS
begin
  if (new.netmajor_id is null) then
    new.netmajor_id = gen_id(netmajor_gen, 1);
end
^

commit work^

set term ; ^


insert into netmajor_example (str_data, int_data) values ('one', 1);
insert into netmajor_example (str_data, int_data) values ('twenty', 20);
commit work;

select * from netmajor_example;

Take a look at the results, which in my machine are:

; NETMAJOR_ID STR_DATA                     INT_DATA
;============ ============================ ============
;           1 one                                     1
;           2 twenty                                 20

IF you have questions, don't hesitate to contact. Best regards.