MySQL：INDEX，UNIQUE，FOREIGN KEY和PRIMARY KEY之间有什么区别？

Ok, so i'm a newbie here at SQL..

好的，所以我是SQL的新手..

i'm settings up my tables, and i'm getting confused on indexes, keys, foreign keys..

我正在设置我的表，我对索引，键，外键感到困惑。

I have a users table, and a projects table

我有一个用户表和一个项目表

I want to use the users (id) to attach a project to a user.

我想使用users（id）将项目附加到用户。

this is what I have so far.

这就是我到目前为止所拥有的。

DROP TABLE IF EXISTS projects;

CREATE TABLE projects (
    id int(8) unsigned NOT NULL,
    user_id int(8),
    name varchar(120) NOT NULL,
    description varchar(300),
    created_at date,
    updated_at date,
    PRIMARY KEY (id),
    KEY users_id (user_id)
) ENGINE=InnoDB;

ALTER TABLE projects (
    ADD CONSTRAINT user_projects,
    FOREIGN KEY (user_id) REFERENCES users(id),
    ON DELETE CASCADE
)

So what i'm getting lost on is what is the differences between a key, an index, a constraint and a foreign key?

所以我迷失的是密钥，索引，约束和外键之间的区别是什么？

I've been looking online and can't find a newb explanation for it.

我一直在网上看，无法找到新的解释。

ps. i'm using phpactiverecord and have the relationships set up in the models

PS。我正在使用phpactiverecord并在模型中设置关系

user-> has_many('projects'); projects -> belongs_to('user');

user-> has_many（'projects'）; projects - > belongs_to（'user'）;

not sure if that has anything to do with it, but thought i'd throw it in there..

不确定这是否与它有任何关系，但我想我会把它丢在那里..

Thanks.

谢谢。

EDIT:

编辑：

I thought it could possible be something to do with navicat, so I went into mamps phpmyadmin and ran this...

我认为这可能与navicat有关，所以我进入mamps phpmyadmin然后运行这个...

DROP TABLE IF EXISTS projects;

DROP TABLE IF EXISTS项目;

