blocks|key|884873|text|如果用户名是主键，并且用户更改了他/她的用户名，则需要更新所有具有对users表的外键引用的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|884874|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If the username is the primary key and a user changes his/her username, you will need to update all the tables which have foreign key references to the users table.

blocks|key|884901|text|因为有人可能想要更改他们的用户名(或任何名称)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|884902|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Because someone might want to change their username (or any name for that matter).

blocks|key|1099787|text|例如，整数搜索(?id=5)比字符串搜索(?username=bob)快得多，而且基数更高。另一个例子是，uid是auto_increment，所以您不必显式地插入它，但它会在每个插入查询中自动递增。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1099788|附言:你的教授大错特错了:D|1099789|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

For instance, integer search (?id=5) is much way faster and has higher cardinality than string search (?username=bob).
Another example, uid is auto_increment, so you don't have to insert it explicitly but it will auto increment in each insert query.

PS: Your prof is soooo wrong about it :D

blocks|key|885019|text|我们使用ID来防止重复数据，它可以使一些过程变得不复杂(如果我们想要更新或删除数据)，如果我们使用ID，它会更简单。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|885020|如果您不想使用ID，您可以使用其他字段。但别忘了让它们变得独特。它可以使您的数据成为防止重复数据。|885021|PRIMARY之外的另一种方式是唯一的。|885022|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

we use ID to prevent duplication data and it can make some procces become not complicated (if we want to update or delete data), it more simple if we use ID.

if you dont want to use ID you can use another fields. but dont forget to make them become UNIQUE. it can make your data become preventive from duplication data.

another way outside PRIMARY is UNIQUE.

blocks|key|1099875|text|我同意上面所有的答案。我想说ID很容易实现，当涉及到索引时，与varchar相比，Int总是首选的。你的教授应该更清楚，为什么他会拒绝Int+id高于我！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1099876|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I go with all the answers above. I would say an ID is easy to implement and when it comes to indexing, Int is always preferred compared to a varchar. Your professor should know better, why would he say no to Int id is above me!

blocks|key|1099909|text|这通常被称为surrogate+key，它有很多优点。其中之一是将数据库关系与应用程序数据隔离。更多细节和相应的缺点可以在上面提供的wiki链接中找到。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1099910|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Surrogate_key^0|6|D|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

This is typically called a <a href="http://en.wikipedia.org/wiki/Surrogate_key" rel="noreferrer">surrogate key</a> and it has many benefits. One of which is insulating your database relationships from the application data. More details and the corresponding disadvantages can be found at the wiki link provided above.

blocks|key|1099938|text|因为userid应该是唯一的(不能重复)&有时是索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1099939|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Because userid is supposed to be unique (cannot be duplicated) &amp; sometimes is index.

blocks|key|885225|text|你的教授做了一件正确的事情，他指出，如果要求用户名应该是唯一的，那么你应该让用户名唯一，并且不能为空。uid也可以是一个键，但是除非你真的在某个地方使用它，否则它是不需要的。设计中更重要的方面应该是实现自然密钥。所以我同意你教授的意见。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|885226|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Your professor is doing the right thing by pointing out that you should have made username unique and not nullable if it was a requirement that user names should be unique. The uid could be a key as well but unless you are actually using it somewhere then it isn't needed. The more important aspect of the design ought to be to implement the natural key. So I agree with your professor's comment.

blocks|key|1100129|text|如果你已经向你的教授证明，为每个用户分配一个唯一的任意整数对你的应用程序是有价值的，那么他说这是“完全无用和不必要的”，当然是错误的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1100130|然而，也许你没有理解他的意思。如果他告诉你的要求是“两个用户不能有相同的用户名”，那么你就没有满足这个要求。|1100131|真诚的感谢你张贴你的SQL，它是非常有用的，但大多数人不会为此而烦恼。|1100132|使用您的表，我可以这样做：|1100133|INSERT+INTO+users+(username)+VALUES+(NULL);
INSERT+INTO+users+(username)+VALUES+(NULL);
INSERT+INTO+users+(username)+VALUES+(NULL);
INSERT+INTO+users+(username)+VALUES+(NULL);
INSERT+INTO+users+(username)+VALUES+(NULL);|code-block|syntax|javascript|1100134|这将导致以下结果：|1100135|SELECT+uid,+username,+passhash,+email,+rdate+
FROM+users;

