December 10, 2007 at 5:07 pm
Comments posted to this topic are about the item Do We Need a PK?
December 10, 2007 at 10:45 pm
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
December 11, 2007 at 12:13 am
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
December 11, 2007 at 1:22 am
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.
December 11, 2007 at 3:20 am
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."
December 11, 2007 at 4:53 am
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, MCSE Business Intelligence, Data Platform
December 11, 2007 at 5:30 am
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.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
December 11, 2007 at 6:08 am
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.
December 11, 2007 at 6:26 am
How about a table with only one row?
Mattie
December 11, 2007 at 6:30 am
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.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
December 11, 2007 at 6:30 am
I'm with MattieNH in that a table where you know you'll only have one row (such as a table that holds system configuration settings, etc) wouldn't need a PK. In fact, you might have to force a PK that doesn't make sense within the scope.
December 11, 2007 at 6:38 am
ok... so what do we get from a primary key... I support around 30 highly transactional DB's(Same db different location) in excess of 300GB. 10 of which are over 700 GB and several targetted in the 2TB range within a year or two. That said, this database does not have a primary key defined. It does have a unique column however and there is a nonclustered ordered index on this column by design. This column is also stored in all tables with related data thus acting like a PK and allowing for quick seeks for data. Your overall design should drive whether you define a PK or not.
Unfortunately or Fortunately the developers of the db I support created the base in Oracle and to this day still feel they are far smarter than MS and any optimizer that MS can write so they do a lot of strange things. Those strange things work oddly well.
I am a firm believer you need a unique value that you can create an ordered index on in each table. I do not feel it should be an absolute that it is defined as a PK. I am also an extreme opponent to ever using a quid as a PK as they cannot be used efficiently as an ordered column for seeks. They also require more storage space and thus more memory. I would much prefer to see a valid indexing strategy than the use of PK's any day. Often times your best Clustered index is not necessarily your PK anyways.
December 11, 2007 at 7:14 am
My 2 cents:
Logically, every table should have a primary key. Once we move into the physical realm, my philosophy is "less is better" or "prove you need it". My thinking is, any table without a relationship to another another table or a table with NO children does not NEED a primary key. To add one would simply add overhead.
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
December 11, 2007 at 7:22 am
A one row table is interesting. Perhaps that makes sense, but really I'd define a PK anyway. Never know when you'll add another row for some reason.
Robert H, you've got a primary key. You've defined it and use it as a PK, have FKs, just don't have them defined? So why not? It would be no change to the functionality, but it would make it easy for anyone else to determine the PK on the table.
A nullable composite key is essentially what I had in my report table, though in this case, you don't really have a natural key based on data. You'd need a surrogate key and as I mentioned, I'd probably do that now if I were building the report.
December 11, 2007 at 7:30 am
There is a logical PK however not a defined one. The developers decided to handle the field values in code instead of working with functionality built in. This is also allowing them to be a lot less platform dependent.
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply