在 phpMyAdmin 中设置外键? [英] Setting up foreign keys in phpMyAdmin?

问题描述

我正在使用 phpMyAdmin 设置数据库.我有两个表(foo 和 bar)，索引在它们的主键上.我正在尝试在它们之间创建一个关系表 (foo_bar)，使用它们的主键作为外键.

I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys.

我将这些表创建为 MyISAM，但此后将所有三个表都更改为 InnoDB，因为我读到 MyISAM 不支持外键.所有 id 字段都是 INT(11).

I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11).

当我选择 foo_bar 表时，单击关系视图"链接，并尝试将 FK 列设置为 database.foo.id 和 database.bar.id，它说 未定义索引！" 在每列旁边.

When I choose the foo_bar table, click the "relation view" link, and try to set the FK columns to be database.foo.id and database.bar.id, it says "No index defined!" beside each column.

我错过了什么?

为了简单起见，我想继续使用 phpMyAdmin.我目前正在使用 XAMPP，它很容易让我专注于 PHP/CSS/Javascript，而且它带有 phpMyAdmin.

For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin.

此外，虽然我还不能设置显式外键，但我确实有一个关系表并且可以执行这样的连接:

Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this:

SELECT * 
FROM foo 
INNER JOIN foo_bar 
ON foo.id = foo_bar.foo_id 
INNER JOIN bar
ON foo_bar.bar_id = bar.id;

没有在数据库中明确定义 FK 让我感到不舒服.

It just makes me uncomfortable not to have the FKs explicitly defined in the database.

推荐答案

如果你想使用 phpMyAdmin 建立关系，你必须做 2 件事.首先，您必须在引用表中的外键列上定义一个索引(在您的情况下为 foo_bar.foo_id).然后，转到关系视图(在引用表中)并选择引用列(在您的情况下为 foo.id)以及更新和删除操作.

If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.

我认为如果您有多个相互链接的表，外键很有用，特别是，如果您正确设置引用选项，您的删除脚本将变得非常短.

I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.

确保两个表都选择了 InnoDB 引擎.

Make sure both of the tables have the InnoDB engine selected.

这篇关于在 phpMyAdmin 中设置外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！