MySQL 在 phpMyAdmin 中设置外键？

Setting up foreign keys in phpMyAdmin?

提问by Nathan Long

I'm setting up a database using phpMyAdmin. I have two tables (fooand 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.

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

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 idfields are INT(11).

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

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

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

What am I missing?

我错过了什么？

Clarification/Update

澄清/更新

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.

为简单起见，我想继续使用 phpMyAdmin。我目前正在使用 XAMPP，它很容易让我专注于 PHP/CSS/Javascript，并且它带有 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;

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

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

回答by rael_kid

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.

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

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.

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

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

编辑：确保两个表都选择了 InnoDB 引擎。

回答by awais

phpMyAdmin lets you define foreign keys using their "relations" view. But since, MySQL only supports foreign constraints on "INNO DB" tables, the first step is to make sure the tables you are using are of that type.

phpMyAdmin 允许您使用它们的“关系”视图定义外键。但是，由于 MySQL 仅支持对“INNO DB”表的外部约束，因此第一步是确保您使用的表属于该类型。

To setup a foreign key so that the PID column in a table named CHILD references the ID column in a table named PARENT, you can do the following:

要设置外键以便名为 CHILD 的表中的 PID 列引用名为 PARENT 的表中的 ID 列，您可以执行以下操作：

1. For both tables, go to the operations tab and change their type to "INNO DB"
2. Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
3. In the CHILD table, define an index for the PID column.
4. While viewing the structure tab of the CHILD table, click the "relation view" link just above the "add fields" section.
5. You will be given a table where each row corresponds to an indexed column in your CLIENT table. The first dropdown in each row lets you choose which TABLE->COLUMN the indexed column references. In the row for PID, choose PARENT->ID from the dropdown and click GO.

1. 对于这两个表，转到操作选项卡并将其类型更改为“INNO DB”
2. 确保 ID 是 PARENT 表的主键（或至少是索引列）。
3. 在 CHILD 表中，为 PID 列定义一个索引。
4. 在查看 CHILD 表的结构选项卡时，单击“添加字段”部分正上方的“关系视图”链接。
5. 您将获得一个表，其中每一行都对应于 CLIENT 表中的一个索引列。每行中的第一个下拉列表让您选择索引列引用的 TABLE->COLUMN。在 PID 行中，从下拉列表中选择 PARENT->ID，然后单击 GO。

By doing an export on the CHILD table, you should see a foreign key constraint has been created for the PID column.

通过在 CHILD 表上进行导出，您应该看到已为 PID 列创建了一个外键约束。

回答by Brett

This is a summary of a Wikipedia article. It specifies the different types of relationships you can stipulate in PHPmyadmin. I am putting it here because it is relevant to @Nathan's comment on setting the foreign keys options for "on update/delete" but is too large for a comment - hope it helps.

这是维基百科文章的摘要。它指定了您可以在 PHPmyadmin 中规定的不同类型的关系。我把它放在这里是因为它与@Nathan 关于为“更新/删除”设置外键选项的评论有关，但对于评论来说太大了 - 希望它有所帮助。

CASCADE

级联

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

每当主（引用）表中的行被删除（相应更新）时，具有匹配外键列的子（引用）表的相应行也将被删除（相应更新）。这称为级联删除（或更新[2]）。

RESTRICT

严格

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

当外键表中存在引用被引用表中的值的行时，无法更新或删除值。同样，只要有来自外键表的引用，就不能删除行。

NO ACTION

没有行动

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

NO ACTION 和 RESTRICT 非常相似。NO ACTION 和 RESTRICT 之间的主要区别在于，NO ACTION 会在尝试更改表后进行参照完整性检查。RESTRICT 在尝试执行 UPDATE 或 DELETE 语句之前进行检查。如果引用完整性检查失败，两个引用操作的行为相同：UPDATE 或 DELETE 语句将导致错误。

SET NULL

置空

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

更新或删除引用行时，引用行中的外键值将设置为 NULL。仅当引用表中的相应列可以为空时，这才是可能的。由于 NULL 的语义，在外键列中具有 NULL 的引用行不需要引用行。

SET DEFAULT

默认设置

Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.

与 SET NULL 类似，当引用行被更新或删除时，引用行中的外键值被设置为列默认值。

回答by Nishad Up

In phpmyadmin, you can assign Foreign key simply by its GUI. Click on the table and go to Structure tab. find the Relation View on just bellow of table (shown in below image).

在 phpmyadmin 中，您可以简单地通过其 GUI 分配外键。单击表格并转到“结构”选项卡。在表格下方找到关系视图（如下图所示）。

You can assign the forging key from the list box near by the primary key.(See image below). and save

