|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 4:33 PM
Points: 21,728,
Visits: 5,982
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, April 15, 2008 10:12 AM
Points: 29,
Visits: 19
|
|
The only reason I can think of not to, is when you have data which is referenced in groups only, and not by a primary key, ever. Say for example, some kind of error logging, where your design dictates that you never need to access individual rows (uneditable, auto-deleted).
Basically, I can't think of anytime a primary key is harmful unless you are worried about index/memory space performance. Odds are any performance impact could be more easily offset with more hardware than the manpower to optimize.
John
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, November 02, 2009 2:31 PM
Points: 147,
Visits: 446
|
|
A unique key or a primary key? Think about the difference for a minute.
If you want to be academic then, yes, you need a unique key. Here in the real world I'm sure the correct answer is "it depends". Personally, I don't treat my database like an expensive, high-performance, 24x7 spreadsheet application. So, I add UK's/PK's to my tables even as surrogates.
James Stover, McDBA
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, September 10, 2009 12:14 PM
Points: 377,
Visits: 37
|
|
| An audit table perhaps? i.e. a copy of the master table with extra columns for 'operation, datetime, userid'. You could then make a 4+ column primary key, but it hardly seems worth it.
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Wednesday, November 04, 2009 5:45 AM
Points: 386,
Visits: 371
|
|
Even for an audit table I would use a surrogate primary key because I may have need to join to that. The article makes a great case for this case being the exception, but it still seems to me that the table is temporary in the sense that the data is being recreated for each report run. If the same question had been posed to me regarding permanent tables (ie the table itself is not just permanent, but the data within will remain) I would say "So far all the tables I have seen without a primary key were evidence of a design issue. There may be rare situations that might make that necessary, but as a near universal rule, all permanent tables should have primary keys."
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, November 04, 2009 2:41 PM
Points: 497,
Visits: 96
|
|
The only place I can think of where a PK would not be workable would be in the very rare occasion where you want to use a nullable column as one column in a composite key. I've never done this, but have considered it. For example, one column in the key references another table. However, occasionally, you find an "unknown" for that key value. You could add a record to the referenced table called "unknown" or you could just leave that column null in the referencing table. The problem with using null is that you would not be able to use a PK constraint. You would have to use a unique index in that case.
Russel Loski, MCT, MCITP
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 7:08 AM
Points: 427,
Visits: 253
|
|
From a design perspective, I think that Messrs. Codd and Date would have something to say about the need for a primary key. Primary keys need have nothing to do with indexes, depending on the target RDBMS. In tools like ERwin, the creation of code to create an index for a primary key is optional, but, SQL Server does not work that way, at least not in my experience (v4.2.1 forward).
What the primary key is supposed to represent is a way to uniquely identify a row. I can see valid arguments why a null value should be allowed in a composite primary key component, but, again, SQL Server does not allow that.
In my own case, I would only argue in favor of throwaway tables not having primary keys because they are used to load data where the relationships are not as well defined and the data format is the only one available. Other than that, every table has a primary key. That is not to say the primary key is always the clustered key. Uniquely identifying a row and retrieving multiple rows efficiently are not necessarily the same thing.
------------
I know you believe you understand what you thought I said, but I'm not quite sure that what you heard is what I meant.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 6:39 AM
Points: 9,
Visits: 111
|
|
| Here's another reason why we should have a PK. We have a large vendor database we want to replicate for reporting purposes. We wanted to use transactional replication but the vast majority of the tables did not have primary keys, and therefore we can't use transactional replication.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Friday, October 16, 2009 9:59 AM
Points: 1,717,
Visits: 506
|
|
How about a table with only one row?
Mattie
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 7:08 AM
Points: 427,
Visits: 253
|
|
From what I remember of Normal Form, design constraints like primary keys are not related to how many rows the table contains. Either their is a unique way to identify a row, or there is not.
------------
I know you believe you understand what you thought I said, but I'm not quite sure that what you heard is what I meant.
|
|
|
|
