Does having a primary key column mean there is an index on that column? If so, what kind of index is it?
+1
A:
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.
LBushkin
2010-02-01 20:22:03
SQL Server 2008
Shayan
2010-02-01 20:24:20
+1
A:
Please check on this article
http://stackoverflow.com/questions/462477/sql-primary-key-and-index
madatanic
2010-02-01 20:22:29
A:
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.
Pointy
2010-02-01 20:22:35
+2
A:
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.
Andomar
2010-02-01 20:22:44
So if I have a primary key(which necessarily I have) I can't have another clustered index? Because just one clustered index is possible. How can I say that I don't want it to be unclustered?
Shayan
2010-02-01 20:27:22
You can create a nonclustered primary key using the `nonclustered` keyword, see http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx
Andomar
2010-02-01 20:29:48
A:
In all the DBs I've used, PRIMARY KEY is basically just a UNIQUE index.
Kaleb Brasee
2010-02-01 20:23:18
I think this is basically correct. So +1 to compensate for the unexplained downvote :)
Andomar
2010-02-01 20:32:45
+5
A:
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.
AdaTheDev
2010-02-01 20:23:56
+1 and FYI for the OP: Indexes are not covered by ANSI, though MySQL and SQL Server use the same terminology. Oracle does not distinguish - they are just indexes. Also re: SQL Server - a clustered index is created by default when defining a primary key, but the clustered index can be changed to associate other column(s) in the table.
OMG Ponies
2010-02-01 20:36:18