Problem with a foreign key referencing a primary key which is also part of an unique key (MariaDB)

Question

I created a database using MariaDb. I have the following author table (with id as primary key), which is extended by two other tables, author_personal and author_corporate, representing two different kinds of author and having different fields:

CREATE TABLE `author` (
  `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_personal` (
  `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 `author` (`id`, `author_type`) ON DELETE CASCADE
);

CREATE TABLE `author_corporate` (
  `id` int(10) unsigned NOT NULL,
  `corporate_name` varchar(50) DEFAULT NULL,
  `corporate_acronym` varchar(5) DEFAULT NULL,
  `author_type` char(1) GENERATED ALWAYS AS ('C') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_corporate_FK` (`id`,`author_type`),
  CONSTRAINT `author_corporate_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

Although author.id would be enough, I decided to create an UNIQUE KEY (id, author_type) to be referenced by the foreign key in the two other tables, so that it would be impossible to reference from author_personal an author which is not flagged as P, and from author_corporate an author which is not flagged as C.

The problem rises when I want to reference author using the primary key, like in this table:

CREATE TABLE `work_authors` (
  `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` FOREIGN KEY (`work_id`) REFERENCES `work` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `work_authors_FK_author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON UPDATE CASCADE
);
    

Both DBeaver and PhpMyAdmin think that work_authors_FK_author references author_UN instead of the actual primary key (author.id).

This means that in DBeaver I am not able to click on the values in work_authors.author_id and open the referenced record in authors because I get the following error:

Entity [davide_library.author] association [work_authors_FK_author] columns differs from referenced constraint [author_UN] (1<>2)

I created the foreign keys using DBeaver. Although I selected PRIMARY as the unique key, in the list of foreign keys it always show author_UN as the referenced object. I don't know if this behavior depends on MariaDB or DBeaver.

My question is:

Is there any way to explicitily reference in the DDL the primary key instead of the unique key?

or, alternatively:

Is there a different way, other than the UNUQUE constraint, to check that author_personal references only authors flagged with P, and the same for author_corporate?

Answer 1

MariadDB seems to handle it correctly. According to Fiddle the information_schema is correct, so the problem seems to be with your tools:

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