November 8, 2011 at 2:55 am
[font="Tahoma"]
Hello Friends,
I am in the process of creating Data Dictionary for a DB and I found that there are many tables in this DB which do not have a Primary Key but has Foreign Keys...Is this a common scenario?
Thanks
Murali
[/font]
November 8, 2011 at 3:05 am
All too common in badly designed databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 3:07 am
It depends.
For tables that are designed to not being referred to by other tables, a PK may not make sence.
That is a design issue.
However, it is advised a table has a unique key to guarantee to address a single row.
In some cases, even a UK isn't defined, because people want a "fast entry table" and decide to do garbage handling afterward. For this kind of tables, I haven't seen FK defined either, because the designers consider that as a slowdown factor as well.
Do these tables have unique indexes ? ( = AK/UK)
In sqlserver FK can refer to a PK or to a AK.
Another thing worth mentioning is that SQLServer, by default makes a PK the clustering index, unless there is already a clustering index defined or you specify nonclustered.
Some people persist clustering being a bad thing.
It isn't! One just needs to give it some thoughts.
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
November 8, 2011 at 3:09 am
I see it all the time..
though common does not mean that it is good practice.
November 8, 2011 at 3:18 am
[font="Tahoma"]
No Identity, No index, nothing.. Simpley foreign keys.. As you have all mentioned this should not be a best practice..
[/font]
November 8, 2011 at 3:21 am
no it's not good pratice, and will run like a dog if there is a lot of data in there.
November 8, 2011 at 3:27 am
MuraliKrishnan1980 (11/8/2011)
No Identity, No index, nothing.. Simpley foreign keys.. As you have all mentioned this should not be a best practice..
Foreign key from this table or referencing this table? If referencing, there must be a primary key, unique constraint or unique index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 4:51 am
[font="Tahoma"]
Hi Gail.. These tables are having foreign keys which are referencing other tables...These tables are not referred by other tables....
[/font]
November 8, 2011 at 5:59 am
MuraliKrishnan1980 (11/8/2011)
[font="Tahoma"]Hi Gail.. These tables are having foreign keys which are referencing other tables...These tables are not referred by other tables....[/font]
What kind of tables are we talking about? are these small lookup tables like "States" or "Countries" or, are these large operational tables?
Either way I would work on finding a natural unique identified for each one of them, once you find it make it a PK and be happy until you find the next design flaw 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2011 at 6:29 am
A primary key is not needed for log tables or certain staging tables. Often I see people add an identity column to these tables anyway... whether it is needed or not. I suppose they have been taught that ALL tables should have a primary key so they do it without consideration to the useless overhead.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply