Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jagd29
Starting Member
5 Posts |
 Posted - 2008-06-17 : 15:44:09
|
| I'm new to these forums, and I'm not a database developer, per se, so please forgive me if I make any newb'ish comments.I have a lookup table called tblCars, that has two columns, cars_id and cars_title. Typically what I do with tables like this is I make cars_id an autonumber, and cars_title the primary key. The cars_title would contain unique data such as Ford, Chevy, Toyota, etc, which is why I like to make it the primary key (ie - guarantee that it remains unique and no duplicates are ever placed in it).I would then create an index on cars_id so that I could use it in foreign key constraints.However, I'm being told by a number of people that it is incorrect to make cars_title the primary key, and that the autonumber field should be the primary key. Yet I am having trouble arriving at a real good reason as to why this is the correct way to do it. I like the warm-fuzzies that I get knowing that no one can accidentally insert a duplicate car title into tblCars because of the primary key constraint.Thanks in advance for any thoughts or insights on this. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 15:51:25
|
| Your option is to use a natural key. Their option is use a surrogate key.The natural key versus surrogate key is a very debated topic. You can search these forums as well as other sql forums for these debates if you are interested in reading them.If the data in cars_title ever needs to be modified, then it is much easier with a surrogate key. For example a userid column. I got married a couple of years back and switched my last name. My userid had to change as a result. It's simple with a surrogate key as you only have to change the parent table. But with a natural key and child tables, it's a bit harder.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
jagd29
Starting Member
5 Posts |
Posted - 2008-06-17 : 16:37:51
|
| Thank you for the helpful input. It looks like I need to take some time and research natural keys vs. surrogate keys.Given your example, I wouldn't use a natural key if all I had to uniquely identify the person were first, middle and last names. it seems clear that the best choice would be to use a surrogate key.However, whenever I use natural keys, I do so because I'm 100% sure it'll never have a duplicate (ie - you could never have two Fords). All the same, maybe I am just straining at a gnat here. It's likely more just my ego than anything. Thanks again for the help. :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 16:47:03
|
| You don't need to use a primary key in order to guarantee uniqueness. You can also use a unique index or unique constraint (preferred over index). So if you go down the surrogate key approach, make sure to add a unique constraint to your cars_title column.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jagd29
Starting Member
5 Posts |
Posted - 2008-06-17 : 18:08:26
|
| Understood.I'm getting the impression that popular opinion would be the surrogate key approach. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 18:10:53
|
| The answer to the debate should be "It depends on the situation." There should not be one answer for every single table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-06-18 : 06:07:23
|
| As tara says it depens on the situation but I tend to use surrogate keys whenever I can unless the natural key is something which is uniqe "by default", like a sosial security number or a phone number. Natural keys also has a tendency to rely on more than one column which makes it tedious to write joins, and I try to the best of my ability to keep primary keys as one single column unless it's a junction table. I'd go for having the ID as a primary key with a clustered index and put a unique constraint on the car model...--Lumbago |
|
|
|
|
|
|
|
|
Primary Key?