Unique Id Columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ATLChris
Forum Newbie
Posts: 15
Joined: Mon Feb 09, 2009 6:21 am
Location: Atlanta, GA

Unique Id Columns

Post by ATLChris »

I just wanted to ask for peoples preferances on this topic. Should every database table have a unique "Auto Increment" column?

i.e. I have 2 database tables, one is "users" it features user account information including (userid which is unique & auto increment). The other is "user_settings" it features the users account settings. In "user_settings" I refer the other tables userid. Userid is unique to each row as each user only has 1 set of settings.

Now, my question is, should I have a column in "user_settings" called settingid and make it unique and auto increment, or is the unique userid column enough?

I guess it is a matter of preference, but I wanted to ask what you alls thoughts are.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Unique Id Columns

Post by Eran »

Not every table should have an auto-incrementing key. A very good example is the one you brought up - a 1-to-1 relationship should use the foreign key (userid in this case) as the primary key. In other cases you might have a good natural key (such as a hash or a unique value that would never be changed). Use an auto-incrementing integer mainly when a good natural key is not available.
Post Reply