Year and Month as Primary Key

Question

I have a Payments table, with paymentDate and licenseId attributes. I want to make sure that, there is only one payment for a licenseId every month. How can I do that? I have seen an answer which says to store incomplete date (day as 00) and make it unique. But, I need exact date too.

I have tried the below things but in vain.

PRIMARY KEY (`licenseId`, YEAR(`paymentDate`), MONTH(`paymentDate`))

UNIQUE (`licenseId`, YEAR(`paymentDate`), MONTH(`paymentDate`))

I am looking for ways, which doesn't include extra year and month attributes in table.

Answer 1

You can use functions to obtain PRIMARY KEYs by using GENERATED (or COMPUTED or CALCULATED and sometimes VIRTUAL) fields. However, the virtual keyword also refers to the storage class (STORED or VIRTUAL) meaning that the generated field is either kept on disk or calculated on-the-fly.

See the fiddle here:

You can have your (licenceID, YEAR(paymentDate), MONTH(paymentDate)) as the PRIMARY KEY as follows:

CREATE TABLE t
(
  i INT, 
  a DATE,
  a_y SMALLINT GENERATED ALWAYS AS  (YEAR(a)) STORED,
  a_m SMALLINT GENERATED ALWAYS AS (MONTH(a)) STORED,
  
  CONSTRAINT t_pk PRIMARY KEY (i, a_y, a_m)
);

and then test:

INSERT INTO t (i, a) VALUES (1, NOW());                               ✓
INSERT INTO t (i, a) VALUES (1, DATE_ADD(NOW(),  INTERVAL 1 MONTH));  ✓
INSERT INTO t (i, a) VALUES (2, now());                               ✓

but:

INSERT INTO t (i, a) VALUES (1, '2021-10-30');

gives

Duplicate entry '1-2021-10' for key 't.PRIMARY'

Creating a table with a PRIMARY KEY using direct calls to the functions isn't possible:

CREATE TABLE s
(
  i INT, 
  a DATE,
  a_y SMALLINT GENERATED ALWAYS AS (YEAR(a)) STORED,
  a_m SMALLINT GENERATED ALWAYS AS (MONTH(a)) STORED,
  
  CONSTRAINT t_pk PRIMARY KEY (i, (YEAR(a)), (MONTH(a)))
);

gives:

The primary key cannot be a functional index

Doesn't work in PostgreSQL either. In MySQL, it won't work for the VIRTUAL storage class (which PostgreSQL doesn't support anyway).

Answer 2

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.