Skip to main content
deleted 11 characters in body
Source Link
jkavalik
  • 5.1k
  • 1
  • 13
  • 20

With MySQL 8.0.13+ functional key parts should work fine:

CREATE TABLE `table` (
  `licenseId` int,
  `paymentDate` date DEFAULT NULL,
  UNIQUE KEY `c` (`licenseId`,(year(`paymentDate`)),(month(`paymentDate`)))
);

Can be tested online at https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fef176b2ec3a68a1e45acbca122339f7

On the other hand the Primary keyPrimary key cannot be defined that way (tested here). So you need to add some other - autoinc or similar preferably, or possibly even (licenseId, paymentDate) would be fine as athe PK.

With MySQL 8.0.13+ functional key parts should work fine:

CREATE TABLE `table` (
  `licenseId` int,
  `paymentDate` date DEFAULT NULL,
  UNIQUE KEY `c` (`licenseId`,(year(`paymentDate`)),(month(`paymentDate`)))
);

Can be tested online at https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fef176b2ec3a68a1e45acbca122339f7

On the other hand the Primary key cannot be defined that way (tested here). So you need to add some other - autoinc or similar preferably, or possibly even (licenseId, paymentDate) would be fine as a PK.

With MySQL 8.0.13+ functional key parts should work fine:

CREATE TABLE `table` (
  `licenseId` int,
  `paymentDate` date DEFAULT NULL,
  UNIQUE KEY `c` (`licenseId`,(year(`paymentDate`)),(month(`paymentDate`)))
);

Can be tested online at https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fef176b2ec3a68a1e45acbca122339f7

On the other hand the Primary key cannot be defined that way (tested here). So you need to add some other - autoinc or similar, or possibly even (licenseId, paymentDate) would be fine as the PK.

Source Link
jkavalik
  • 5.1k
  • 1
  • 13
  • 20

With MySQL 8.0.13+ functional key parts should work fine:

CREATE TABLE `table` (
  `licenseId` int,
  `paymentDate` date DEFAULT NULL,
  UNIQUE KEY `c` (`licenseId`,(year(`paymentDate`)),(month(`paymentDate`)))
);

Can be tested online at https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fef176b2ec3a68a1e45acbca122339f7

On the other hand the Primary key cannot be defined that way (tested here). So you need to add some other - autoinc or similar preferably, or possibly even (licenseId, paymentDate) would be fine as a PK.