如何在MySQL中使用关键字'references'？

How is the references keyword used when creating a table?

创建表时如何使用references关键字？

Let's say I want to create two tables person and hobby and I want the hobby table id to reference the id of person?

假设我想创建两个表人和爱好，我希望爱好表id引用人的id？

person table
- id
- name

hobby
- id
- person_id
- hobby_name

How do I do that?

我怎么做？

5 个解决方案

#1

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))

#2

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;

#3

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.

references关键字用于定义在外键关系中使用的表和列。这意味着爱好表中的记录必须具有person表中存在的person_id，否则在插入时您将收到该键不存在的错误。

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时，上述删除将成功并自动删除链接到从主键表中删除的person_id的表爱好表中的任何和所有记录。

#4

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));

然后，看看这一行：FOREIGN KEY（person_id）REFERENCES person（person_id））;

Here person_id is the foreign key and it has come from person table and in that table it's name is person_id... That's it.

这里person_id是外键，它来自人员表，在那个表中，它的名字是person_id ......就是这样。

#5

-1

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, references allows us to to specify the target table column to which a foreign key refers.

就其含义而言，引用允许我们指定外键引用的目标表列。

#1