SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is a Primary Key an index?


Is a Primary Key an index?

Author
Message
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49430 Visits: 21142
Sergiy (7/18/2014)
Koen Verbeeck (7/17/2014)
Sergiy (7/16/2014)
Koen Verbeeck (7/16/2014)
So it is 100% possible to decouple the primary key from the index

Can you illustrate how?


Easy peasy. I draw a table diagram in Visio and declare the combination of a few columns to be the primary key.
(in other words, I am creating the logical design)
Where do you see an index?

I do not see a primery key here either.
I do not even see a database.
It's just a set of drawings, labels, indicated intentions.

As soon you try to turn it into an actual database and actually create that PK the corresponding index will be there.


You appear to be suggesting that a primary key does not and can not exist unless it is physically implemented in a database - is that the case?

Meaning, presumably, that you object to a basic database design document using the term 'primary key'.

Perhaps instead you refer to a relation's "default unique-row identifier(s)". Does the job, I suppose.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59951 Visits: 13297
Sergiy (7/18/2014)


As soon you try to turn it into an actual database and actually create that PK the corresponding index will be there.


Again, that is just the physical manifistation of the primary key.
It may change from database implementation to another. What if Microsoft had decided to implement it in a whole different way?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25338 Visits: 12488
Phil Factor (7/17/2014)
@Sergei
Please correct me if I'm wrong but I'd always believed a primary key was enforced by a constraint which was implemented by means of both a UNIQUE index and NOT NULL constraints on all the participating columns.

@Phil, you are wrong (but your QotD is correct, of course, a primary key is not an index).

A primary key is not a constraint. There are two acceptable definitions in relational theory: (1) it is the candidate key (set of columns) chosen to be the principal means of reaching/identifying individual rows in a relation, or (2) it is the value of a primary key as defined by (1) in a particular row.

Neither of those things is a primary key constraint. A primary key constraint is a constraint that ensures that the primary key is fully defined (ie includes no NULL values) in every row, and that the projection of the relation onto the columns involved in the primary key contains no duplicates, ie the set of columns involved in the primary key is a superkey so it can be used to reach or identify an individual row - while theory talks about candidate keys and database design attempts to discover them by understanding what real world rules apply to the data, an RDBMS can't implement a constraint which enforces the candidate key property other than for single column keys - in effect the constraint that the primary key be a candidate key rather than just a superkey is a theoretical (and often ignored or broken) constraint on the schema design, not a constraint on the data.

The primary key constraint is implemented as a set of constraints: a domain constraint for each column involved in the key that specifies that the domain excludes NULL and a uniqueness constraint that ensures there are no duplicates; the uniqueness constraint is in turn implemented (in almost every RDBMS) using an index.

Tom

sqlvogel
sqlvogel
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 3706
Here's a small example:

CREATE TABLE t
(x INT NOT NULL UNIQUE,
y INT NOT NULL,
z INT NOT NULL UNIQUE,
CONSTRAINT pkc PRIMARY KEY (x,y));



"pkc" is not the primary key of this table - it's just a constraint. (x,y) is not the primary key either. As Tom already mentioned, in SQL the constraint called "PRIMARY KEY" is a superkey constraint whereas the actual primary key is by definition supposed to be a candidate key and not just any superkey. In this unusual example (x) is defined to be a unique single column and therefore (x,y) is not a candidate key because it isn't irreducibly unique - it isn't the smallest subset of unique attributes of a superkey. Either (x) or (z) could be the primary key of this table, but (x,y) certainly can't be.

So in principle a primary key doesn't have to be the set of columns defined by a PRIMARY KEY constraint. A PRIMARY KEY constraint can be thought of as a technical feature which common sense suggests ought to be used to identify the actual primary key columns, but it doesn't have to be used that way. Once you realise that fact, I think it follows that even in a SQL Server database a primary key doesn't have to require a corresponding index. It is possible (though usually not desirable) to enforce uniqueness in other ways without a specific index: by using a combination of CHECK constraints for example. The primary key of a table might just as well be some column(s) whose uniqueness is enforced without using an index.

Is it sensible that SQL Server creates indexes whenever we use the PRIMARY KEY syntax? Usually it makes sense to do so, but it is also a product limitation. It would be nice to have the option to create uniqueness constraint (PRIMARY KEY or UNIQUE) without SQL Server always creating a new index. Other DBMSs allow that. SQL Server does not.
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4196 Visits: 1695
Read it, thought about it, missed it, learned from it, and moved on from it.

Thanks, good question.

Not all gray hairs are Dinosaurs!
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6283 Visits: 2774
TomThomson (7/15/2014)
Irritating because over-pedantic. Good question nevertheless.


+1
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search