How to Replace a Natural Key with an Autonumber

In this Microsoft Access tutorial I'm going to teach you how to replace a Natural Key, such as a phone number, social security number, product code, etc. with a Surrogate Key (Autonumber). 

Stacey from Enfield, Connecticut (a Silver Member) writes: I am planning to replace my primary key of a major table in my database that is in production, with an autonumber as recommended in the Access Expert classes.  I will have a lot of relationships to update after the fact. Any advice or tips would be appreciated. I use a number generated by another system, but have to manually enter it. The table is related to a handful of others.

Marko from Valrico, Florida (a Platinum Member) writes: I'm pondering the same exact question. I have a main table with a manual primary key and that drives all my other tables. The primary key is a lot id. It's a permanent id assigned to a piece of property and can never change and I thought I was safe with it; however if I take on more clients there may be issues with the lot id as primary key. The key is set up as an address of 2575 Smith Lane would get a lot id of 2575SL.

Prerequisites

• Relationships: https://599cd.com/Relationships
• Update Queries: https://599cd.com/UpdateQ

Links

• AutoNumbers Good or Bad?: https://599cd.com/GoodOrBad
• Other Autonumber Videos: https://599cd.com/AutoNumbers

Recommended Courses

• Access Expert 1: https://599cd.com/acx1
• Relationship Seminar: https://599cd.com/RelationshipSeminar
• Action Queries: https://599cd.com/ActionQuery

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

Keywords

access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Natural key, surrogate key, autonumber, primary key, pk, foreign key, fk, replace a natural key with an autonumber