Primary key

[netJP12L]

Hi, I have a Location table. I am wondering what should be its primary key.

LocationTbl

ID identity (1,1) not null
LocationCode not null
LocationName null
LocationDescrption null

Thanks

[Not Active]

Is this a trick question? Obviously the PK is the identity column.

---

I know the language. I've read a book. - _Madmatt

[SilimSayo]

ID seems like an obvious choice but it won't have any meaningful info to end users. If LocationCode is unique and is meaningful to users, I would choose it as the primary key.

[Jorgen Andersson]

I think you're missing the point with the primary key. It should never be visible to the end users!

"When did ignorance become a point of view" - Dilbert

[Corporal Agarn]

I do not agree that the primary key should never be visible to the end user but it is not a must have.

[Corporal Agarn]

As stated the ID should be your primary key with indexing on the LocationCode and possibly LocationName depending on criteria used to query the data.

[netJP12L]

Thanks fellows for replying to my post. I had no intenions to trick you. It just sometimes little things create a confusion. I think, I am going to keep LocationCode unique. So, would you say i shouldn't keep ID (an identity column) in my Location table at all?

[Corporal Agarn]

The ID column is a good idea. I would suggest you keep it.

[Jorgen Andersson]

One of the key requirements for a primary key is that it must not change over time, and one of the best ways to achieve this is to use a surrogate key. Preferably an integer as most databases are tuned for queries on integers. As it's only a part of the plumbing, the surrogate key has no need to ever be visible outside the DB. In particular, it should never be revealed to the user. This allows the DB administrator to change the representation of the keys at will if necessary. I can think of very few circumstances when there's a sense in making a key visible to the end user. For example when it's used as a parameter in webpage request.

"When did ignorance become a point of view" - Dilbert

[Luc Pattyn]

I agree. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

---

[Luc Pattyn]

If locationCode gets its values assigned by someone external to the system, then it should NOT be used as the PK of your table. Primary Keys must be yours, and yours alone. So nobody can entice you to suddenly change their values. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

---

[Not Active]

Well said.

---

I know the language. I've read a book. - _Madmatt