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.
You can use functions to obtain
PRIMARY KEYs by using
CALCULATED and sometimes
VIRTUAL) fields. However, the virtual keyword also refers to the storage class (
VIRTUAL) meaning that the generated field is either kept on disk or calculated on-the-fly.
See the fiddle here:
You can have your (
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()); ✓
INSERT INTO t (i, a) VALUES (1, '2021-10-30');
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))) );
The primary key cannot be a functional index
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.