2

This is the query I am trying to execute but it doesn't work.

ALTER TABLE values ADD UNIQUE(id_cod, id_op, (SELECT date(V.completed_date) FROM values V;));

I have these 3 columns in the values table: id_cod, id_op and completed_date which I want to use for the UNIQUE KEY but it is vital to me that I only use the date part of the date-time.

Cœur
  • 37,241
  • 25
  • 195
  • 267
adrian
  • 21
  • 1
  • 2

2 Answers2

4

You have three alternatives, none of which are great options.

The first is to include a separate column just for the date. You can maintain this using a trigger, and then create a unique index using that column.

You can split the column into two pieces, a date and a time. Then use only the date part for the index.

You can represent the field as a string, using the format YYYY-MM-DD HH:MM:SS. Then you can use a prefix index on this field: create unique index idx_values_3 on values(id_code, id_op, datestr(10)).

I think the second option would be the most reasonable.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I agree that the second option is best. I don't agree though that it's not a great option :-) It seems that id_code + id_op + date build the table's natural key, whereas the time is just an additional information. So it makes complete sense to separate the two. – Thorsten Kettner Sep 18 '14 at 11:47
  • @ThorstenKettner . . . Other databases support either function-based indexes or indexes on computed columns. Those are better ways to implement such a unique index. – Gordon Linoff Sep 18 '14 at 17:46
  • Yes I know of them and I agree when talking of an index that is meant to speed up access. Here, however, we are talking about a unique index. id_code + id_op + date uniquely identify a record. I think it's good to separate such a key from additional data. – Thorsten Kettner Sep 18 '14 at 19:08
2

You can't do that in MySQL.

You can add a fourth column completed_date_d DATE, include it into the unique constraint and make a trigger which would update the column with date part of completed_date.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614