Reasons to use PRIMARY KEY?

Question

Post reply

Reasons to use PRIMARY KEY?

sqlvogel

SSCrazy Eights

Points: 9526
More actions
September 4, 2009 at 10:10 am

#204341

Primary keys are not essential in the Relational Model. Every relation / relvar must of course have at least one candidate key but if it has more than one such key then it makes no difference whether you designate any of those keys to be a "primary" one or not.
SQL's PRIMARY KEY constraint is also superfluous because NOT NULL UNIQUE does the same job.
So if the PRIMARY KEY syntax was dropped from the SQL language would anyone actually miss it?
The main reason to use PRIMARY KEY seems to be just convention - do it because everyone else does. Another possible argument in its favour might be that certain developer tools and data management tools depend on the presence of a PRIMARY KEY constraint. However, I don't find that a very good excuse because the power and usefulness of such software would be improved if it supported any candidate key equally and not just those declared as PRIMARY KEY. The PRIMARY KEY constraint ought not to excuse software vendors from improving their software.
Are there any other reasons to use PRIMARY KEYs?

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Can you explain the reasons to NOT use a PRIMARY KEY?

Exactly how will not using them improve anything?

steveb. SSC-Forever Points: 46733 More actions · Answer 2

It can make developing software a lot easier - this is probably the best reason

Lowell SSC Guru Points: 323518 More actions · Answer 3

Speed.

the PK determines the actual order of the data, so the any SQL's that use that "PK = somevalue" is very fast...because that PK index is used, and makes index seeks or scans faster.

Also, the PK is actually stored at the leaf level in the page, so it's faster because the table data does not have to be accessed to get the value.

so while "In Theory", PK's are not required, "In Practice", they are required to make any data access acceptable as far as speed goes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 4

Lowell (9/4/2009)
Speed.
the PK determines the actual order of the data, so the any SQL's that use that "PK = somevalue" is very fast...because that PK index is used, and makes index seeks or scans faster.
Also, the PK is actually stored at the leaf level in the page, so it's faster because the table data does not have to be accessed to get the value.
so while "In Theory", PK's are not required, "In Practice", they are required to make any data access acceptable as far as speed goes.

Lowell, you are talking about clustered indexes. Not PRIMARY KEYs. They aren't the same thing and one does not depend on the other.

Regards,

Lowell SSC Guru Points: 323518 More actions · Answer 5

Ahh David, yeah, i fell into that SQL trap where i assume all my PK's are also the clustered index...my bad.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

steveb (9/4/2009)
It can make developing software a lot easier - this is probably the best reason

Do you mean that having Primary Keys makes it easier, or not having them makes it easier?

Either way, how does it make it easier?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

How is primary key not an essential concept in RDBMS'es? They go directly to the definition of a well-formed table, and are the basis by which Codd's Guaranteed access rule is implemented.

Primary key is not an column, it's not a property of an index, it's a whole lot more than that. Among other thing it's what should be used as a foreign key if any natural keys are not suitable (and even in some cases where there might be a natural key).

It is something that is use for consistency. Consistency of relationship. It's also the one and only one you have to worry about changing in the future. I've seen many many projects where you start out with a dozen candidate keys, and not one survives as unique and addressable. How do you then guarantee access to the data you're storing in this amorphous object that used to be a table?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 8

Matt Miller (9/4/2009)
How is primary key not an essential concept in RDBMS'es? They go directly to the definition of a well-formed table, and are the basis by which Codd's Guaranteed access rule is implemented.

Any candidate key will suffice for guaranteed access and if every table has at least one candidate key there is no special reason to call any of them "primary". Codd says that where there is more than one candidate key the choice of primary key is "arbitrary" and suggests it should be optional.

Among other thing it's what should be used as a foreign key if any natural keys are not suitable (and even in some cases where there might be a natural key).

I don't know what you mean by that. A primary key could be "natural" or otherwise. In relational terms "primary key" implies nothing about the type of data in that key other than that it qualifies as a candidate key (though in fact SQL doesn't even require that much, which is one reason to deprecate SQL's misappropriation of the term "PRIMARY KEY").

It is something that is use for consistency. Consistency of relationship. It's also the one and only one you have to worry about changing in the future.

This is a fair point. Designating some key as "primary" acts as an aide-mémoire during design. It can be used to indicate that a key has some "special" significance. My own view is that that's really the job of data modelling tools and a data dictionary. It's not useful or necessary in the DBMS.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Lowell (9/4/2009)
the PK determines the actual order of the data

Um... no... sorry. It doesn't. You can have PK's that are not a clustered PK.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Jeff Moden (9/5/2009)
Lowell (9/4/2009)
the PK determines the actual order of the data
Um... no... sorry. It doesn't. You can have PK's that are not a clustered PK.

Heh... and I fell into my own trap of not reading all the posts before responding. I see this was already corrected. Sorry for my duplication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 11

So what are my reasons for wanting to abolish the PRIMARY KEY?

One good reason is the enormous amount of needless misinformation and confusion about keys in online articles and even in some books. Try this expirement. Take a look at many popular database books or anyone else talking about "primary keys" (including many posts of the in SQL Server Central). 9 times out of 10 you'll find they aren't talking about primary keys at all. They mean a candidate key.

If the PRIMARY KEY constraint was deprecated and no longer a topic of discussion in database design then the quality of that discussion and information would be improved. Students and practitioners would be the better for it because their mental model of what keys are would more accurately reflect real database design issues.

Another reason to discontinue the PRIMARY KEY constraint is one already mentioned: SQL PRIMARY KEY is not the same as what the relational model calls a primary key so overloading those different meanings can only lead to confusion. (Yes I'm aware that SQL isn't relational either but that's no excuse)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Ummm... I really want my doctor to know the differerence between a transverse colon and a descending colon even though they're both just a part of the same large intestine especially if he intends to operate.

Although the technical differences are virtually NIL between a PK and a CK and they're both candidate keys, I still prefer that people know the subtle differences that can occur with their creation. I also enjoy the ease in which I can create a PK when only one candidate key needs to be present on a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 13

Jeff Moden (9/9/2009)
Ummm... I really want my doctor to know the differerence between a transverse colon and a descending colon even though they're both just a part of the same large intestine especially if he intends to operate.
Although the technical differences are virtually NIL between a PK and a CK and they're both candidate keys, I still prefer that people know the subtle differences that can occur with their creation. I also enjoy the ease in which I can create a PK when only one candidate key needs to be present on a table.

The subtle differences are just minor quirks of the syntax in SQL Server. Obviously if the syntax was removed then those differences would disappear too, so why would that matter? The only thing "easier" about PRIMARY KEY is that it is slightly fewer keystrokes than NOT NULL UNIQUE.

steveb. SSC-Forever Points: 46733 More actions · Answer 14

Michael Valentine Jones (9/4/2009)
steveb (9/4/2009)
It can make developing software a lot easier - this is probably the best reason
Do you mean that having Primary Keys makes it easier, or not having them makes it easier?
Either way, how does it make it easier?

Sorry I wasn’t very clear, I meant it makes it easier to have primary keys as I can define them in the database and then everyone working on the project from developers, testers to project managers and business analysts will know exactly what the primary key should be on the table as it will show up in SSMS, Visual Studio and generated diagrams as the primary key.

Sure the same thing can be done with a unique non-null index but it is not immediately obvious that this is the primary key.