| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-08-06 : 18:33:58
|
| I've learned some SQL by osmosis, and read a few books. I can build querys that used to frighten me, and I am embarrased to say that I haven't got my arms around "exactly" why I benefit if I declare a column as a Primary Key. I can join on anything that isn't declared so.I can guess - that the Column is maintained in some "higher/faster access" manner when it is declared as a Primary or even Foreign Key. I'd appreciate any comments or pointers to electronic or hardcopy discussion on the subject.SamC |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-06 : 19:07:06
|
Books Online has a number of entries on primary keys, and any book on SQL or relational databases will cover it too. Also check the database design section on SQL Team:http://www.sqlteam.com/FilterTopics.asp?TopicID=129Discussion? On SQL Team? About PRIMARY KEYS? Hmmmmm...http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18429 Basically a primary key guarantees that your data can be uniquely identified; each row has a value or combination of values that no other row in the table has. That's guaranteed by the primary key constraint. Primary keys also do not allow nulls. Think of a deck of cards; each card is unique in its number and suit. Without these the cards would be blank and nothing would distinguish them from any other card. Without a primary key your tables are really nothing more than "heaps" of data.The benefit you get is that you can't end up with duplicate rows that have the same key. You get an index automatically when using a primary key, and it can be used by the query analyzer to improve performance. |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-06 : 20:08:06
|
quote:
You get an index automatically when using a primary key, and it can be used by the query analyzer to improve performance.
yes, an index almost always improves retrieval performance (and i can't think of any examples where it would hamper retrieval performance), but there is some slight overhead to pay in update performance, because both the table and the index have to be updated -- but this slight overhead is usually worth it, because you usually retrieve way more than you updatethe benefit of not allowing nulls can, of course, be achieved on any column by declaring it NOT NULLthe other benefit mentioned was uniquenessyou can get this benefit from a unique constraint as easily as from a primary key constraint, but since the primary key is, by definition, unique, and since the database creates a unique index as its mechanism for enforcing primary key uniqueness, it is often simply just more convenient to declare a primary keyhow'd i do, rob? better this time? rudyhttp://rudy.ca/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-06 : 21:24:16
|
 My thoughts exactly!Just pray that my OTHER thoughts don't intrude on your mind....(shudder) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-07 : 08:14:47
|
| SamC, you are in a unique and exciting position.The majority of professionals in this industry "can build querys", write a stored procedure or two and put together a schema that will suffice.But that is where most people stop. The theory is viewed is 'academic' . . . as outside the realm of essential.Well, that is bull shit.That is the very reason why the vast majority of databases in production under-perform. That is the reason why the majority of databases in production are a mess of duct tape and band-aids.Take the time to learn ... take the time to understand Primary Keys, referential integrity, normalization ... take the time to understand the relational model. Databases and sql are not like writing applications and C, VB, ASP/ColdFusion. The same rules don't apply. You simply can't "moo to the herd" of trade mags, in this world. Databases are different. Databases are set based. Databases are data-centric. Databases are art. Databases are scuplture. Databases are the thoery, that so may people discard.It's hard for me to express what I am trying to get across without a Louisville Slugger in hand ....Jay White{0} |
|
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-07 : 10:19:49
|
| jay, i'm getting goosebumps -- that was fabulous!!thank you |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-08-07 : 10:33:04
|
| Have you hugged your SQL Server today?Justin |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-08-07 : 10:47:07
|
| Hey ! Thanks to everyone for the motivation to learn more.The key to "primary key" that I took from the responses at large was the Louisville Slugger. I knew there had to be more to a Primary Key than forcing a unique identity.No - seriously - I had completely missed the RI impact of the Primary / Foreign Key designation and it directly addresses an issue I've had to enforce with ASP code in our databases - that is - how to clean up all tables when a user is deleted. Do it right with Keys and it "shouldn't" be possible to leave stray rows around.I'd like to tie this thread to earlier thread on keys... Another benefit to using a natural key (like email address) rather than an identity key is this: Should I delete a row in a table "users", and not the corresponding rows in a table "visitations" - then a natural foreign key in "visitations" like "email" will tell me something about the originator while and identity key gives me "a random number" - no information at all. Because the corresponding row in "users" needed to correlate - is gone.SamC |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-07 : 11:09:48
|
quote:
I'd like to tie this thread to earlier thread on keys... Another benefit to using a natural key (like email address) rather than an identity key is this: Should I delete a row in a table "users", and not the corresponding rows in a table "visitations" - then a natural foreign key in "visitations" like "email" will tell me something about the originator while and identity key gives me "a random number" - no information at all. Because the corresponding row in "users" needed to correlate - is gone.
A foreign key constraint isn't gonna care if you use a natural or surrogate key. You can't delete the user and leave the record in visitations if you are enforcing referntial integrity with a foreign key constraint....Jay White{0} |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-08-07 : 11:14:22
|
| Right you are. I suppose identity keys shouldn't leave you high and dry with a random number as I'd suggested.SamC |
|
|
|
|
More on Primary Keys