关于主键的一般 SQL 问题

发布于 12-04 15:13 字数 83 浏览 0 评论 0原文

我知道这很简单,但就这样吧。

我想知道在没有主键的表中,如何知道哪些列是主键?有什么我应该阅读的技术或内容吗?

先感谢您

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

归属感2024-12-11 15:13:05

您需要查看您的数据结构。

主键必须

  • 决不为 NULL(无例外
  • 可靠且唯一标识每一行

,这会有所帮助如果它

  • 很小且易于使用
  • 稳定(根本不改变,或至少不经常改变)
  • 单个列(或最多两个)

检查您的数据 - 哪些列或一组列可以满足这些要求?

一旦您拥有了这些潜在的主键(“候选键”),请考虑如何访问数据,以及可能需要与相关实体关联的其他数据 - 什么才是外键?您想通过名称引用您的部门吗?可能不是一个好主意,因为名称可能会拼写错误,它可能会随着时间的推移而改变等等。按部门的办公地点?也是错误的选择。但像唯一的“部门 ID”之类的东西可能是个好主意。

如果您在实际数据中找不到任何合适的列可以用作主键并且有意义,则通常的做法是引入“代理键” - 一个额外的列,通常是 INT< /code> (通常类似于“自动增量”INT),它将用作每行的人工标识符。如果您这样做,一种常见的最佳实践是永远不要在任何数据屏幕上显示该人工密钥 - 它对系统用户没有任何意义 - 因此甚至不要向他们显示它。

检查这些要求和大量经验将帮助您找到正确的主键。

You need to take a look at your data structures.

A primary key must:

  • never be NULL (no exceptions)
  • reliably and uniquely identify each single row

and it helps if it's

  • small and easy to use
  • stable (doesn't change at all, or at least not often)
  • a single column (or at most two)

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.

冷清清2024-12-11 15:13:05

这实际上取决于数据本身。您需要确定哪些字段可用于唯一标识记录。

It really depends on the data itself. You need to determine what fields can be used to identify the record uniquely.

千と千尋2024-12-11 15:13:05

在 SQL Server 中,它旁边会有一个键。它通常是 ID 或包含 ID 的内容。它也是唯一的并且通常是增量的。当你用 SQL 查看它时。服务器管理工​​作室在表格设计下,您将在列列表的顶部看到它,并带有 Lil 键图标。

它是一个唯一的标识符,可以将每条记录相互解读。有点像每个人都有一个 ssn。

In SQL server it'll have a key next to it. It's typically ID or something with ID in it. It's also unique and typically increments. When you look at it in SQL. Server management studio under table design you'll see it towards the top of the list of columns with the Lil key icon.

It's a unique identifier that deciphers each record from one another. Kind of like how each person has a ssn.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文