Newbie: 1.Primary Key Always? 2.Use Natural if no dups?

[AustinOne]

Two Newbie Questions to which I could not find existing answers:

1) I see in MSSQL2000, in the sample Pubs database, the roysched table and the discounts table have no primary keys? Isn't this a relational best-practice no-no? In other words, shouldn't you always have a primary key, even if you have to resort to using a unique "identity" column? If so, why then would MS do this as an example?

2) If you have a table that has a naturally occurring unique key candidate column, such as a table of automobile brands, Ford, GM, Toyota, Mercedes, etc., which are unique and you want to enforce this (regardless of whether this column is the primary key or not), then is there any reason why you would add or need an identity column and use that as the primary key instead of the brand column, which you want to make unique anyway?

 

[gmmastros]

In my opinion...

1) I always have a primary key in every table I build. no excpetions.

2) I don't ever use naturally occurring keys. Ever. I always use an integer column (or several integer columns) for my primary keys. The problem with using naturally occuring primary keys is that you may some day want to change the value of that key. For example, if Ford were to change the name of the company, and the column was NOT a primary key, then... no big deal. If Ford was the primary key, then you would have a more difficult time changing it. For example, you could have another table for the model, like so...

Make Model
---- -------
Ford Mustang
Ford Escort

If you have a proper foreign key constraint set up between the tables, then you won't be able to (easily) change the data.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

1) I also always use a primary key.

2) I use a combination of natural keys and an integer column. The data structor is what will deturmin what method I use. For example I have a ticketing system which I built when I got to this company (I seam to build them at most of the companies that I go to). Several tables use an integer value as the primary key in order to keep updating the value of the key easy. In this case I'm dealing with the problem type table. It's simply a list of the possible problems that can be selected in the drop down. The table has two fields the id and the text value for that id.

Now in the employees table the employees NT login is the primary key, as they are already unique across the network and are removed when the employee leaves the company (so that I don't have to worry about the next person with the same name getting the same login).

Then there are tables which have more than one column as the primary key. As my ticketing system supports more than one queue (departments) there is a two column table which makes a many to many relationship between the queue table and the employee table and employees can be members of more than one queue. This table has both the employee login and the queue id as the primary key.

It is important to know the difference between the primary key and a unique index (or constraint).

When building a fully relational database the example that qmmastros shouldn't occure very often. If you were going to make the car brand the primary key then there wouldn't be much use to having the lookup table at all.

You should also keep in mind that the Microsoft examples aren't always the best. They tend to cut corners that they shouldn't be cutting.


Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[AustinOne]

Thanks, gmmastros and mrdenny, for taking the time to share your insight and experience!

AustinOne