您可以从主键附近的列表框中分配锻造键。（见下图）。并保存

corresponding SQL query automatically generated and executed.

自动生成并执行相应的 SQL 查询。

回答by user2060451

For those new to database .... and need to ALTER an existing table. A lot things seem to be pretty straightforward, but there is always something ... between A and B.

对于那些不熟悉数据库的人......并且需要更改现有表。很多事情似乎很简单，但总有一些东西……在 A 和 B 之间。

Before anything else, take a look at this.

在此之前，先看看这个。

1. Make sure you have P_ID (parent ID on both parent and child table).
2. Of course it will be already filled in the parent. Not necessarily in the child in a true and final way. So for instance P_ID #3 (maybe many times in the child table will be pointing to original P_ID at parent table).

3. Go to SQL tab (I am using phpMyAdmin, should be similar in other ones) and do this command:

   ALTER TABLE child_table_name    
   ADD FOREIGN KEY (P_ID)   
   REFERENCES parent_table_name (P_ID)
   

4. Click on child table, than structure, finally on relational view. Finish your DB planning there. There was a nice answer before this one about cascade, restrict, etc. Of course it could be done by commands...

1. 确保您有 P_ID（父表和子表上的父 ID）。
2. 当然，它已经在父级中填充了。不一定以真实和最终的方式在孩子身上。因此，例如 P_ID #3（子表中可能多次指向父表中的原始 P_ID）。

3. 转到 SQL 选项卡（我正在使用 phpMyAdmin，在其他选项卡中应该类似）并执行以下命令：

   ALTER TABLE child_table_name    
   ADD FOREIGN KEY (P_ID)   
   REFERENCES parent_table_name (P_ID)
   

4. 单击子表，然后单击结构，最后单击关系视图。在那里完成您的数据库规划。在这个关于级联、限制等之前有一个很好的答案。当然可以通过命令来完成......

回答by Shafeeq M kunjumoideen

Foreign key means a non prime attribute of a table referes the prime attribute of another*in phpMyAdmin* first set the column you want to set foreign key as an index

外键是指一个表的非主属性引用另一个*在phpMyAdmin中的主属性* 首先设置要设置外键的列作为索引

then click on RELATION VIEW

然后点击关系视图

there u can find the options to set foreign key

在那里你可以找到设置外键的选项

回答by markus

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

InnoDB 允许您使用 ALTER TABLE 向表添加新的外键约束：

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

On the other hand, if MyISAM has advantages over InnoDB in your context, why would you want to create foreign key constraints at all. You can handle this on the model level of your application. Just make sure the columns which you want to use as foreign keys are indexed!

另一方面，如果 MyISAM 在您的上下文中比 InnoDB 有优势，那么您为什么要创建外键约束呢？您可以在应用程序的模型级别处理此问题。只需确保要用作外键的列已编入索引！

回答by pouya

Don't forget that the two columns should have the same data type.

不要忘记这两列应该具有相同的数据类型。

for example if one column is of type INT and the other is of type tinyint you'll get the following error:

例如，如果一列的类型为 INT 而另一列的类型为 tinyint，您将收到以下错误：

Error creating foreign key on [PID column] (check data types)

在 [PID 列] 上创建外键时出错（检查数据类型）

回答by Vinod

Step 1: You have to add the line: default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your OS) and restart the mysqld service.

步骤 1：您必须在 mysql 配置文件（my.cnf 或 my.ini 取决于您的操作系统）的 [mysqld] 部分下添加行：default-storage-engine = InnoDB 并重新启动 mysqld 服务。

Step 2: Now when you create the table you will see the type of table is: InnoDB

第 2 步：现在当你创建表时，你会看到表的类型是：InnoDB

Step 3: Create both Parent and Child table. Now open the Child table and select the column U like to have the Foreign Key: Select the Index Key from Action Label as shown below.

第 3 步：创建父表和子表。现在打开子表并选择列 U 喜欢有外键：从操作标签中选择索引键，如下所示。

Step 4: Now open the Relation View in the same child table from bottom near the Print View as shown below.

第 4 步：现在从靠近打印视图的底部打开同一子表中的关系视图，如下所示。

Step 5: Select the column U like to have the Foreign key as Select the Parent column from the drop down. dbName.TableName.ColumnName

第 5 步：选择列 U 喜欢有外键作为从下拉列表中选择父列。dbName.TableName.ColumnName

Select appropriate Values for ON DELETE and ON UPDATE

为 ON DELETE 和 ON UPDATE 选择适当的值

回答by Sahil Ralkar

First set Storage Engine as InnoDB

首先将存储引擎设置为InnoDB

then the relation viewoption enable in structure menu

然后在结构菜单中启用关系视图选项