Add unique constrains as date from datetime column in mysql

Question

I need to create a unique constrains for my table which has date time but i need to make mid and date from createdttm as unique . Here is my table structure

CREATE TABLE IF NOT EXISTS `sampletable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mid` varchar(50) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `createdttm` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31826 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

score 0 · Answer 1 · answered Aug 02 '21 at 14:10

0

You can do this by using a combined unique index.

UNIQUE KEY `date key` (`mid`,`createdttm`),

answered Aug 02 '21 at 14:10

Thallius

2,482
2
18
36

score 0 · Answer 2 · answered Aug 02 '21 at 14:12

This could easily be solved by adding a unique key for mid and the date part of createdttm:

ALTER TABLE `sampletable` ADD UNIQUE `un_idx`(`mid`, DATE(`createdttm`));

but, MySql does not allow the use of functions in the definition of an index.

What you can do is define a virtual generated column to be the date part of createdttm and then create the unique index based on that column and mid:

ALTER TABLE `sampletable` ADD COLUMN `created` DATE AS (DATE(`createdttm`));
ALTER TABLE `sampletable` ADD UNIQUE `un_idx`(`mid`, `created`);

See the demo.

Add unique constrains as date from datetime column in mysql

2 Answers2