How to create default constraint on the table of Firebird to assign auto-generated UUID value

Question

I am trying to define a table with a column type of UUID (CHAR(16) CHARACTER SET OCTETS). After reading through the Firebird 3.0 Developer's Guide, I found only context-variables/simple expression/constant are supported. I would like to know if there is a way to define a default constraint on a table to call GEN_UUID() to assign UUID for insertion?

Answer 1

You cannot do this with a DEFAULT clause, as that only allows literals and a select number of what the Firebird documentation refers to as 'context variables'.

To do what you want, you need to create a before insert trigger to generate the value. Something like this:

create trigger bi_yourtable before insert on yourtable
as
begin
  new.uuid_column = gen_uuid();
end

Or, if you don't want unconditional generation of the UUID:

create trigger bi_yourtable before insert on yourtable
as
begin
  if (new.uuid_column is null) then
  begin
    new.uuid_column = gen_uuid();
  end
end