MariaDB BEFORE INSERT TRIGGER for UUID

Question

I'm using MariaDB 5.5.29 on a Windows 64 Platform. I created this 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 I want to make sure, that an UUID (which is ment to be the primary key) is automatically inserted if not provided. Therefore, I created this trigger:

DELIMITER |
CREATE TRIGGER TRIG_BI_DM_COUNTRIES
BEFORE INSERT ON DM_COUNTRIES
FOR EACH ROW
BEGIN
    IF NEW.COUNTRY_UUID IS NULL THEN
        SET NEW.COUNTRY_UUID = UUID();
    END IF;
END|
DELIMITER ;

To make sure everything is commited (even if schema changes should not need it):

COMMIT;

Now, when I try to insert a new row with this:

INSERT INTO DM_COUNTRIES
    (C_NAME, C_LOCALNAME, C_COUNTRYCODE)
VALUES
    ('Großbritannien', 'Great Britain', 'UK');

I expected the trigger to put a new UUID in place and then a successful insert into the table. But what happens is this:

Error Code: 1364. Field 'COUNTRY_UUID' doesn't have a default value

This is definetly the correct answer as I did not provided an UUID. The trigger was supposed to fill it automatically.

I tried to formulate the trigger with unhex, just to make sure the generated value fits in the field:

DELIMITER |
CREATE TRIGGER TRIG_BI_DM_COUNTRIES
BEFORE INSERT ON DM_COUNTRIES
FOR EACH ROW
BEGIN
    IF NEW.COUNTRY_UUID IS NULL THEN
        SET NEW.COUNTRY_UUID = UNHEX(REPLACE(UUID(), '-', ''));
    END IF;
END|
DELIMITER ;

The result is still the same.

The question is: What am I doing wrong?

Answer 1

I think the problem is that the column does not have a default value defined - and we can't put DEFAULT NULL as it is the primary key.

Try adding a DEFAULT 0 value in the column definition and changing the condition inside the 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
FOR EACH ROW
BEGIN
    IF NEW.COUNTRY_UUID = 0 THEN                        -- and here
        SET NEW.COUNTRY_UUID = UUID();
    END IF;
END |

INSERT INTO DM_COUNTRIES
    (C_NAME, C_LOCALNAME, C_COUNTRYCODE)
VALUES
    ('Großbritannien', 'Great Britain', 'UK') |

DELIMITER ;

Tested at SQL-Fiddle (at MySQL 5.5, no MariaDB instance available)