MariaDB functional index

Question

I am trying to define the following table:

CREATE TABLE IF NOT EXISTS `test` (
  `gid` INT NOT NULL,
  `x` INT NOT NULL,
  `y` INT NOT NULL,
  `z` INT NOT NULL,
  `type` INT NOT NULL,
  ...
  PRIMARY KEY ( `gid`, `x`, `y`, `z` ),
  UNIQUE INDEX `type_index` ( `gid`, ( CASE WHEN `type` = 1 THEN `type` END ) ),
  UNIQUE INDEX `tp_index` ( `gid`, ( CASE WHEN `type` = 2 THEN ... END ) ),
);

I want the table to allow only one entry per gid when type is 1, while it can have multiple entries per gid for any other type. In MySQL, this query is working fine. However, using MariaDB the query does not work at all.

Result in MariaDB:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( CASE WHEN type = 1 THEN type END ) ), UNIQUE INDEX tppad ( gid, ( CASE' at line 1

How can I solve this error?

In other words: What is the MariaDB equivalent to this?

I am using version 10.2.20-MariaDB-10.2.20+maria~xenial-log

Answer 1

Use a generated column:

type_is_1 boolean generated always as (case when type = 1 then 1 end),
type_is_2 boolean generated always as (case when type = 2 then 1 end),
UNIQUE INDEX type_index ( gid, type_is_1 ),
UNIQUE INDEX tp_index ( gid, type_is_2 )

That is MariaDB does not allow expressions as index keys. But you can easily work around that using generated columns.