-2

I have two tables, entity and entity_role. entity looks like this:

CREATE TABLE `entity` (
  `id` varchar(36) NOT NULL,
  `type` varchar(36) NOT NULL,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`,`type`)
);

And entity_role looks like this:

CREATE TABLE `entity_role` (
  `id` varchar(36) NOT NULL,
  `entity_type` varchar(36) DEFAULT NULL,
  `user_id` varchar(36) NOT NULL,
  `role_id` varchar(36) NOT NULL,
  `entity_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_entity_role_user_id` (`user_id`),
  KEY `FK_entity_role_role_id` (`role_id`),
  CONSTRAINT `FK_entity_role_role_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  CONSTRAINT `FK_entity_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

I'm trying to add a foreign key reference from entity_role to entity by running the following statement:

alter table entity_role add constraint `FK_entity_role_entity`
  foreign key(`entity_id`, `entity_type`)
  references `entity`(`id`, `type`)
  on delete cascade;

However I get back from the server: [Code: 1005, SQL State: HY000] (conn=26) Can't create table entity_role (errno: 150 "Foreign key constraint is incorrectly formed")

I've looked over this multiple times and read through the MariaDB documentation several times and I feel like I'm banging my head against the wall now. Anyone know what I'm doing wrong here? Both tables are using the InnoDB engine.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Jesse
  • 253
  • 1
  • 3
  • 12
  • your question title mentions null values but your question body does not. do be aware that if any of the values in a composite foreign key are null, the non-null values may have any value, even ones not present in the foreign table. https://dbfiddle.uk/kpRGR57a – ysth May 23 '23 at 02:49

1 Answers1

0

Turns out that the answer to this question was exactly what I was looking for. Specifically, MariaDB won't tell you why it doesn't like your foreign key unless you go ask the specific engine:

SHOW ENGINE InnoDB STATUS;

Won't post the wall of text, but this line in particular was the relevant bit (and helpfully was at the very top!):

2023-05-19 14:39:14 0x7fc78c15e640 Error in foreign key constraint of table `default`.`entity_role`:
Alter  table `default`.`entity_role` with foreign key `FK_entity_role_entity` constraint failed. Field type or character set for column 'entity_type' does not match referenced column 'type'.------------

I had omitted omitted this in the question, not realizing that they were relevant, but the varchar columns all have ascii_bin collation. All of them, that is, except for the entity_type column on entity_role, which had none set at all (I presume this means use the table default)! Modifying the column to use a matching collation solved the problem.

Jesse
  • 253
  • 1
  • 3
  • 12