CODEWITHSUNDEEP
mysqlsqlunique-constraint
Yahya Uddin
Yahya Uddin

Reputation: 28871

Add unique constraint to date portion of a DateTime column

In MySQL is it possible to do a unique constraint for the date portion of a dateTime column?

For example if I had 2018-10-01 10:05:06, the following would not be allowed 2018-10-01 11:00:00, because it has the same date.

Currently my best solution is to separate the dateTime column in to a separate date and time column, and perform a unique operation on the date column.

Upvotes: 1

Views: 2220

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You understand one method, which is to split the date and time into separate columns. That is probably the simplest method.

A trigger is another method, but that is rather painful to maintain.

Another possibility is to use column prefixes. The downside is that the column needs to be stored as a string:

create table t (
    . . .,
    datetimecol char(19),  -- format "YYYY-MM-DD HH:MI:SS"
);

create unique index unq_t_datetimecol on t(datetimecol(10));

This approach is so useful that MySQL has finally implemented indexes using expressions in 8.0.13. This allows:

create table t (
    . . .,
    datetimecol datetime, 
    . . .
);

create unique index unq_t_datetimecol on t(date(datetimecol));

Oh, there is another method using generated columns as well:

create table t (
    . . .,
    datetimecol datetime, 
    . . .,
    datecol date generated always as (date(datetimecol)) stored,
    unique (datecol)
);

Upvotes: 0

GMB
GMB

Reputation: 222512

This is too complex to be natively handled by MySQL, that has a quite limited support for constraints anyway.

You would have to create :

  • a stored procedure that performs the check
  • BEFORE triggers for INSERT and UPDATE, that call the procedure

Here is what the procedure should look like (assuming that the table is named my_table and that the column holding the datetime is my_datetime) :

DELIMITER $$ 

CREATE PROCEDURE `check_new_datetime`(IN new_datetime DATETIME)
BEGIN
    IF new_datetime IS NOT NULL THEN
        IF (SELECT COUNT(*) FROM my_table WHERE DATE(new_datetime) = DATE(my_datetime)) > 0 THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'check constraint check_new_datetime failed';
        END IF;
    END IF;
END$$

DELIMITER ;

Triggers creation :

-- before insert
DELIMITER $$
CREATE TRIGGER `my_table_before_insert` BEFORE INSERT ON `my_table`
FOR EACH ROW
BEGIN
    CALL check_new_datetime(new.my_datetime);
END$$   
DELIMITER ; 

-- before update
DELIMITER $$
CREATE TRIGGER `my_table_before_update` BEFORE UPDATE ON `my_table`
FOR EACH ROW
BEGIN
    CALL check_new_datetime(new.my_datetime);
END$$
DELIMITER ;

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30585

You can't but as a work-around

CREATE TABLE tbl_x (
  ts DateTime,
  gents Date AS (CAST(tbl_x.ts AS DATE)),
  UNIQUE(gents)
);

Upvotes: 0

Related Questions