How to define a primary key?

Question

How to define a primary key?

Muralidharan Venkatraman-417748

Mr or Mrs. 500

Points: 546
More actions
November 13, 2008 at 12:30 pm

#154915

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

"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.

Muralidharan Venkatraman-417748 Mr or Mrs. 500 Points: 546 More actions · Answer 3

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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'd contact the vendor and request their assistance.

Steve Jones - SSC Editor SSC Guru Points: 734605 More actions · Answer 5

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.

Johan Bijnens SSC Guru Points: 135267 More actions · Answer 6

I think you'll get the best grip about it when reading Dr. Codd rules for normalization.

here's an overview: www.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