November 13, 2008 at 12:30 pm
Given a table with lots of data, what is the approach u take to determine the primary key?
My understanding is, a primary key should be not null and uniquely identify each row.
Is there like a standard script which can go over the tables and make that decision?
Thanks
Murali
November 13, 2008 at 12:41 pm
Muralidharan Venkatraman (11/13/2008)
Is there like a standard script which can go over the tables and make that decision?
None that I've ever seen. While it's possible to tell currently what columns are unique, there's no way to know if that's a fluke, or if the column will always be unique.
The choice of a PK is an important part of designing a database, it's a value that identifies a row of data. It's not something that should be selected at random.
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 13, 2008 at 12:45 pm
"My understanding is, a primary key should be not null and uniquely identify each row"
A primary key must be not null and must uniquely identify each row.
"Is there like a standard script which can go over the tables and make that decision?"
No. This is something the table designer must do when they create the table.
November 13, 2008 at 12:54 pm
I have been handed over a sql server 2005 db with 100+ tables which is used by an important application. Unfortunately, these tables were designed without primary keys. I found it when i was trying to build replication because the fundamental requirement is to have a primary key.
I can reverse engineer all these tables with erwin to build a data model but not sure how i am going to uniquely reference my data since the application is brought from a 3rd party vendor.
Any thoughts?
November 13, 2008 at 1:04 pm
I'd contact the vendor and request their assistance.
November 13, 2008 at 1:09 pm
Usually the PK is a field that obviously identifies what's in the tables. Often there is an "ID" field with an identity or integer value in there. If you have any uniqueidentifier fields, they can be PKs.
Social Security number, employee ID, company name, all can be primary keys if they are unique values.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 13, 2008 at 1:13 pm
I think you'll get the best grip about it when reading Dr. Codd rules for normalization.
here's an overview: qa.sqlservercentral.com/articles/Advanced/coddsrules/1208/
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply