无法在mysql中添加外键约束

问题描述

在应用外键时出现此无法添加外键约束"错误.

I am getting this "Cannot add foreign key constraint" error while applying foreign keys.

否则查询绝对可以正常工作.MySQL查询的语法似乎没有问题.

Otherwise the queries work absolutely OK. There seems to be no problem what so ever in the syntax of the MySQL query.

        //Department Table
                query = "CREATE TABLE IF NOT EXISTS department "
                    + "("
                    + "dept_id INT PRIMARY KEY AUTO_INCREMENT,"
                    + "dept_name VARCHAR(40) NOT NULL"
                    + ")ENGINE=InnoDB";
                command = new MySqlCommand(query, connection);
                command.ExecuteNonQuery();

                //Designation Table
                query = "CREATE TABLE IF NOT EXISTS designation "
                    + "("
                    + "desig_id INT PRIMARY KEY AUTO_INCREMENT,"
                    + "designation VARCHAR(40) NOT NULL"
                    + ")ENGINE=InnoDB";
                command = new MySqlCommand(query, connection);
                command.ExecuteNonQuery();

                // PayNature Table
                query = "CREATE TABLE IF NOT EXISTS paynature "
                    + "("
                    + "paynature_id INT PRIMARY KEY AUTO_INCREMENT,"
                    + "pay_nature VARCHAR(40) NOT NULL"
                    + ")ENGINE=InnoDB";
                command = new MySqlCommand(query, connection);
                command.ExecuteNonQuery();

                // Employee Type
                query = "CREATE TABLE IF NOT EXISTS employeetype "
                    + "("
                    + "emptype_id INT PRIMARY KEY AUTO_INCREMENT,"
                    + "emp_type VARCHAR(40) NOT NULL"
                    + ")ENGINE=InnoDB";
                command = new MySqlCommand(query, connection);
                command.ExecuteNonQuery();

                // Pay Scale Table
                query = "CREATE TABLE IF NOT EXISTS payscale "
                    + "("
                    + "payscale_id INT PRIMARY KEY AUTO_INCREMENT,"
                    + "payscale VARCHAR(40) NOT NULL, " // Optional
                    + "basic_salary DEC(10,3) NOT NULL"
                    + ")ENGINE=InnoDB";
                command = new MySqlCommand(query, connection);
                command.ExecuteNonQuery();

                // Employee Table
                query = "CREATE TABLE IF NOT EXISTS employee "
                    + "("
                    + "emp_id INT PRIMARY KEY AUTO_INCREMENT,"
                    + "emp_name VARCHAR(40) NOT NULL,"
                    + "emptype_id_fk VARCHAR(40)," //Optional
                    + "dept_id_fk INT,"    //Optional
                    + "desig_id_fk INT,"   //Optional
                    + "payscale_id_fk INT NOT NULL, "
                    + "INDEX(emptype_id_fk, dept_id_fk, desig_id_fk, payscale_id_fk),"

                    //Employee - R - EmployeeType
                    + "FOREIGN KEY(emptype_id_fk)"
                    + "REFERENCES employeetype(emptype_id)"
                    + "ON UPDATE CASCADE,"

                    // Employee - R - Department
                    + "FOREIGN KEY(dept_id_fk)"
                    + "REFERENCES department(dept_id)"
                    + "ON DELETE SET NULL ON UPDATE CASCADE,"


                    // Employee - R- Designation
                    + "FOREIGN KEY(desig_id_fk)"
                    + "REFERENCES designation(desig_id)"
                    + "ON DELETE SET NULL ON UPDATE CASCADE ,"

                    // Employee - R - Payscale
                    + "FOREIGN KEY(payscale_id_fk)"
                    + "REFERENCES payscale(payscale_id)"
                    + "ON DELETE CASCADE ON UPDATE CASCADE"
                    + ")ENGINE=InnoDB";

                command = new MySqlCommand(query, connection);
                command.ExecuteNonQuery();

推荐答案

只看了第一个.

"emptype_id_fk VARCHAR(40),"
.....
+ "FOREIGN KEY(emptype_id_fk)"
+ "REFERENCES employeetype(emptype_id)"
....

但是 employeetype 表的 emptype_id 字段被声明为 INT(11)

But the emptype_id field of the employeetype table is declared as an INT(11)

可能您希望 employee 表中的字段 emptype_id_fk 为INT.
进行此更改后，查询有效

Probably you want the field emptype_id_fk in the employee table to be an INT.
After making this change the query works

这篇关于无法在mysql中添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！