关于主键的一般 SQL 问题
我知道这很简单,但就这样吧。
我想知道在没有主键的表中,如何知道哪些列是主键?有什么我应该阅读的技术或内容吗?
先感谢您
I know this is pretty elementary but here it goes.
I would like to know how you know what columns are a primary key in a table that does not have a primary key? Is there a technique or something that I should read?
Thank you in advance
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
您需要查看您的数据结构。
主键必须:
,这会有所帮助如果它
检查您的数据 - 哪些列或一组列可以满足这些要求?
一旦您拥有了这些潜在的主键(“候选键”),请考虑如何访问数据,以及可能需要与相关实体关联的其他数据 - 什么才是外键?您想通过名称引用您的部门吗?可能不是一个好主意,因为名称可能会拼写错误,它可能会随着时间的推移而改变等等。按部门的办公地点?也是错误的选择。但像唯一的“部门 ID”之类的东西可能是个好主意。
如果您在实际数据中找不到任何合适的列可以用作主键并且有意义,则通常的做法是引入“代理键” - 一个额外的列,通常是
INT< /code> (通常类似于“自动增量”INT),它将用作每行的人工标识符。如果您这样做,一种常见的最佳实践是永远不要在任何数据屏幕上显示该人工密钥 - 它对系统用户没有任何意义 - 因此甚至不要向他们显示它。
检查这些要求和大量经验将帮助您找到正确的主键。
You need to take a look at your data structures.
A primary key must:
and it helps if it's
Check your data - which columns or set of columns can fulfill these requirements??
Once you have those potential primary keys (the "candidate keys") - think about how you will access the data, and what other data might need to be associated with this one entity in question - what would make sense as a foreign key? Do you want to reference your department by its name? Probably not a good idea, since the name could be misspelled, it might change over time etc. By the department's office location? Bad choice, too. But something like a unique "department ID" might be a good idea.
If you don't find any appropriate column(s) in your actual data that could serve as primary key and would make sense, it's a common practice to introduce a "surrogate key" - an extra column, often an
INT
(and often something like an "auto-increment" INT) that will serve as an artificial identifier for each row. If you do this, one common best practice is to never show that artificial key on any data screen - it has no meaning whatsoever to the users of your system - so don't even show it to them.Checking these requirements, and a lot of experience, will help you find the right primary key.