关于mysql表创建的几个问题 [英] Couple of questions on mysql table creation

问题描述

CREATE TABLE favorite_food(
    person_id SMALLINT UNSIGNED,
    food VARCHAR(20),
    CONSTRAINT pk_favorite_food PRIMARY KEY(person_id,food),
    CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
    REFERENCES person (person_id)
);

我是MySQL的新手，完全来自 HTML和JS背景.我在这里有几个问题.

I am new to MySQL and coming purely from HTML and JS background. I have couple of questions here.

1. 为什么我们需要给CONSTRAINT一个像"pk_favorite_food"这样的名称.它的目的和用途是什么?
2. 参考是什么意思?
3. CONSTRAINT pk_favorite_food PRIMARY KEY(person_id,food),为什么我们在这里添加两列作为主键.

1. Why do we need to give a CONSTRAINT a name like "pk_favorite_food". What is the purpose and use behind it?
2. What does the REFERENCES mean?
3. CONSTRAINT pk_favorite_food PRIMARY KEY(person_id,food), Why do we add two columns as Primary Key here.

推荐答案

第一

为什么我们需要给CONSTRAINT一个像"pk_favorite_food"的名字.它的目的和用途是什么?

Why do we need to give a CONSTRAINT a name like "pk_favorite_food". What is the purpose and use behind it?

不需要要求为约束指定名称，如果没有，则MySQL会在内部为您分配一个名称.它只是一个标识符，这使得以后更容易修改或删除约束.

You are not required to assign a name to the constraint, and if you do not, MySQL will assign one internally for you. It is merely an identifier, which makes it easier to modify or drop the constraint later.

如果创建的表没有该约束名称，然后执行SHOW CREATE TABLE favodite_food;，您将看到一个名称，该名称与上面定义的名称有些相似，但由MySQL自动分配.自动创建的标识符可能类似于主键idx_favorite_food和FOREIGN KEY约束favorite_food_ibfk_1.

If you create the table without that constraint name and then do SHOW CREATE TABLE favodite_food;, you will see a name somewhat similar to the one you have defined above, but assigned automatically by MySQL. The automatically created identifiers would likely be something like idx_favorite_food for the primary key and favorite_food_ibfk_1 for the FOREIGN KEY constraint.

REFERENCES是什么意思?

REFERENCES是在FOREIGN KEY约束中使用的关键字，用于指示FOREIGN KEY列引用哪个表中的列.阅读

REFERENCES is a keyword used in FOREIGN KEY constraints to indicate which table the FOREIGN KEY column refers to a column in. Read the MySQL reference on FOREIGN KEY constraints for full syntactic details. In your example, it looks a little strange because it appears on its own line. Really, it belongs to the FOREIGN KEY definition.

如果您在它所属的行上看到它，可能会更清楚一些

It may be a little clearer if you see it on the line it belongs to:

/* REFERENCES is a component of this FOREIGN KEY definition */
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)

第三

CONSTRAINT pk_favorite_food PRIMARY KEY(person_id,food)，为什么我们在这里添加两列作为主键.

CONSTRAINT pk_favorite_food PRIMARY KEY(person_id,food), Why do we add two columns as Primary Key here.

第2部分PRIMARY KEY被称为复合键.当您不需要单个标识列(如自动增量值)时使用它，但是这两列的组合在表中必须是唯一的，因此也可以唯一地标识表中的行.因此，在上面的示例中，它强制执行以下事实:任何person_id不能有两个具有相同food的行:

The 2 part PRIMARY KEY is called a composite key. It is used when you don't need a single identifying column (like an auto-increment value), but the combination of those two columns must be unique in the table and can therefore also uniquely identify the row in the table. So in the example you have above, it enforces the fact that any person_id cannot have two rows with the same food:

INSERT INTO favorite_food (person_id, food) VALUES (1, 'apples'); /* works */
INSERT INTO favorite_food (person_id, food) VALUES (1, 'bananas'); /* works */
INSERT INTO favorite_food (person_id, food) VALUES (1, 'apples'); /* Fails!  Person 1 already has apples and the pair must be unique */

这篇关于关于mysql表创建的几个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！