Is there an index on the primary key of a table? [duplicate]

This question already has answers here开发者_运维知识库: sql primary key and index (11 answers) Closed 8 years ago.

Does having a primary key column mean there is an index on that column? If so, what kind of index is it?

---

For SQL Server, which I believe from previous questions is what you're using, when you define a PRIMARY KEY, it will automatically have a index on that column which will default to being a CLUSTERED index. You can define whether it should be a NONCLUSTERED or a CLUSTERED index when you create the constraint.

---

Yes, a primary key implies an index.

If the primary key is clustered, the index will be part of the main table file. It it's not clustered, it will be part of a separate index file.

---

It depends on the database.

Some databases either require or automatically create primary key indexes as a way to enforce the uniqueness of a primary key. Others are perfectly happy to perform a full scan of the table.

Which database are you using?

EDIT:

• SQLServer (versions 7 - 2008) creates indexes or primary keys - you can control whether or not it is clustered.
• Older versions of Oracle (8i,9i) also create indexes when you add a unique key constraint. Newer versions (10g) don't seem to, based on the test case I just looked at.

---

In any "real" database, yes having a primary key means having a unique index. In some databases, the primary key index can/will cluster on key values too.

---

In all the DBs I've used, PRIMARY KEY is basically just a UNIQUE index.