Can anyone think of a good reason for NOT having a PK?
-
My DBA intentionally let some code in that dropped the PK from a table with no replacement... can anyone think of a good reason not to have a PK? Not even a surrogate key? Seems a bit insane to me but I thought I'd ask...
--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) -
Not sure if it fits with your scenario, but what about if that table only ever had one row of data
--------------------
Colt 45 - the original point and click interface
-
- maybe all he wants is a fast logging-kind of table.
(pumping it away to another server for querying/investigating and providing it with indexes overthere 
IMO even this kind of tables should have a unique key for every row
A regular table should have a PK (or unique key replacing it)
(just like Codd taught us)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Phil's reason is the only one I can think of. Such tables are not really "tables" per se but are represented as tables in SQL Server in the absence of anything else. I suppose you could have a setting=value sort of table with two columns rather than a wide table with a single row where each column contains a setting's value.
-
Thanks, guys. Yeah, the only thing I could think of was a minimal logging bulk insert staging table but he's not using it for that... it's a permanent cross-reference table being used to resolve multiple-to-multiple joins. I just can't imagine not having a primary key on those two columns.
--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) -
.. multiple-to-multiple ..
indeed, it's best to avoid duplicates, so at least a unique constraint may be advisable. If it should be a primary key ?? (are there going to be child tables related ?)
In most cases, many to many relationships contain extra info about the relationship itself (e.g. datecreated/dateexpired, resoncodes,... kind of stuff)
I'd certainly support it with the FK-indexes and provide a clustering index somehow because of maintenance issues with heaps.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Good reason: Brain fart?
I have a few tables without indexes, but those are for like where I am storing information about logins and I am more worried about inserts being fast. That table has one stored procedure that runs against it every morning to summarize which logins where used the previous day, store that information in another table (with indexes!) then delete the information.
-
I could see allowing duplicates in a table for some reason, but you'd still want a PK in case you ever needed to work with a row. Even in a logging or counting table you'd still want a PK, even if it were a surrogate.
-
Yep... that's the position I was taking... just wanted to make sure there wasn't some exotic reason I was missing. Thanks for the feedback, folks.
--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) -
I think Anders gave the best answer!! The only other plausible reason is that he's trying to make you look better by intentionally doing bad things.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Heh... maybe that's it! I should thank him, huh?
--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) -
Even that type of table should have PK, just not necessarily a clustered key.
The only two examples I have ever seen is temporary working tables for bulk import (where you do not want to drop rows or fail import before you can check and vailidate data), or a table with a single row.
Mark -
In SQL Server 2005 it is possible to use the ROW_NUMBER function to sort data easily without a primary key. Not sure if this was your DBA's intention, but may be worth asking.
-
Not sure what you mean, Ronda. What kind of a sort are you talking about?
My real concern is over "entity integrity".... or, if you will, a table without a Primary Key is only a "heap" and many applications will fail (like Enterprise Manager) if you try to modify or delete a row that has been duplicated unless there is a tie breaker like a primary key column (clustered or not). And, Even if the table is not exposed to an external application, there are usually some performance gains to be had by having a primary key constraint and the accompanying index that results.
--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) -
I'd be curious to see the DDL for the table and its associated object(s) ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply