MySQL Workbench - Error ERROR 1215 (HY000) with foreign key constraint

Question

i have the following two tables:

I need to add UT as foreign key for Id_UT, but the entries in "referenced column" disappear.

It seems to me that all the constraints for the key values are satisfied (datatype, size, uniqueness). What can be wrong here?. Thank you.

Here a minimum working example:

-- MySQL dump 10.13  Distrib 5.7.17, for Win32 (AMD64)
--
-- Host: 127.0.0.1    Database: newschema
-- ------------------------------------------------------
-- Server version 5.7.17

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `catalogo_reperti`
--

DROP TABLE IF EXISTS `catalogo_reperti`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `catalogo_reperti` (
  `UT` varchar(45) COLLATE utf16_unicode_ci NOT NULL,
  `Id_Reperto` varchar(45) COLLATE utf16_unicode_ci NOT NULL,
  `Documentazione` text COLLATE utf16_unicode_ci,
  PRIMARY KEY (`Id_Reperto`,`UT`),
  UNIQUE KEY `Id_Reperto_UNIQUE` (`Id_Reperto`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `catalogo_unita_topografiche`
--

DROP TABLE IF EXISTS `catalogo_unita_topografiche`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `catalogo_unita_topografiche` (
  `Id_UT` varchar(45) COLLATE utf16_unicode_ci NOT NULL,
  PRIMARY KEY (`Id_UT`),
  UNIQUE KEY `Id_UT_UNIQUE` (`Id_UT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

It's not entirely clear what you are asking. Are you trying to create a foreign key and get an error message, or does the foreign key disappear, or something else? — Henning Koehler, Nov 10 '18 at 11:10
The foreign key simply disappears from "referenced column". If I use the alter table command, it returns a generic 1215 error. — Discipulos, Nov 10 '18 at 11:12
@Discipulos take a look at the following link. It might helps you [MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint](https://stackoverflow.com/questions/18930084/mysql-error-1215-hy000-cannot-add-foreign-key-constraint) — mohabbati, Nov 10 '18 at 11:22
I have already checked the question, but all constraints here seem correct and the alter table command returns an error. — Discipulos, Nov 10 '18 at 11:38
Could be that Id_UT contains values that don't exist in the other table. The FK constraint would then be violated already when it is created. — GolezTrol, Nov 10 '18 at 12:04
BTW, there is no code in your question that creates an FK. Could you please add the code that causes the error? — GolezTrol, Nov 10 '18 at 12:05
Maybe something went wrong there? The question doesn't contain any code that creates an FK. — GolezTrol, Nov 10 '18 at 12:24
There is the SQL create table generating the structure which in Mysql workbench gives me the mentioned error. — Discipulos, Nov 10 '18 at 12:32
Your question still does not include the code that generates the foreign key. But it looks like you need an index on `UT` (as the first column). — Solarflare, Nov 10 '18 at 13:34
Because I am using the tool provided by Workbench (Figure 3). — Discipulos, Nov 10 '18 at 16:15
You need any index on `catalogo_reperti(UT)` (or one that starts with that column) for your foreign key to work. In general, you should check all your indexes: having both a unique index and the primary key on `Id_UT` is redundant (a primary key is already unique), and a unique key on `Id_Reperto`, when `(Id_Reperto,UT)` is your primary key might indicate that the primary key is incorrectly chosen (as just `Id_Reperto` would already make a primary key, so check if `Id_Reperto` is really unique or if `UT` should really be part of the primary key). — Solarflare, Nov 11 '18 at 14:54

score 0 · Answer 1 · answered Nov 10 '18 at 19:33

0

Forcing the declaration of Charset/collation (instead of leaving the default ones, which anyway are set to be the same) allows the program to declare the foreign key.

answered Nov 10 '18 at 19:33

Discipulos

423
8
23

MySQL Workbench - Error ERROR 1215 (HY000) with foreign key constraint

1 Answers1