php 如何在phpmyadmin中创建外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37615586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a foreign key in phpmyadmin
提问by laurajs
I want to make doctorid a foreign key in my patient table.
我想让我的病人表中的doctorid 成为外键。
So I have all of my tables created - the main problem is that when I go to the table > structure > relation view only the primary key comes up that I can create a foreign key (and it is already the primary key of the certain table that I want to keep - i.e Patient table patient is enabled to be changed but the doctor Id -I have a doctor table also- is not enabled).
所以我创建了我所有的表 - 主要问题是当我转到表>结构>关系视图时，只有主键出现，我可以创建一个外键（它已经是某个表的主键我想保留的 - 即患者表患者可以更改，但医生 ID - 我也有医生表 - 未启用）。
I have another table with two composite keys (medicineid and patientid) in relation view it enables me to change both
我在关系视图中有另一个带有两个复合键（medicineid 和patientid）的表，它使我能够同时更改两者
Do I have to chance the index of doctor ID in patient table to something else? both cannot be primary keys as patient ID is the primary for the patient table - doctor is the foreign.
我是否必须将患者表中医生 ID 的索引改为其他内容？两者都不能是主键，因为患者 ID 是患者表的主键 - 医生是外键。
I hope anyone can help
我希望任何人都可以提供帮助
Kind regards
亲切的问候
回答by Jon Story
You can do it the old fashioned way... with an SQL statement that looks something like this
你可以用老式的方式来做……用一个看起来像这样的 SQL 语句
ALTER TABLE table_name
ADD CONSTRAINT fk_foreign_key_name
FOREIGN KEY (foreign_key_name)
REFERENCES target_table(target_key_name);
This assumes the keys already exist in the relevant table
这假设相关表中已经存在键
回答by Alok Patel
The key must be indexed to apply foreign key constraint. To do that follow the steps.
键必须被索引以应用外键约束。要做到这一点，请按照以下步骤操作。
- Open table structure. (2nd tab)
- See the last column action where multiples action options are there. Click on Index, this will make the column indexed.
- Open relation view and add foreign key constraint.
- 开放式表结构。（第二个标签）
- 查看有多个操作选项的最后一列操作。单击Index，这将使列索引。
- 打开关系视图并添加外键约束。
You will be able to assign DOCTOR_IDas foreign now.
您现在可以将DOCTOR_ID指定为外来的。
回答by Sky Games Inc
To be able to create a relation, the table Storage Enginemust be InnoDB. You can edit in Operationstab.
为了能够创建关系，表存储引擎必须是InnoDB。您可以在操作选项卡中进行编辑。
Then, you need to be sure that the idcolumn in your main table has been indexed. It should appear at Indexsection in Structuretab.
然后，您需要确保主表中的id列已被索引。它应该出现在“结构”选项卡的“索引”部分。
Finally, you could see the option Relations Viewin Structuretab. When edditing, you will be able to select the parent column in foreign table to create the relation.
最后，您可以在“结构”选项卡中看到“关系视图”选项。编辑时，您将能够选择外部表中的父列来创建关系。
See attachments. I hope this could be useful for anyone.
见附件。我希望这对任何人都有用。
回答by Yagnik Detroja
When you create table than you can give like follows.
当您创建表时，您可以给出如下所示。
CREATE TABLE categories(
cat_id int not null auto_increment primary key,
cat_name varchar(255) not null,
cat_description text
) ENGINE=InnoDB;
CREATE TABLE products(
prd_id int not null auto_increment primary key,
prd_name varchar(355) not null,
prd_price decimal,
cat_id int not null,
FOREIGN KEY fk_cat(cat_id)
REFERENCES categories(cat_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
)ENGINE=InnoDB;
and when after the table create like this
当表创建后这样
ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;
Following on example for it.
以它为例。
CREATE TABLE vendors(
vdr_id int not null auto_increment primary key,
vdr_name varchar(255)
)ENGINE=InnoDB;
ALTER TABLE products
ADD COLUMN vdr_id int not null AFTER cat_id;
To add a foreign key to the products table, you use the following statement:
要将外键添加到 products 表，请使用以下语句：
ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;
For drop the key
为了放下钥匙
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;
Hope this help to learn FOREIGN keys works
希望这有助于学习外键有效
