MySQL中的外键创建问题

I have followed this article: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html to create a foreign key between two tables. Every single attempt fails. Is there anything that I am missing?!

我已经按照这篇文章:http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html在两个表之间创建了一个外键。每一次尝试都失败了。有什么我想念的吗?!

It's really frustrating and I never expected to encounter this issue at all!

这真的令人沮丧,我从没想过会遇到这个问题!

Thanks.

4 个解决方案

#1

You don't show what you tried or what the error was, so all of the answers are only guesswork.

您没有显示您尝试的内容或错误的内容,因此所有答案都只是猜测。

Here's a checklist of things that must be true before foreign keys can work:

这是在外键可以工作之前必须为真的事项清单:

InnoDB storage engine must be enabled.

必须启用InnoDB存储引擎。
```
mysql> SHOW VARIABLES LIKE 'have_innodb';
```
Both tables must use the InnoDB storage engine (MyISAM doesn't support referential integrity constraints).

两个表都必须使用InnoDB存储引擎(MyISAM不支持参照完整性约束)。
```
mysql> SHOW CREATE TABLE <parent_table>;
mysql> SHOW CREATE TABLE <child_table>;
```
The column(s) in the referenced table must be the leftmost column(s) of a key. Typically this is the PRIMARY KEY, but InnoDB actually allows them to be part of any key.

引用表中的列必须是键的最左列。通常这是PRIMARY KEY,但InnoDB实际上允许它们成为任何键的一部分。
The foreign key column and its referenced primary key column must be of exactly the same data type (must match signed vs. unsigned, int vs. bigint, string charset, etc.). Only the string length is allowed to be different.

外键列及其引用的主键列必须具有完全相同的数据类型(必须匹配signed与unsigned,int与bigint,字符串charset等)。只允许字符串长度不同。
You have to get the constraint declaration syntax right. :-)

您必须正确获取约束声明语法。 :-)
If you're adding a constraint to a populated table, all the existing values must satisfy the constraint (thanks to Jordan S. Jones' answer on this thread).

如果要向填充表添加约束,则所有现有值必须满足约束(感谢Jordan S. Jones在此线程中的答案)。

#2

What exactly is going wrong? We can't really help you without details...

究竟出了什么问题?没有细节,我们无法真正帮助你......

mysql> CREATE TABLE parent (id INT NOT NULL,
    ->                      PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE child (id INT, parent_id INT,
    ->                     INDEX par_ind (parent_id),
    ->                     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                       ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

Works fine for me.

对我来说很好。

#3

One thing you have to remember, that if you're child table/column has values that don't exist in the parent table/column, you will always get an error.

您必须记住的一件事是,如果您的子表/列具有父表/列中不存在的值,您将始终收到错误。

#4

Example:

use trading_research;
drop table if exists stock_history;
create table stock_history
(
        company_id integer(4) NOT NULL,
        price_date date NOT NULL,
        primary key(company_id, price_date),
        opening_price decimal(10,2) NULL,
        closing_price decimal(10,2) NULL,
        high_price decimal(10,2) NULL,
        low_price decimal(10,2) NULL,
        adjusted_closing_price decimal(10,2) NULL,
        volume decimal(20, 2) NULL,
        constraint foreign key (company_id) references stock_company (company_id) on delete cascade on update cascade
);

If you give details of what you're trying and/or the errors you get, I can help more.

如果你详细说明你正在尝试什么和/或你得到的错误,我可以提供更多帮助。

#1