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.
Unique Id Columns
Moderator: General Moderators
Re: Unique Id Columns
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.