sql - MariaDB BEFORE INSERT TRIGGER for UUID -

i'm using mariadb 5.5.29 on windows 64 platform. created table:

create table dm_countries (     country_uuid binary(16) not null,     c_name varchar(255) not null unique,     c_localname varchar(255) default null,     c_countrycode varchar(255) default null,     primary key (country_uuid) ) engine=innodb; 

now want make sure, uuid (which ment primary key) automatically inserted if not provided. therefore, created trigger:

delimiter | create trigger trig_bi_dm_countries before insert on dm_countries each row begin     if new.country_uuid null         set new.country_uuid = uuid();     end if; end| delimiter ; 

to make sure commited (even if schema changes should not need it):

commit; 

now, when try insert new row this:

insert dm_countries     (c_name, c_localname, c_countrycode) values     ('großbritannien', 'great britain', 'uk'); 

i expected trigger put new uuid in place , successful insert table. happens this:

error code: 1364. field 'country_uuid' doesn't have default value

this definetly correct answer did not provided uuid. trigger supposed fill automatically.

i tried formulate trigger unhex, make sure generated value fits in field:

delimiter | create trigger trig_bi_dm_countries before insert on dm_countries each row begin     if new.country_uuid null         set new.country_uuid = unhex(replace(uuid(), '-', ''));     end if; end| delimiter ; 

the result still same.

the question is: doing wrong?

i think problem column not have default value defined - , can't put default null primary key.

try adding default 0 value in column definition , changing condition inside trigger to: if new.country_uuid = 0 then:

delimiter |  create table dm_countries (     country_uuid binary(16) not null default 0,         -- changed here     c_name varchar(255) not null unique,     c_localname varchar(255) default null,     c_countrycode varchar(255) default null,     primary key (country_uuid) ) engine=innodb |   create trigger trig_bi_dm_countries before insert on dm_countries each row begin     if new.country_uuid = 0                        -- , here         set new.country_uuid = uuid();     end if; end |  insert dm_countries     (c_name, c_localname, c_countrycode) values     ('großbritannien', 'great britain', 'uk') |  delimiter ; 

tested @ sql-fiddle (at mysql 5.5, no mariadb instance available)