在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建立关系，则必须做两件事.首先，您必须在引用表的外键列上定义一个索引(在您的情况下为foo_bar.foo_id).然后，转到关系视图(在引用表中)并选择被引用的列(在您的情况下为foo.id)以及on update和on delete操作.

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屋！