MySQL错误代码:1005 [英] MySQL Error Code: 1005

问题描述

我正在尝试将外键添加到我的表中，但是收到此错误. 错误代码:1005无法创建表'william.#sql-88c_3'(errno:150) 我有3张桌子.员工，客户和合同.

雇员[employee_no PK]，客户[customer_no PK]合同[contract_no PK] 我想将合同的外键作为合同[contract_no PK，employee_no FK]，customer_no FK]

我试图直接执行失败，现在我正在尝试alter语句.Alter脚本有什么问题吗?

    ALTER TABLE contract
    ADD CONSTRAINT `employee_no_fk2` FOREIGN KEY (`employee_no`) REFERENCES `employee` 
    (`employee_no`);


   ALTER TABLE contract
    ADD CONSTRAINT `Customer_no_fk2` FOREIGN KEY (`Customer_no`) REFERENCES `client` 
    (`Customer_no`);

解决方案

大多数此类错误将与数据类型缺少匹配有关..如果您可以通过这些链接..它可能会帮助您. 选中此 ...也对此进行检查

正如他们在第二个链接中所说:

您应该首先查看的是数据类型是否一致 在外键和主键列之间.

mysql> SHOW engine innodb STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

I am trying to add foreign keys to my table but receiving this error. Error Code: 1005 Can't create table 'william.#sql-88c_3' (errno: 150) I have 3 tables. employee, client and Contract.

employe [employee_no PK] , Client[customer_no PK] contract [contract_no PK] I want to have Foreign keys for contract as contract [contract_no PK, employee_no FK], customer_no FK]

I tried to do directly it failed, I am now trying the alter statement.Is anything wrong with the Alter script?

    ALTER TABLE contract
    ADD CONSTRAINT `employee_no_fk2` FOREIGN KEY (`employee_no`) REFERENCES `employee` 
    (`employee_no`);


   ALTER TABLE contract
    ADD CONSTRAINT `Customer_no_fk2` FOREIGN KEY (`Customer_no`) REFERENCES `client` 
    (`Customer_no`);

解决方案

Most of such error will be related to data type miss match or so.. If you could go through these links.. it might help you i guess.. Check-this ... also Check-this

As they say in the second link:

The first place you should look is whether the data types agree between the foreign key and primary key columns.

mysql> SHOW engine innodb STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

这篇关于MySQL错误代码:1005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！