MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `author1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_type` char(1) NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  UNIQUE KEY `author_UN` (`id`,`author_type`) USING BTREE,
  CONSTRAINT `author_CHECK_type` CHECK (`author_type` in ('P','C'))
);

CREATE TABLE `author_personal1` (
  `id` int(10) unsigned NOT NULL,
  `surname` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  -- other fields
  `author_type` char(1) GENERATED ALWAYS AS ('P') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_personal_FK` (`id`,`author_type`),
  CONSTRAINT `author_personal_FK` FOREIGN KEY (`id`, `author_type`)
     REFERENCES `author1` (`id`, `author_type`) ON DELETE CASCADE
);
CREATE TABLE `work_authors1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  KEY `work_authors_FK` (`work_id`),
  KEY `work_authors_FK_author` (`author_id`) USING BTREE,
  CONSTRAINT `work_authors_FK_author` FOREIGN KEY (`author_id`) 
     REFERENCES `author1` (`id`) ON UPDATE CASCADE
);
select table_name, constraint_name, referenced_table_name, unique_constraint_name from information_schema.referential_constraints;


table_name
constraint_name
referenced_table_name
unique_constraint_name


author_personal1
author_personal_FK
author1
author_UN

work_authors1
work_authors_FK_author
author1
PRIMARY

table_name	constraint_name	referenced_table_name	unique_constraint_name
author_personal1	author_personal_FK	author1	author_UN
work_authors1	work_authors_FK_author	author1	PRIMARY