如何在 MySQL 中使用关键字“references”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2192280/
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 use the keyword 'references' in MySQL?
提问by sasori
How is the referenceskeyword used when creating a table?
references创建表时如何使用关键字？
Let's say I want to create two tables personand hobbyand I want the hobby table id to reference the id of person?
假设我想创建两个表person，hobby并且我希望爱好表 id 引用 person 的 id？
person table
- id
- name
hobby
- id
- person_id
- hobby_name
How do I do that?
我怎么做？
回答by H?vard S
Create the hobby table similarly to this:
与此类似地创建爱好表：
CREATE TABLE hobby (
id INT NOT NULL AUTO_INCREMENT,
person_id INT NOT NULL,
hobby_name VARCHAR(255),
PRIMARY KEY(id),
FOREIGN KEY(person_id) REFERENCES person(id))
回答by AJ.
Here is an example directly from MySQL website:
这是直接来自 MySQL 网站的示例：
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
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;
回答by RC.
CREATE TABLE person (person_id INT NOT NULL,
PRIMARY KEY (person_id));
CREATE TABLE hobby (hobby_id INT NOT NULL, person_id INT NOT NULL,
PRIMARY KEY(hobby_id),
FOREIGN KEY(person_id) REFERENCES person(person_id));
The references keyword is used to define which table and column is used in a foreign key relationship. This means that a record in the hobby table must have a person_id that exists in the person table or else at the time of insert you will receive an error that the key does not exist.
reference 关键字用于定义在外键关系中使用哪个表和列。这意味着 hobby 表中的记录必须有一个 person_id 存在于 person 表中，否则在插入时您将收到键不存在的错误。
To answer your question above about what "ON DELETE CASCADE" does, it allows you to delete a parent key record (in person) and it's corresponding children records (in hobby) without having to delete all the children records first.
要回答您上面关于“ON DELETE CASCADE”的作用的问题，它允许您删除父密钥记录（亲自）及其相应的子记录（业余爱好），而无需先删除所有子记录。
To clarify, if you have children records attached to a primary key entry and you attempt to delete the primary key entry like:
澄清一下，如果您将子记录附加到主键条目，并且您尝试删除主键条目，例如：
DELETE FROM person where person_id = 1;
without having the DELETE ON CASCADE, you would receive an error if any records in hobby had person_id's of 1. You would have delete all of those records first before doing the delete above. With DELETE ON CASCADE used, the above delete would succeed and automatically delete any and all records from table hobby table linked to the person_id being deleted from the primary key table.
在没有 DELETE ON CASCADE 的情况下，如果爱好中的任何记录的 person_id 为 1，您将收到错误。在执行上述删除之前，您将首先删除所有这些记录。使用 DELETE ON CASCADE，上述删除将成功并自动删除表 hobby 表中的任何和所有记录，该表链接到从主键表中删除的 person_id。
回答by Maniruzzaman Akash
Reference keyword is used actually to know where the foreign key has come. That means which is the table name and what is the name of this in that table.
引用关键字实际上是用来知道外键来自哪里。这意味着哪个是表名以及该表中的名称是什么。
I say this is correct :
我说这是正确的：
CREATE TABLE person (person_id INT NOT NULL,
PRIMARY KEY (person_id));
CREATE TABLE hobby (hobby_id INT NOT NULL, person_id INT NOT NULL,
PRIMARY KEY(hobby_id),
FOREIGN KEY(person_id) REFERENCES person(person_id));
Then, look at this line :
然后，看看这一行：
FOREIGN KEY(person_id) REFERENCES person(person_id));
Here person_idis the foreign key and it has come from persontable and in that table it's name is person_id...
That's it.
这person_id是外键，它来自person表，在该表中它的名称是person_id......就是这样。
回答by Abhi
Here is an example of how you can use it.
这是一个如何使用它的示例。
create table hobby(id int references person(id),person_id int,hobby_varchar(20), primary key(id));
For what it means, referencesallows us to to specify the target table column to which a foreign key refers.
对于它的含义，references允许我们指定外键引用的目标表列。
