CODEWITHSUNDEEP
mysqlmariadb
Priv
Priv

Reputation: 840

Insert or replace when column matches specific value

I have 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` )
);

The combination of gid, x, y, z is unique. There can be multiple rows with the same gid and type as long as the x/y/z values are different.

However, I want the combination of gid and a specific type value to be unique. For example, there can only be one row per gid = x with type = 1, but there can be multiple rows with gid = x and type = 2. I know that I can declare a unique constraint for gid and type and then use INSERT ... ON DUPLICATE VALUE UPDATE .., however I only want that to work when type equals 1.

Any solution for this?

EDIT: A solution for MySQL has been found. However, this does not work with MariaDB. What is the MariaDB solution?

Upvotes: 1

Views: 113

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176024

You could achieve it with function index(supported by MySQL 8.0.13 and newer):

CREATE UNIQUE INDEX myIndex ON test(gid,(CASE WHEN `type` = '1' THEN `type` END));

db<>fiddle demo

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` )
);

CREATE UNIQUE INDEX myIndex ON test(gid,(CASE WHEN `type` = '1' THEN `type` END));

INSERT INTO `test`(gid, x,y,z,`type`) VALUES (1,1,1,1,1);
INSERT INTO `test`(gid, x,y,z,`type`) VALUES (1,2,2,2,1);
-- Duplicate entry '1-1' for key 'myIndex'

INSERT INTO `test`(gid, x,y,z,`type`) VALUES (3,1,1,1,2);
INSERT INTO `test`(gid, x,y,z,`type`) VALUES (3,1,2,2,2);
SELECT * FROM `test`;

Can I add the unique index on table creation? 

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 myIndex(gid,(CASE WHEN `type` = '1' THEN `type` END))
);

db<>fiddle demo2

Upvotes: 4

Related Questions