uid+++username+++passhash+++email+++rdate
1+++++<NULL>+++++<NULL>+++++<NULL>++<NULL>
2+++++<NULL>+++++<NULL>+++++<NULL>++<NULL>
3+++++<NULL>+++++<NULL>+++++<NULL>++<NULL>
4+++++<NULL>+++++<NULL>+++++<NULL>++<NULL>|1100136|我认为这就是你的教授试图表达的观点:如果不在username上强制使用自然密钥，你就根本没有任何数据完整性。|offset|length|style|CODE|1100137|如果我是教授，我也会敦促你从你的设计中删除可以为空的列。|1100138|entityMap^0|0|0|0|0|0|0|0|M|8|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|17|8|@$S|18|T|19|U|V]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

If you have demonstrated to your professor that assigning a unique arbitrary integer to each user is of value to your application then of course he would be wrong to say that it is "completely useless and unnecessary".

However, maybe you missed his point. If he told you that the requirement is that "no two users can have the same username" then you haven't met that requirement.

Sincere thanks for posting your SQL DDL, it is very useful but most don't bother on SO.

Using your table, I can do this:

<pre><code>INSERT INTO users (username) VALUES (NULL);
INSERT INTO users (username) VALUES (NULL);
INSERT INTO users (username) VALUES (NULL);
INSERT INTO users (username) VALUES (NULL);
INSERT INTO users (username) VALUES (NULL);
</code></pre>

Which results in this:

<pre><code>SELECT uid, username, passhash, email, rdate 
FROM users;

uid username passhash email rdate
1 &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt;
2 &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt;
3 &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt;
4 &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt; &lt;NULL&gt;
</code></pre>

I think is the point your professor was trying to make: without enforcing the natural key on <code>username</code> you don't really have any data integrity at all. 

If I was the prof, I'd also urge you to remove nullable columns from your design.

blocks|key|1100175|text|你想以明文形式存储你的用户名，以便任何人窃取吗？我永远不会考虑使用某一天我可能想要加密(或现在就想加密)的自然密钥。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1100176|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

And do you want to store your usernames in clear text for any one to steal? I would never consider using a natural key that I might want to encrypt someday (or want to encrypt now).

I'm taking a course called "database systems" and for our class project I have to design a website.

Here's an example of a table I created:

<pre><code>CREATE TABLE users
(
 uid INT NOT NULL AUTO_INCREMENT,
 username VARCHAR(60),
 passhash VARCHAR(255),
 email VARCHAR(60),
 rdate DATE,
 PRIMARY KEY(uid)
);
</code></pre>

The professor told me "uid" (user id) was completely useless and unnecessary and I should have used the username as the primary key, since no two users can have the same username.

I told him it was convenient for me use a user id because when I call something like domain.com/viewuser?id=5 I just check the parameter with: <code>is_numeric($_GET['id'])</code>... needless to say he was not convinced.

Since I've seen user_id and other similar attributes (thread_id, comment_id, among others) on plenty of tutorials and looking at the database schema of popular software (eg. vbulletin) there must be plenty of other (stronger) reasons.

So my question is: How would you justify the need of a not null auto incrementing id as a primary key vs using another attribute like the username?

Why use an auto-incrementing primary key when other unique fields exist?

数据完整性

数据库

我正在选修一门名为“数据库系统”的课程，对于我们的课程项目，我必须设计一个网站。下面是我创建的一个表的示例：CREATE TABLE users(  uid INT NOT NULL AUTO_INCREMENT,  username VARCHAR(60),  passhash VARCHAR(255),  emai...

问当存在其他唯一字段时，为什么要使用自动递增的主键？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当存在其他唯一字段时，为什么要使用自动递增的主键？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当存在其他唯一字段时，为什么要使用自动递增的主键？
EN