'MariaDB add UNIQUE constraint on calculated field

I would like to add a unique constraint on a table in order to avoid more than one transaction every 2 minutes on the same ID... but MariaDB doesn't seem to like that :

ALTER TABLE expenses ADD CONSTRAINT UNIQUE (id, FLOOR(UNIX_TIMESTAMP(date_inserted)/120));
ERROR 1064 (42000): 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 'UNIX_TIMESTAMP(date_inserted)/120))' at line 1

Is there any way to achieve this ?

Thanks

mysqlmariadbunique-constraint


Solution 1:[1]

Create a generated column and use that in your index

eg

CREATE TABLE `t` (
  `salesperson` varchar(10) DEFAULT NULL,
  `id` int(11) DEFAULT NULL,
  `date_inserted` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
alter table t
    add column uts int as (floor(UNIX_TIMESTAMP(date_inserted)/120));
ALTER TABLE t 
 ADD CONSTRAINT UNIQUE iddt (id, uts);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 P.Salmon

Related Questions