0

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).

DBeaver Foreign Key description

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?

8
  • 1
    You'll need a trigger to enforce the exclusive subtype.
    – bbaird
    Jan 20 at 16:07
  • Do you mean that I should replace the UNIQUE constraint with a trigger (I didn't know about them) and use only the ID in relations? Jan 20 at 16:11
  • 1
    Yes. I'm not an expert in MariaDB so I can't show you how to write the trigger, but you will need to query the author table to verify the author_type matches the table the record is being inserted into. mariadb.com/kb/en/trigger-overview
    – bbaird
    Jan 20 at 16:15
  • Thank you, knowing about triggers is helpful enough! Jan 20 at 16:24
  • Have you tried author_type as a stored column? You can also try it as a normal column: author_type char(1) DEFAULT 'C' NOT NULL AND a CHECK (author_type ='C')
    – Lennart
    Jan 21 at 0:19
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.