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

Vue implements multiple ideas for theme switching

Table of contents Dynamically change themes The f...

MySQL trigger trigger add, delete, modify and query operation example

This article uses examples to describe the add, d...

Detailed tutorial on installing MySQL 5.7.20 on RedHat 6.5/CentOS 6.5

Download the rpm installation package MySQL offic...

Architecture and component description of docker private library Harbor

This article will explain the composition of the ...

A practical record of troubleshooting a surge in Redis connections in Docker

On Saturday, the redis server on the production s...

How to delete all contents in a directory using Ansible

Students who use Ansible know that Ansible only s...

Summarize the problems encountered in using Vue Element UI

Table of contents 1. DateTimePicker date selectio...

CSS easily implements fixed-ratio block-level containers

When designing H5 layout, you will usually encoun...

Solution to the problem that Centos8 cannot install docker

Problem [root@zh ~]# [root@zh ~]# [root@zh ~]# yu...

How to generate a unique server-id in MySQL

Preface We all know that MySQL uses server-id to ...

10 Popular Windows Apps That Are Also Available on Linux

According to data analysis company Net Market Sha...