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

JavaScript to achieve simple tab bar switching case

This article shares the specific code for JavaScr...

How to use Docker plugin to remotely deploy projects to cloud servers in IDEA

1. Open port 2375 Edit docker.service vim /lib/sy...

Implementation of debugging code through nginx reverse proxy

background Now the company's projects are dev...

Detailed explanation of how to use the vue3 Teleport instant movement function

The use of vue3 Teleport instant movement functio...

How to improve MySQL Limit query performance

In MySQL database operations, we always hope to a...

Analysis of permissions required to run docker

Running Docker requires root privileges. To solve...

Summary of the three stages of visual designer growth

Many people have read this book: "Grow as a ...

React diff algorithm source code analysis

Table of contents Single Node Diff reconcileSingl...

Mysql case analysis of transaction isolation level

Table of contents 1. Theory SERIALIZABLE REPEATAB...

Causes and solutions for slow MySQL query speed and poor performance

1. What affects database query speed? 1.1 Four fa...