Foreign key rejected despite columns having different data types

Question

I'm trying to set up two tables in a database, and add a foreign key between them. They're declared as follows:

CREATE TABLE `clothing` (
  `name` varchar(26) COLLATE utf8_bin NOT NULL,
  `image` varchar(64) COLLATE utf8_bin NOT NULL,
  `localized_name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`localized_name`)),
  `main` varchar(18) COLLATE utf8_bin DEFAULT NULL,
  `stars` tinyint(3) unsigned NOT NULL,
  `id` tinyint(3) unsigned NOT NULL,
  `splatnet` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`splatnet`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `abilities` (
  `name` varchar(18) COLLATE utf8_bin DEFAULT NULL,
  `image` varchar(48) COLLATE utf8_bin NOT NULL,
  `id` tinyint(3) unsigned NOT NULL,
  `localized_name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`localized_name`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I want to create a foreign key on clothing that references abilities with the following command:

ALTER TABLE `abilities` ADD FOREIGN KEY (`name`) REFERENCES `clothing` (`main`);

However, attempting to do this raises this error in return:

Source and target columns must have the same data type, there must be an index on the target columns and referenced data must exist.
Can't create table `prismarine_rusted`.`abilities` (errno: 150 "Foreign key constraint is incorrectly formed")

I'm not entirely sure what's causing this, and unless I'm overlooking something really obvious, main and name have the same type, and therefore, should be able to be tied together via a foreign key. I'm using MariaDB v10.4.12, with SQL mode set to TRADITIONAL.

score 4 · Accepted Answer · answered Apr 12 '20 at 04:56

Although the foreign and primary key columns involved here are the same type, you are trying to reference clothing.main, which is not a unique or primary key column. From the MariaDB documentation:

The referenced columns must be a PRIMARY KEY or a UNIQUE index.

Note that this differs from InnoDB on MySQL, where a foreign key column can in fact reference a non unique column in another table.

One way to remedy this error would be to make clothing.main a unique column:

ALTER TABLE clothing ADD UNIQUE (main);

Note that doing this might only make logical sense if the values in main are already unique. If not, then perhaps you would have to revisit your data model.

Not to mention the error message even says right there that the columns have to be indexed. — Powerlord, Apr 12 '20 at 04:59
@Powerlord I remember you [from this question](https://stackoverflow.com/questions/41101998/git-checkout-vs-git-checkout/41102213#41102213). — Tim Biegeleisen, Apr 12 '20 at 05:00

score 0 · Answer 2 · answered Apr 12 '20 at 04:58

0

It might be because there is a value in abilities.name that has no match in the referenced table.

answered Apr 12 '20 at 04:58

Peter Dongan

1,762
12
17

Foreign key rejected despite columns having different data types

2 Answers2