How to make MariaDB produce an error if value not specified on NOT NULL Enum Columns?

user_14098466 0
Viewed 958

Problem Summary

For MariaDB an insert statement can produce an error if you do not specify a column which has a NOT NULL constraint. However if the column is of data type ENUM instead the implicit default (first in the list) is used and the insert does not produce an error. I would like to get an error instead.

Details / What I tried

Error for number data type

On a table with with a number column with NOT NULL constraint I do get an error as expected

create table test_number (
    name VARCHAR(40),
    num tinyint(1) not null
);

insert into test_number (name, num) values ('small',null); # sanity check

insert into test_number (name) values ('small');
#Response: Error Code: 1364. Field 'num' doesn't have a default value

No Error for enum data type

On a table with with an ENUM column with NOT NULL constraint I do not get an error

create table test_enum (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large') not null
);

insert into test_enum (name, size) values ('small',null); # sanity check
#Response: Error Code: 1048. Column 'size' cannot be null
insert into test_enum (name, size) values ('small',default(size)); # sanity check 2
#Response: Error Code: 1364. Field 'size' doesn't have a default value

However the following insert statement is processed successfully

insert into test_enum (name) values ('small');
select * from test_enum;
|#|name |size   |
|1|small|x-small|

As you can see the implicit default 'x-small' which is the first in the list of the create statement is used.

Database Configuration

I tried it on multiple versions of MariaDB >= 5.5, the output above was produced with 10.5.5.

SHOW VARIABLES LIKE '%version%';
|version|10.5.5-MariaDB-1:10.5.5+maria~focal|

I do use strict SQL_MODE for the database

SELECT REPLACE(@@SQL_MODE, ',', '\n');
#|STRICT_ALL_TABLES|

Desired Result

The above success insert actually happens implicitly in our application whenever we add an enum column and forget to adapt the corresponding code in the DAO Layer. I would like to get an error instead so that existing tests in the CI will fail.

mariadb enums foreign-keys mysql sql
2 Answers

welcome to the forum.

You are not going to like my solution, but I think it might be the only way to get the exception you want.

Based on the apparently immutable fact that "If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.", then you can get an error with a null insert by

a. adding a dummy EMPTY STRING value e.g. "" as the first value in the enum field

b. defining a trigger as follows:

    CREATE TRIGGER `test_enum_BEFORE_INSERT` 
    BEFORE INSERT ON `test_enum` 
    FOR EACH ROW
    BEGIN
        IF new.size = "" THEN
            SIGNAL SQLSTATE '23000' SET MYSQL_ERRNO=1048, MESSAGE_TEXT='Column  "size" cannot be null';
        END IF;  
    END

The dummy string in the enum field can in fact be anything, so long the trigger tests for whatever it is. So "Bad Value" could be used, and the trigger's test becomes

IF new.size = "Bad Value" THEN...

The firing of the SIGNAL will cause the INSERT to fail.

user_48335060

This is documented behavior:

If a DEFAULT clause is missing, the default value will be:

  • NULL if the column is nullable;

  • otherwise, the first value in the enumeration.

There are other drawbacks when using enums, for example when it comes to maintaining the list of values. I would actually recommend using foreign keys and a referential table instead: this is much more flexible, and easier to maintain:

create table sizes (
    size varchar(20) primary key
);
insert into sizes values
    ('x-small'),
    ('small'),
    ('medium'),
    ('large'),
    ('x-large')
;

create table data (
    name varchar(40) primary key,
    size varchar(20) not null references sizes(size)
);

insert into data(name) values('foo');
-- error: Field 'size' doesn't have a default value

insert into data(name, size) values('foo', 'small');
-- ok

insert into data(name, size) values('bar', 'baz');
-- error: Cannot add or update a child row: a foreign key constraint fails (`db_2126492119`.`data`, CONSTRAINT `data_ibfk_1` FOREIGN KEY (`size`) REFERENCES `sizes` (`size`))

Demo on DB Fiddle

Depending on your actual use case, you could also have an auto-incremented primary key instead.

user_106767160
Related