By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| @@version |
|---|
| 5.7.35 |
-- MySQL 5.7.29
-- Foreign Key checks value of virtual column against the reference table
-- if the column is used in a unique index and if the column the direct
-- predecessor of the actual foreign key column
-- (here virtual column `year`, followed by `createdBy_user_id` used in `user_id_fk_constr)
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`active` tinyint(1) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`email_confirmed_at` datetime DEFAULT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(255) NOT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`job` varchar(64) DEFAULT NULL,
`position` varchar(64) DEFAULT NULL,
`specialKnowledge` text,
`tasks` text,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
-- id = 20 to match the current year!
INSERT INTO users (id, username, password) VALUES (20,'admin', 'secret');
CREATE TABLE `tasks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`noOfYear` int(11) NOT NULL,
`year` int(11) GENERATED ALWAYS AS (right(year(`createdAt`),2)) VIRTUAL NOT NULL,
`createdBy_user_id` int(11) NOT NULL,
`updatedBy_user_id` int(11) NOT NULL,
`status` enum('open','closed') NOT NULL,
`customer_id` int(11) NOT NULL,
`projectDescription` text,
PRIMARY KEY (`id`),
UNIQUE KEY `tasks_year_unique_constr` (`year`,`noOfYear`),
KEY `user_id_fk_constr` (`createdBy_user_id`),
KEY `customer_id_fk_constr` (`customer_id`),
KEY `user_up_id_fk_constr` (`updatedBy_user_id`),
-- CONSTRAINT `customer_id_fk_constr` FOREIGN KEY (`customer_id`) REFERENCES `Customer` (`id`),
CONSTRAINT `user_id_fk_constr` FOREIGN KEY (`createdBy_user_id`) REFERENCES `users` (`id`),
CONSTRAINT `user_up_id_fk_constr` FOREIGN KEY (`updatedBy_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
-- there is no entry "1" for createdBy_user_id, but the foreign key
-- will still accept it as it checks the value of the virtual column "year" (20)
-- against the user table
-- updatedBy_user_id will be verified correctly
INSERT INTO tasks (createdBy_user_id,updatedBy_user_id,noOfYear,createdAt,updatedAt,status,customer_id)
VALUES (1,20,1,NOW(),NOW(),"open",1)
Cannot add or update a child row: a foreign key constraint fails (`db_1887918981`.`tasks`, CONSTRAINT `user_id_fk_constr` FOREIGN KEY (`createdBy_user_id`) REFERENCES `users` (`id`))
select * from users
| id | active | email_confirmed_at | username | password | first_name | last_name | job | position | specialKnowledge | tasks | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 20 | null | null | null | admin | secret | null | null | null | null | null | null |
-- createdBy_user_id is 1, while no row in users with id 1 exists
select * from tasks