用户名/密码索引 [英] Index for username/password
问题描述
这是否适用于登录表：
CREATE TABLE登录
（
ID INT NOT NULL IDENTITY PRIMARY KEY，
名称VARCHAR（15）NOT NULL，
密码VARCHAR（15）NOT NULL
）
GO
CREATE UNIQUE INDEX IX_Logon_Name ON登录（名称）
CREATE INDEX IX_Logon_NameAndPassword ON登录（姓名，密码）
GO
我确实希望这个名字是唯一的，但也会频繁搜索
name&密码。这是怎么做的？我不完全理解在名称和&
密码VS名称&密码。
Does this make sense for a logon table:
CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO
I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don''t fully
understand the difference between placing a single index in name &
password VS one on both name & password.
推荐答案
Cecil（ce********@yahoo.com）写道：
Cecil (ce********@yahoo.com) writes:
这对于登录表是否有意义：
创建表登录
（ID INT NOT NOT NULL IDENTITY PRIMARY KEY，
name VARCHAR（15）NOT NULL，
密码VARCHAR（15）NOT NULL
）
GO
CREATE UNIQUE INDEX IX_Logon_Name ON登录（名称）
CREATE INDEX IX_Logon_NameAndPassword ON登录（名称，密码）
GO
我确实希望这个名字是独一无二的，但也会经常搜索
name&密码。这是怎么做的？我并不完全理解在名称和密码VS中放置单个索引与名称和密码之间的区别。密码。
Does this make sense for a logon table:
CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO
I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don''t fully
understand the difference between placing a single index in name &
password VS one on both name & password.
我没看到ID列的目的？为什么不将名称作为主要的
键？
索引（名称，密码）在这里似乎没什么用处。通常表单上的
索引（uniquecolumn，othercolumn）没有意义，但有时可以实现所谓的覆盖查询
。但是只要
表中没有很多其他列，就很难在这里看到
的情况。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server联机丛书2000年在
http://www.microsoft。 com / sql / prodinf ... ons / books.mspx
I don''t see the purpose of the ID column? Why not make the name the primary
key?
The index on (name, password) does not seem very useful here. Usually an
index on the form (uniquecolumn, othercolumn) is not meaningful, but it
can be sometimes, to achieved so-called covered queries. But as long as
the table does not have lots of other columns, it''s difficult to see a
case for it here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
>>我没有看到ID列的用途？为什么不将名称作为主要的
>>I don''t see the purpose of the ID column? Why not make the name the primary
key？
我在考虑做什么那，但我打算让登录表像一张身份证一样
。仅用于有效识别。我想在多个需要
认证的项目中重用
这个表设计。
所以如果我以后有一个员工比如，需要登录，而不是将
a用户名，密码添加到Employee表中我可以简单地将一个LogonID
字段添加到employee表中以链接它与他们的登录表中的识别记录
。
您认为这是一个坏主意吗？
我也是我认为总是使用int ID作为我的主要
键而不是用于搜索和加入的字符串会更快。
如果我有一个链接到登录表的外键我必须将整个字符串作为外键而不是一个int。所以它是
我的计划是确保每个表都有一个int主键，即使它可以通过已经存在的列（例如
用户名。
再次，您认为这是一个坏主意吗？你会为varchar用户名字段命名外国
键？ usernameID？它似乎只是它b / b如果附加了ID，我应该是一个数字。我喜欢使用ID
因为当我看到它时，我知道这是某种关键，但也许我不会这样做。
不应该这样做。
我之前正在阅读一篇帖子，他向我建议所有
字段名称在我的架构中都是唯一的。所以，如果我理解他
正确：
LogonID，LogonName，& LogonPassword将是更好的字段名称。
LogonPassword似乎有点过分而不仅仅是密码而是
如果你将是唯一的，你可能会有另一个名为
密码在另一个表中，所以我想你必须这样做。
几乎就像每个字段名称的表格一样。
我从头开始创建一个简单的数据库所以我正在努力使用尽可能好的
做法，并且对你的推荐非常感兴趣
Erland。谢谢。
I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.
So if I later had an employee say, that needs to login, rather than add
a username,password to the Employee table I could simply add a LogonID
field to the employee table to link it w/ their identification record
in the Logon table.
Do you think this is a bad idea?
Also I thought it would be faster to always use an int ID as my primary
key instead of a string for searching and joining.
If I were to have a foreign key linking to the logon table I''d have to
stick the whole string as the foreign key instead of just an int. So it
was my plan to make sure each table had an int primary key even if it
was possible to uniquely id a record by an already present column like
username.
Again, do you think this is a bad idea? What would you name the foreign
key to a varchar username field? usernameID? It just seems like it
should be a number to me if it has ID appended to it. I like using ID
becuase I know it is a key of somekind when I see it but maybe I
shouldn''t do that.
I was reading a post by someone earlier who suggested to me that all
field names be unique across my schema. So if I understand him
correctly:
LogonID, LogonName, & LogonPassword would be better field names.
LogonPassword seems sorta like overkill compared to just password but
if you''re going to be unique you might have another field called
password in another table so I guess you''d have to do it that way.
Almost like table-qualifying each field name.
I''m starting a simple DB from scratch so I''m trying to use as good a
practices as I can and would be very interested in your reccomendations
Erland. Thanks.
Cecil写道：
Cecil wrote:
我没有看到ID列的目的？为什么不把这个名字作为主键呢？
I don''t see the purpose of the ID column? Why not make the name the primary
key?
我想这样做，但我打算让登录表像
一样一张身份证。仅用于有效识别。我想在多个需要
认证的项目中重复使用这个表设计。
I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.
名称仍然是唯一的但不是它？所以它仍然应该对名称有一个独特的限制。
在数据库中存储密码是一个固有的安全漏洞。不要
存储它们，加密或其他方式。如果必须，请存储
的安全哈希密码。如果您使用的是SQL Server 2005，则使用内置的
加密/身份验证。在可能的情况下，使用集成安全
而不是自己发明。
-
David Portas
SQL Server MVP
-
Name would still be unique though wouldn''t it? So it should still have
a unique constraint on name.
Storing passwords in the database is an inherent security flaw. Don''t
store them, encrypted or otherwise. If you must, store a secure hash of
the password. If you are using SQL Server 2005 then use the built in
encryption / authentication. Where possible, use integrated security
rather than invent your own.
--
David Portas
SQL Server MVP
--
这篇关于用户名/密码索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
