Oracle - 表上没有主键有什么影响吗？

Oracle - Is there any effect of not having a primary key on a table?

提问by Sathya

We use sequence numbers for primary keys on the tables. There are some tables where we dont really use the primary key for any querying purpose. But, we have Indexes on other columns. These are non-unique indexes. The queries use these non-primary key columns in the WHERE conditions.

我们使用序列号作为表上的主键。在某些表中，我们并没有真正将主键用于任何查询目的。但是，我们在其他列上有索引。这些是非唯一索引。查询在 WHERE 条件中使用这些非主键列。

So, I dont really see any benefit of having a primary key on such tables. My experience with SQL 2000 was that, it used to replicate tables which had some primary key. Otherwise it would not.

所以，我真的没有看到在这样的表上拥有主键的任何好处。我使用 SQL 2000 的经验是，它用于复制具有一些主键的表。否则不会。

I am using Oracle 10gR2. I would like to know if there are any such side-effects of having tables that dont have primary key.

我正在使用 Oracle 10gR2。我想知道没有主键的表是否有任何此类副作用。

回答by Opus

A table need not have a primary key. There is no effect on the database whatsoever for a table to have no explicit keys because every row in the database has an implicit unique data point that Oracle uses for storage and certain internal references. That is the ROWID pseudocolumn. ROWID is a piece of data that uniquely identifies every row in a database--with some notable exceptions.

表不需要有主键。表没有显式键对数据库没有任何影响，因为数据库中的每一行都有一个隐式的唯一数据点，Oracle 用于存储和某些内部引用。那就是 ROWID 伪列。ROWID 是唯一标识数据库中每一行的一段数据——有一些值得注意的例外。

The following query on my database returns the data shown:

对我的数据库的以下查询返回显示的数据：

select rowid from user$ where rownum <= 5;

AAAAAKAABAAAAFlAAC
AAAAAKAABAAAAFlAAD
AAAAAKAABAAAAFiAAD
AAAAAKAABAAAAFlAAE
AAAAAKAABAAAAFlAAF

It is not strictly necessary to have a key on a table. The Oracle10g database that I just queried has 569 system tables that have no primary or unique keys. It is a decision for the DBA and developer how keys should be created on database tables. The developers on my project always create primary keys regardless of their usefulness or sanity. As a DBA, I create keys only where they make sense.

桌子上没有钥匙是绝对必要的。我刚才查询的Oracle10g数据库有569个没有主键或唯一键的系统表。应由 DBA 和开发人员决定如何在数据库表上创建键。我的项目的开发人员总是创建主键，无论它们是否有用或是否理智。作为 DBA，我只在有意义的地方创建密钥。

Kind regards,

亲切的问候，

Opus

作品

回答by Andrew not the Saint

There are some tables where we dont really use the primary key for any querying purpose.

在某些表中，我们并没有真正将主键用于任何查询目的。

Then why do you have a sequence at all, if you never ever use it? Every table must have something that uniquely identifies a record, it need not be an artificial incremental sequence (aka a surrogate key), it could be a combination of natural key). There are always queries which access by some kind of a unique key (candidate key), which means that you'll definitely need an index and you may as well make the index unique.

那么，如果您从未使用过它，为什么还要有一个序列呢？每个表都必须具有唯一标识记录的东西，它不必是人工增量序列（又名代理键），它可以是自然键的组合）。总是有通过某种唯一键（候选键）访问的查询，这意味着您肯定需要一个索引并且您也可以使索引唯一。

There are other benefits of enforcing PK constraints on all persistent (non-temporary) tables:

对所有持久（非临时）表强制执行 PK 约束还有其他好处：

1. Ensuring that you will never ever have duplicate values (that your application won't be able to corrupt the DB)
2. Helping external tools perform automatic ER modeling
3. Last but not the least - allowing FK constraints!

1. 确保您永远不会有重复的值（您的应用程序将无法破坏数据库）
2. 帮助外部工具执行自动 ER 建模
3. 最后但并非最不重要的 - 允许 FK 约束！

回答by l0b0

Off the top of my head, you can't have a foreign key without a unique key, so there's no way to link the table to other tables. Changing this after the fact is not trivial since all code which accesses this table might be affected, either by not working at all because of shifted references, or by performing differently. Also, I believe we learned at uni (but this was 5+ years ago) that unique indexes are better for performance, because rows are segmented without regard for their contents.

在我的脑海中，没有唯一键就不能拥有外键，因此无法将表链接到其他表。事后更改这一点并非微不足道，因为访问此表的所有代码都可能受到影响，要么由于引用移位而根本无法工作，要么执行不同。另外，我相信我们在 uni（但这是 5 多年前）了解到唯一索引对性能更好，因为行被分割而不考虑它们的内容。