Using Autoincrement Primary keys as Foreign Keys

Question

When creating a table, there is an option for us to set the numeric primary key as autoincrement where its value increases whenever a new data in inserted.

This primary number can be used for setting the relationship of a table where it is assigned as a foreign key to that table.

So my question is it a good practice for us to use the autoincrement numeric primary as foreign key or should we use the programmatically generated code.

e.g. for a table products we have the fields id (autoincrement primary key), productCode (program generated unique item code. field property also set to unique), Description. and we are going to create a relationship to a transactions table. What field should be used as foreign key: autoid or productCode?

Answer 1

I'd use the auto-increment key. These keys are completely meaningless and in no way connected to the business or something in the environment that might change them, and this makes them much easier to use as foreign keys in other tables.

No matter how much the business is promising that some meaningful key will never change, or that the algorithm that is creating the keys will stay the same forever, these things might change, and when they do there will be problems if you didn't remember to set up cascading updates or something like that.

Answer 2

An autoincrement primary key is just as much programatically created as one you calculate yourself. And it's just as (if not more so) unique.
I've never encountered a situation where people had any second thoughts about using them as foreign keys (where a foreign key on the primary key is appropriate of course, which is normally the case).

Answer 3

Personally, I never make a table without an autoincrement primary key, and I always use this as the foreign key. I've never regretted it. Denormalized tables have their use, but even if it's the kind of table that would probably be ok denormalized, I still add an autoincrement PK. I've even set up an autoincrement PK for tables that have a user-provided and "guaranteed" unique ID column (which becomes informational only).

Why?

1. Arbritrarity. The user has no reason to care or know about it. Thus, they won't be changing it and breaking everything.
2. Guaranteed correctness. If someone fat fingers the user-provided "guaranteed" unique ID, and somehow doesn't notice for a while, they can change it without breaking everything.
3. Expandability. In a few years, there's a decent chance that someone will want to join on that table.
4. Guaranteed uniqueness. "What do you mean there's more than one John Foobar?!"
5. Query simplicity. It's much easier to remember the name of the foreign key column when it's "id" than when it's "LastName" in this table, "RegistrationNo" in that table...

Answer 4

we have the fields 'id' (autoincrement primary key), 'productCode' (unique), 'Description'.

... relationship to a transactions table. What field should be used as foreign key, the 'autoid' or the product code?

If the Product Code (the Natural Key for this table) is unique and unchanging over the entire lifetime of a Product, then it makes no difference which you use as the Primary Key (although, of course, it has to be the Primary Key in order to use Foreign Keys that reference it!).

If there's any chance that the Product Code might change, then use the [surrogate] primary key.

Answer 5

100 % of the world has lost data at some point and when this happens it can essentially isolate the FK based table. DB guys will promise you this can never happen and tout all sorts of recovery capabilities and backing up and logging concepts but it happens. I would say it's a fair shot 50/50 in my experience of enterprise systems either changing business logic that affects your key structure versus data issues that affect your key structure. You have to remember that this includes bad business logic inserting garbage data that now has run a big muk with your auto incremented keys.

Either option can handle integrity ie ACID equally well and come with their own challenges.Really the situation your in should be looked at to see what works best. I would say it is easier to restore integrity to completely controllable data that is logical than it is auto incremented keys.