September 10, 2009 at 5:07 am
I don't get it! Talking about relational models, where data is supposed to be denormalized, and not defining a primary key on a table? Of course on paper you don't need a primary key but if you have to ensure data consistency in your database a primary key is not a bad idea, isn't it?! How do you ensure that you don't have duplicates on a table which has a 1:m relation to another? Enlighten me!:cool:
September 10, 2009 at 5:12 am
liebesiech (9/10/2009)
I don't get it! Talking about relational models, where data is supposed to be denormalized, and not defining a primary key on a table? Of course on paper you don't need a primary key but if you have to ensure data consistency in your database a primary key is not a bad idea, isn't it?! How do you ensure that you don't have duplicates on a table which has a 1:m relation to another? Enlighten me!:cool:
By having a non-null unique constraint on the column
September 10, 2009 at 5:35 am
liebesiech (9/10/2009)
I don't get it! Talking about relational models, where data is supposed to be denormalized, and not defining a primary key on a table? Of course on paper you don't need a primary key but if you have to ensure data consistency in your database a primary key is not a bad idea, isn't it?! How do you ensure that you don't have duplicates on a table which has a 1:m relation to another? Enlighten me!:cool:
You just require at least one candidate key per table. Normalization deals with dependencies on candidate keys and superkeys, not primary keys. Primary keys are actually irrelevant as far as the Normal Forms are concerned as long as you have at least one candidate key. The fact that many people think normalization requires primary keys is an example of the kind of confusion I'm talking about.
September 10, 2009 at 9:01 am
This discussion seems a bit pointless.
Although SQL Server could be implemented without having PRIMARY KEY, it’s not going to happen without some overwhelming advantage to getting rid of it. Nothing on this thread points to a reason that would justify that.
September 10, 2009 at 9:31 am
Michael Valentine Jones (9/10/2009)
This discussion seems a bit pointless.Although SQL Server could be implemented without having PRIMARY KEY, it’s not going to happen without some overwhelming advantage to getting rid of it. Nothing on this thread points to a reason that would justify that.
You are right. The question was really just expressing my frustration with the amount of rubbish that is being propagated these days about "primary" keys - especially in forums such as this one. It seems like most times when people say the words "primary key" you can expect that they are wrong about something. I'd like to see the term eliminated or at least put in its proper context, which it seldom seems to be. Removing it from DDL syntax would be a start.
September 10, 2009 at 12:09 pm
I'm not sure about this at all. It has been a while since I was in a database class, but I seem to remember in most of them that the discussion on Primary Keys on tables usually came up about the time we started discussing logical to physical designs. There wasn't much said about Primary Keys before that, just Candidate Keys. Of course when you get to 3rd normal form, you better have one (Candidate Key) or else you did something wrong.
September 10, 2009 at 4:14 pm
David Portas (9/10/2009)
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.
Yes, just like I said in my example. But I really enjoy the convenience of being able to label one of those keys as the PRIMARY KEY instead of having to figure out which one will likely be the most important one.
As you say, there's really no difference between them... so, why not just leave them as they are? Besides, it provides a source of entertainment when folks talk about them. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2009 at 4:31 pm
It also gives the database users an idea of which candidate key may have the clustered index on it. I know that Primery Keys do not have to be clustered, but since it is the SQL Server default option, report writers and other users may take a Primary Key constraint as a sign that it would be the best candidate key to use for most situations. In the ideal world, anyone writing queries should look at all of the indexes on the table to see what is available, but we all know that that doesn't happen. Everyone, whether they misuse the term or not, is familiar with Primary Keys so having one there could help guide better query writing from non T-SQL ninga's.
September 11, 2009 at 3:36 pm
In my opion, the best reason for having a PK (and FK for the matter) defined is that it is an object and is stored in the database. Having that data available to me helps me to understand the relationships between my tables and how to properly join them in queries. When you have a large database, this is an invaluable object to have.
September 12, 2009 at 6:09 am
I am agree with Speed's comment
Sql Engine make page indexing on primary key. rather table indexing. as well as time estimation become easy for sql engine.
September 12, 2009 at 4:46 pm
it.tomar (9/12/2009)
I am agree with Speed's comment
In the posts above, who are you referring to as "Speed" because I don't see anyone with that particular handle.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2009 at 1:19 pm
John Rowan (9/10/2009)
It also gives the database users an idea of which candidate key may have the clustered index on it. I know that Primery Keys do not have to be clustered, but since it is the SQL Server default option, report writers and other users may take a Primary Key constraint as a sign that it would be the best candidate key to use for most situations. In the ideal world, anyone writing queries should look at all of the indexes on the table to see what is available, but we all know that that doesn't happen. Everyone, whether they misuse the term or not, is familiar with Primary Keys so having one there could help guide better query writing from non T-SQL ninga's.
I think you're actually helping to make David's point for him. If, by convention, we assume that the CK that was arbitraily chosen as the PK is associated with a clustered key, it leads to confusion/incorrect choices when users rely on that assumption, and then you end up with potential subtle performance issues to track down.
On the other hand, if we had better "encouragement" from the design of the language to declare the logical CK's, and declare the indexing as a separate step (say, more closely aligned with a transformation from the logical to a physical implementation), it would help users keep the distinction in mind and reduce the logical/physical confusion so common in practice.
TroyK
September 14, 2009 at 2:13 pm
cs_troyk (9/14/2009)
John Rowan (9/10/2009)
It also gives the database users an idea of which candidate key may have the clustered index on it. I know that Primery Keys do not have to be clustered, but since it is the SQL Server default option, report writers and other users may take a Primary Key constraint as a sign that it would be the best candidate key to use for most situations. In the ideal world, anyone writing queries should look at all of the indexes on the table to see what is available, but we all know that that doesn't happen. Everyone, whether they misuse the term or not, is familiar with Primary Keys so having one there could help guide better query writing from non T-SQL ninga's.I think you're actually helping to make David's point for him. If, by convention, we assume that the CK that was arbitraily chosen as the PK is associated with a clustered key, it leads to confusion/incorrect choices when users rely on that assumption, and then you end up with potential subtle performance issues to track down.
On the other hand, if we had better "encouragement" from the design of the language to declare the logical CK's, and declare the indexing as a separate step (say, more closely aligned with a transformation from the logical to a physical implementation), it would help users keep the distinction in mind and reduce the logical/physical confusion so common in practice.
TroyK
I doubt that separating the indexing from the declaration of primary keys or unique constraints would make anything better.
My experience is that most physical implementers fail to declare an index on foreign key columns, so there is no reason to think they would do better on primary or candidate keys if the index was not automatically created by the database engine when the constraint was declared. You could argue that indexes on foreign key columns are not always needed, but I don’t think most implementers are even aware of the need to consider it.
Also, as a practical matter, it’s hard to imagine an efficient method of enforcing a primary key or unique constraint without an index.
I think it would be good if the development tools had default best practices. For example, make the primary key default to a clustered index, create a index on a FK by default, etc. Then more advanced designers could create the tables the way they want, and less skilled developers would operate with a “fail safe” design.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply