Mysql table creation foreign key error solution

Mysql table creation foreign key error solution

Database Table A:

CREATE TABLE task_desc_tab
(
  id INT(11) PRIMARY KEY NOT NULL COMMENT 'Auto-increment primary key' AUTO_INCREMENT,
  <strong>taskname</strong> VARCHAR(200) NOT NULL COMMENT 'Task name',
  sqlname VARCHAR(20) NOT NULL COMMENT 'sql file name',
  params VARCHAR(5000) NOT NULL COMMENT 'Task parameters, in the format of a JSON string',
  updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Update time',
  detail VARCHAR(3000) COMMENT 'Some description information of the task, just for notes'
)
 ENGINE = InnoDB
 DEFAULT CHARSET = utf8;

Database B:

CREATE TABLE exec_plan_tab
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  <strong>taskname</strong> VARCHAR(200) NOT NULL,
  startdate DATE NOT NULL,
  enddate DATE NOT NULL,
  updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT exec_plan_tab_task_desc_tab_taskname_fk FOREIGN KEY (taskname) REFERENCES task_desc_tab (taskname)
)
 ENGINE = InnoDB
 DEFAULT CHARSET = utf8;

Goal: Create a table so that taskname in table B is a foreign key. Refer to the taskname field in table A. When creating the table, the following error is reported:

[2018-07-19 15:02:29] [HY000][150] Create table 'daxin/#sql-5d_30' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
[2018-07-19 15:02:29] [HY000][1215] Cannot add foreign key constraint
[2018-07-19 15:02:29] [HY000][1215] Cannot add foreign key constraint

After troubleshooting, we found the cause of the problem: taskname in table A must be modified with the UNIQUE field to ensure the uniqueness of the record and avoid ambiguity when referenced by table B.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • MySQL creates three relationship tables in practice
  • How to quickly create a test data table with 8 million entries in MySQL
  • MySQL and PHP basics and application topics: creating database tables
  • Python saves dict dictionary type data to Mysql and automatically creates tables and columns
  • MySQL learning to create and operate databases and table DDL for beginners
  • The first step in getting started with MySQL database is to create a table
  • How to create a table in mysql and add field comments
  • MySQL create table operation command sharing

<<:  js to achieve sliding carousel effect

>>:  Use of Linux crontab command

Recommend

Example tutorial on using the sum function in MySQL

Introduction Today I will share the use of the su...

How to support Webdings fonts in Firefox

Firefox, Opera and other browsers do not support W...

Common ways to optimize Docker image size

The Docker images we usually build are usually la...

Solutions to Mysql index performance optimization problems

The optimization created by MySQL is to add index...

Use of docker system command set

Table of contents docker system df docker system ...

Windows platform configuration 5.7 version + MySQL database service

Includes the process of initializing the root use...

Explanation of the usage of replace and replace into in MySQL

MySQL replace and replace into are both frequentl...

Introduction to the use of anchors (named anchors) in HTML web pages

The following information is compiled from the Int...

A detailed tutorial on how to install Jenkins on Docker for beginners

Jenkins is an open source software project. It is...

Teach you how to use Portainer to manage multiple Docker container environments

Table of contents Portainer manages multiple Dock...

Pure CSS to achieve the effect of picture blinds display example

First, let me show you the finished effect Main i...