CREATE TABLE projects (
    id int(8) unsigned NOT NULL,
    user_id int(8) NOT NULL,
    name varchar(120) NOT NULL,
    description varchar(300),
    created_at date,
    updated_at date,
    PRIMARY KEY (id),
    KEY users_id (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

Still nothing... :(

依然没有... ：（

2 个解决方案

#1

Expanding on Shamil's answers:

扩展Shamil的答案：

INDEX is similar to the index at the back of a book. It provides a simplified look-up for the data in that column so that searches on it are faster. Fun details: MyISAM uses a hashtable to store indexes, which keys the data, but is still linearly proportional in depth to the table size. InnoDB uses a B-tree structure for its indexes. A B-tree is similar to a nested set - it breaks down the data into logical child groups, meaning search depth is significantly smaller. As such, lookups by ranges are faster in a InnoDB, whereas lookups of a single key are faster in MyISAM (try to remember the Big O of hashtables and binary trees).

INDEX类似于书后面的索引。它为该列中的数据提供了简化的查找，以便对其进行搜索更快。有趣的细节：MyISAM使用哈希表来存储索引，这些索引对数据进行键入，但在深度上与表大小成线性比例。 InnoDB为其索引使用B树结构。 B树类似于嵌套集 - 它将数据分解为逻辑子组，这意味着搜索深度要小得多。因此，在InnoDB中按范围查找更快，而在MyISAM中查找单个密钥更快（尝试记住哈希表和二叉树的大O）。

UNIQUE INDEX is an index in which each row in the database must have a unique value for that column or group of columns. This is useful for preventing duplication, e.g. for an email column in a users table where you want only one account per email address. Important note that in MySQL, an INSERT... ON DUPLICATE KEY UPDATE statement will execute the update if it finds a duplicate unique index match, even if it's not your primary key. This is a pitfall to be aware of when using INSERT... UPDATE statements on tables with uniques. You may wind up unintentionally overwriting records! Another note about Uniques in MySQL - per the ANSI-92 standard, NULL values are not to be considered unique, which means you can have multiple NULL values in a nullable unique-indexed column. Although it's a standard, some other RDBMSes differ on implementation of this.

UNIQUE INDEX是一个索引，其中数据库中的每一行必须具有该列或列组的唯一值。这对于防止重复是有用的，例如对于用户表格中的电子邮件列，您希望每个电子邮件地址只有一个帐户。重要提示，在MySQL中，INSERT ... ON DUPLICATE KEY UPDATE语句将在找到重复的唯一索引匹配时执行更新，即使它不是您的主键。当对具有唯一身份用户的表使用INSERT ... UPDATE语句时，这是一个需要注意的陷阱。你可能会无意中覆盖记录！关于MySQL中Uniques的另一个注释 - 根据ANSI-92标准，NULL值不被认为是唯一的，这意味着您可以在可为空的唯一索引列中具有多个NULL值。虽然它是标准，但其他一些RDBMS在实现方面也有所不同。

PRIMARY KEY is a UNIQUE INDEX that is the identifier for any given row in the table. As such, it must not be null, and is saved as a clustered index. Clustered means that the data is written to your filesystem in ascending order on the PK. This makes searches on primary key significantly faster than any other index type (as in MySQL, only the PK may be your clustered index). Note that clustering also causes concerns with INSERT statements if your data is not AUTO_INCREMENTed, as MySQL will have to shift data around on the filesystem if you insert a new row with a PK with a lower ordinal value. This could hamper your DB performance. So unless you're certain you know what you're doing, always use an auto-incremented value for your PK in MySQL.

PRIMARY KEY是一个UNIQUE INDEX，它是表中任何给定行的标识符。因此，它不能为null，并保存为聚簇索引。群集意味着数据在PK上按升序写入文件系统。这使得对主键的搜索速度明显快于任何其他索引类型（如在MySQL中，只有PK可能是您的聚簇索引）。请注意，如果您的数据不是AUTO_INCREMENTed，则群集也会引起INSERT语句问题，因为如果您插入具有较低序数值的PK的新行，MySQL将不得不在文件系统上移动数据。这可能会妨碍您的数据库性能。因此，除非您确定自己知道自己在做什么，否则请始终在MySQL中使用自动递增的值。

FOREIGN KEY is a reference to a column in another table. It enforces Referential Integrity, which means that you cannot create an entry in a column which has a foreign key to another table if the entered value does not exist in the referenced table. In MySQL, a FOREIGN KEY does not improve search performance. It also requires that both tables in the key definition use the InnoDB engine, and have the same data type, character set, and collation.

FOREIGN KEY是对另一个表中的列的引用。它强制执行参照完整性，这意味着如果引用的表中不存在输入的值，则无法在具有另一个表的外键的列中创建条目。在MySQL中，FOREIGN KEY不会提高搜索性能。它还要求密钥定义中的两个表都使用InnoDB引擎，并且具有相同的数据类型，字符集和排序规则。

#2

KEY is just another word for INDEX.

KEY只是INDEX的另一个词。

A UNIQUE index means that all values within that index must be unique, and not the same as ant other within that index. An example would be an Id column in a table.

UNIQUE索引意味着该索引中的所有值必须是唯一的，并且与该索引中的其他值不同。一个例子是表格中的Id列。

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL, i.e, all values in the index must be set. Ideally, each table should have (and can have) one primary key only.

PRIMARY KEY是一个唯一索引，其中所有键列必须定义为NOT NULL，即必须设置索引中的所有值。理想情况下，每个表应该只有一个主键（并且可以有）。

A FOREIGN KEY is a referential constraint between two tables. This column/index must have the same type and length as the referred column within the referred table. An example of a FOREIGN KEY is a userId, between a user-login table and a users table. Note that it usually points to a PRIMARY KEY in the referred table.

FOREIGN KEY是两个表之间的引用约束。此列/索引必须与引用表中的引用列具有相同的类型和长度。 FOREIGN KEY的一个示例是userId，位于用户登录表和users表之间。请注意，它通常指向引用表中的PRIMARY KEY。

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

#1