外键和MySQL错误 [英] Foreign Keys and MySQL Errors

问题描述

我有以下脚本在MySQL 5.1版中创建一个表，该表将引用其他3个表.所有3个表都是使用InnoDB创建的，并且所有3个表的ID列均定义为INT.

I have the following script to create a table in MySQL version 5.1 which is to refer to 3 other tables. All 3 tables have been created using InnoDB, and all 3 tables have the ID column defined as INT.

我已经成功创建了其他引用ACCOUNT和PERSON的表，但是，这是第一个引用ADDRESS的表，因此下面也包括了该表的定义.

I have created other tables successfully which reference ACCOUNT and PERSON, however, this is the first table which references ADDRESS, so I've included the definition for that table, as run, below as well.

我得到的错误是errno 150出现的错误1005(HY000)，我知道这与外键创建有关.

The error which I'm getting is ERROR 1005 (HY000) with errno 150, which I understand to be relating to foreign key creation.

失败的脚本是(为简单起见，删除了额外的列):

The script which fails is (extra columns removed for simplicity):

CREATE TABLE WORK_ORDER (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ACCOUNT_ID INT NOT NULL,
    CUSTOMER_ID INT NOT NULL,
    SALES_ID INT,
    TRADES_ID INT,
    LOCATION_ID INT NOT NULL,
    INDEX CUST_INDEX(CUSTOMER_ID),
    INDEX SALES_INDEX(SALES_ID),
    INDEX TRADES_INDEX(TRADES_ID),
    INDEX ACCOUNT_INDEX(ACCOUNT_ID),
    INDEX LOCATION_INDEX(LOCATION_ID),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES PERSON(ID) ON DELETE CASCADE,
    FOREIGN KEY (SALES_ID) REFERENCES PERSON(ID) ON DELETE SET NULL,
    FOREIGN KEY (TRADES_ID) REFERENCES PERSON(ID) ON DELETE SET NULL,
    FOREIGN KEY (ACCOUNT_ID) REFERENCES ACCOUNT(ID) ON DELETE CASCADE,
    FOREIGN KEY (LOCATION_ID) REFERENCES ADDRESS(ID) ON DELETE SET NULL
) ENGINE=InnoDB;

下面是用于创建ADDRESS表的SQL语句(为简单起见，删除了其他列).

The SQL statement used to create the ADDRESS table is below (extra columns removed for simplicity).

CREATE TABLE ADDRESS (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    PERSON_ID INT NOT NULL,
    ACCOUNT_ID INT NOT NULL,
    ADDRESS_L1 VARCHAR(50),
    ADDRESS_L2 VARCHAR(50),
    CITY VARCHAR(25),
    PROVINCE VARCHAR(20),
    POSTAL_CODE VARCHAR(6),
    COUNTRY VARCHAR(25),
    INDEX CUST_INDEX(PERSON_ID),
    INDEX ACCOUNT_INDEX(ACCOUNT_ID),
    FOREIGN KEY (ACCOUNT_ID) REFERENCES ACCOUNT(ID) ON DELETE CASCADE,
    FOREIGN KEY (PERSON_ID) REFERENCES PERSON(ID) ON DELETE CASCADE
) ENGINE=InnoDB;

我在这里浏览了处理类似问题的几个问题，但大多数似乎是重复的定义和不匹配的字段类型，还有一些没有为一个或另一个表使用InnoDB.但是，这些似乎都不是问题.有什么想法吗?

I've browsed through several questions here dealing with similar issues, but most seem to be duplicate definitions and non-matching field types, as well as some not using InnoDB for one or the other of the tables. However, none of these seem to be the problem. Any ideas?

推荐答案

您始终可以发出"SHOW ENGINE INNODB STATUS"命令.输出中将包含最新外来键错误"部分，其中将详细介绍导致"150"错误的确切原因:

You can always issue a 'SHOW ENGINE INNODB STATUS' command. Buried in the output will be a "LATEST FOREIGN KEY ERROR" section, which will have more details on exactly what caused the '150' error:

mysql> create table a (x int not null) type=innodb;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table b (y int not null, foreign key (y) references a (x) on delete set null) type=innodb;
ERROR 1005 (HY000): Can't create table './test/b.frm' (errno: 150)

mysql> show engine innodb status;
[..... snip snip snip ...]
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091129 16:32:41 Error in foreign key constraint of table test/b:
foreign key (y) references a (x) on delete set null) type=innodb:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
[.... snip snip snip ...]

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