0

Is it possible to enforce uniqueness in MySQL on a pair of columns X,Y where X is Date and Y is Integer. I need uniqueness to apply to Year(X),Y. using UNIQUE it is easy to do this on the pair of columns, but I don't see how I can add the constraint by extracting the Year part of the date.

shelbypereira
  • 2,097
  • 3
  • 27
  • 50
  • 1
    No. You can only apply uniqueness to an entire field. you'd have to have a separate integer field for the year, and unique that entire field. Maybe someday when mysql supports proper constraints, you'll be able to do this, but not today. – Marc B Jun 07 '16 at 20:50
  • Related: http://stackoverflow.com/questions/95183/how-does-one-create-an-index-on-the-date-part-of-datetime-field-in-mysql – rrauenza Jun 07 '16 at 20:56
  • Well, you can enforce the constraint in the query itself, or I suppose you could have a separate table storing valid years. – Strawberry Jun 07 '16 at 20:57
  • wrong slot huh Strawberry? :p – Drew Jun 07 '16 at 20:57
  • Pesky mobile phones – Strawberry Jun 07 '16 at 20:57
  • 1
    http://stackoverflow.com/a/95295/2077386 : "In the meantime you can use character columns for storing DATETIME values as strings, with only first N characters being indexed. With some careful usage of triggers in MySQL 5 you can create a reasonably robust solution based on this idea." – rrauenza Jun 07 '16 at 20:58

1 Answers1

1

If you are running MySQL 5.7 you could use generated tables to concatinate two columns together and define the generated column as unique.

CREATE TABLE unq (mydate DATE, myint INT, mydateint CHAR(30) AS (CONCAT(mydate,myint)) UNIQUE);

David Stokes
  • 120
  • 3