SQL 中是否需要 ID 列? [英] Is ID column required in SQL?

问题描述

传统上我一直在 SQL(主要是 mysql 和 postgresql)中使用 ID 列.

Traditionally I have always used an ID column in SQL (mostly mysql and postgresql).

但是我想知道是否真的有必要每行中的其余列都是唯一的.在我最新的项目中，我将ID"列设置为我的主键，但我从不调用它或以任何方式使用它，因为行中的数据使其独一无二，对我来说更有用.

However I am wondering if it is really necessary if the rest of the columns in each row make in unique. In my latest project I have the "ID" column set as my primary key, however I never call it or use it in any way, as the data in the row makes it unique and is much more useful for me.

那么，如果 SQL 表中的每一行都是唯一的，是否需要一个主键 ID 表，有没有主键 ID 表是否会导致蚂蚁性能变化?

So, if every row in a SQL table is unique, does it need a primary key ID table, and are there ant performance changes with or without one?

谢谢！

编辑/附加信息:让我问这个问题的具体示例是我用于多对多对多对多表的表(如果我们当时仍然称其为)，它有 4 列(加上 ID)每一个都代表一个外部表的ID，每一行都是数字且唯一的.只允许其中一列为空.

EDIT/Additional info: The specific example that made me ask this question is a table I am using for a many-to-many-to-many-to-many table (if we still call it that at that point) it has 4 columns (plus ID) each of which represents an ID of an external table, and each row will always be numeric and unique. only one of the columns is allowed to be null.

我知道对于普通表来说，ID 主键列是一件非常好的事情.但我觉得这个特定的表只会浪费空间并减慢添加新行的速度.

I understand that for normal tables an ID primary key column is a VERY good thing to have. But I get the feeling on this particular table it just wastes space and slows down adding new rows.

推荐答案

如果您的数据集中确实有一些预先存在的列，这些列已经可以唯一标识您的行 - 那么不，不需要额外的 ID 列.然而，主键必须唯一(在所有情况下)并且不能为空(必须为非空).

If you really do have some pre-existing column in your data set that already does uniquely identify your row - then no, there's no need for an extra ID column. The primary key however must be unique (in ALL circumstances) and cannot be empty (must be NOT NULL).

然而，在我 20 多年的数据库设计经验中，这几乎从来都不是真的.大多数看似独一无二的自然"ID 最终并非如此.美国社会安全号码不能保证是唯一的，大多数其他自然"键最终几乎都是唯一的 - 这对于数据库系统来说还不够好.

In my 20+ years of experience in database design, however, this is almost never truly the case. Most "natural" ID's that appear to be unique aren't - ultimately. US Social Security Numbers aren't guaranteed to be unique, and most other "natural" keys end up being almost unique - and that's just not good enough for a database system.

因此，如果您的数据中确实有一个合适的、唯一的键 - 使用它！但大多数情况下，只有一个可以保证在所有行中都是唯一的代理 ID 会更容易、更方便.

So if you really do have a proper, unique key in your data already - use it! But most of the time, it's easier and more convenient to have just a single surrogate ID that you can guarantee will be unique over all rows.

这篇关于SQL 中是否需要 ID 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！