请确认我使用主键和唯一索引 [英] Please confirm my use of primary key and unique index

问题描述

我想我理解主键和索引。

I think I understand primary keys and indexes.

在我的设置中，我有一个包含多个列的表。其中两列是用户ID和用户名。
理想情况下，我希望两者都是唯一的，并且不可为空。

In my setup, I have a table with several columns. Two of these columns are User ID, and Username. Ideally I would like both to be unique, and non nullable.

据我所知，我最好的用途是将用户ID设为主键，因为这是最重要的字段，不是NULL，并且它永远不会随着数据库的增长而改变。

As far as I can tell, my best use would be to have the User ID as the primary key, as this is the most important field not to NULL, and it will never change as the database grows.

然后我必须将用户名列作为一个唯一的索引，所以它可以在另一行上相同，虽然不幸的是，可能最终为NULL。

I would then have to have the username column as a unique index, so that it can be the same on another row, although unfortunately, could end up NULL.

这是我要做的事，除非有办法将两列都设为唯一且非NULLABLE？

This is what I will do unless there is a way to have both columns as unique and non NULLABLE?

推荐答案

从我的应用程序开发和数据仓库体验中，我建议使用单独的主键未在任何业务设置中使用，并且不使用用户ID作为主键。使用UserID作为主键可能会导致一系列问题。我会索引每一列（单独）。

From both my application development and datawarehouse experience I would recommend having a separate primary key that is not used in any business setting and do not use User ID as the primary key. Using UserID as the primary key can lead to a whole host of problems. I would index each column (separately).

任何时候你需要合并或重新分配用户或更改他们的ID等，实际使用他们的userID作为主键将导致这些操作出现很多问题。

Anytime you need to merge or reassign a user or change their ID, etc, having actually used their userID as the primary key will lead to a lot of problems for those operations.

此外，在网络上，这将打开人们看到网址如 ....用户/ 1 / details 然后可能将'1'更改为'2'（例如）并查看其他人的信息。如果ID是'57489574389ghfjghfjghf'那么唯一更好，然后用它来破解URL就更难了。

Also, on the web, this will open up people seeing URL's like ....user/1/details and then potentially being able to change the '1' to a '2' (for example) and seeing other peoples info. It is better if the ID is unique like '57489574389ghfjghfjghf' and then it's harder to hack URLs with.

自然和代理键之间的选择是在这里解释得很好：

http://www.agiledata.org/essays/ keys.html

The choice between a 'natural' and a 'surrogate' key is explained well here:
http://www.agiledata.org/essays/keys.html

人们在此领域遇到的大多数问题都是针对合并和删除等边缘情况。这些通常是最初的低优先级，但对它们的关注将随着时间的推移而增长，并且设计不良的解决方案将开始崩溃（通常因为在数据质量被识别的时候，通常存在如此大量的'坏'数据，前进是站不住脚的 - 旧数据不能修复，如果没有这些规则很难引入与它们共存的新记录。这假设仍然需要更新旧记录的能力。

Most of the problems people experience in this area are for edge cases such as merges and deletes. These are usually of low priority initially but concern over them will grow over time and poorly engineered solutions will start to break down (usually because at the point that data quality is 'recognized' there is often such a large volume of 'bad' data that going forward is untenable - the old data can't be 'fixed' and without that rules are hard to introduce for new records which will co-exist with them. This assumes that the ability to update old records is still required.

这篇关于请确认我使用主键和唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！