Replace Natural Key
By Richard Rost
3 years ago
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
Links
Recommended Courses
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
Intro In this video, I will show you how to replace a natural key, such as a social security number or a product code, with a surrogate key (autonumber) in Microsoft Access. We will cover why surrogate keys are often preferred for table relationships, walk through updating your table structure to add an autonumber primary key, and demonstrate how to update related foreign keys in other tables using an update query. I will also discuss common issues and best practices when converting existing databases, so you can set up your Access tables for more reliable relationships.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to replace a natural key with a surrogate key, also known as an autonumber.
Before we get started, you should have a general understanding of the difference between a natural key and a surrogate key. A natural key is based on some number you find in real life, like a phone number, a social security number, or a product code, like when your vendor gives you a product that has a number on it. A lot number, like real estate lot numbers, that kind of stuff. Those are all numbers that you can find elsewhere in the real world.
A surrogate key is something completely generated by the database. It could be an autonumber, whether incremental or random. It could be a globally unique identifier, any kind of autonumber that's generated by the system as a surrogate key. In Access databases, especially for beginners, we stick with autonumbers.
Now, if you asked a hundred database professionals which is better or which you should use, you'd probably get about a 50/50 split. I personally happen to be in the surrogate key camp. I like to use autonumbers. I have been working with Access since 1994, and I pretty much stick with those. Even if I come across a situation where I'm presented with a natural key, like a phone number or a social security number, I will still assign an autonumber and keep that natural key in the table, because you don't want to lose that data. But I am going to have an autonumber for the relationships between the tables and for tracking uniqueness.
I have a whole separate video on whether or not autonumbers are good or bad. There is the link right there. I will put it down below; you can click on it if you want to get more information on that topic and my reasons why I like autonumbers. I cover that stuff in a lot more detail. Here are all the topics we cover. We talk about lots of different stuff. Go watch this video if you are curious about that.
Today's video is not going to debate that topic. Today, we're going to assume that you're on my side of the debate, and you agree that you should have an autonumber as your primary key and not something like a social security number. I'm going to show you how to take a database that's got a natural key like that and replace it with an autonumber as the primary key. We are still going to keep the social security number. You do not want to lose that, but we are going to add an autonumber and then replace all the foreign keys.
Today's question actually came from my Facebook group. I found this this morning. I do not like Facebook. I have a whole separate video on why I hate Facebook, but I still do have a Facebook group. It's been there for years. Lots of people in it, including students of mine. So I check it once a week or so. I prefer to have the discussion on my website, but if you are a Facebook user, go ahead, you can post in that group.
Stacy from Enfield, Connecticut, one of my silver members, says she is planning to replace her primary key with a major table in her database that is in production with an autonumber, as recommended in the Access Expert classes. Yes, she will have a lot of relationships to update after the fact. That is the problem - updating the relationships. She uses a number generated by another system, but has to manually enter it. The table is related to a handful of others. In other words, you get a number given to you by someone else that you have to use. So we're going to keep that number. We're not going to get rid of that number, but we're not going to use that number for relationships between the tables.
Following that, Marco from Valrico, Florida - if I'm pronouncing that right - one of my platinum members, responded and said he is pondering the same question. He has a main table with a manual primary key, a natural key that drives all his other tables. The primary key is a lot ID, a permanent ID assigned to a piece of property and can never change. He thought he was safe with it. However, if he takes on more clients, there may be issues with lot ID as a primary key. The key is set up as an address - for example, 2575 Smith Lane would get 2575SL. I see this a lot. I had a customer a few years ago that had a customer ID that was generated from the first two characters of their first name, the last three characters of the last name, and something from the address. I forget exactly. It's been a couple years ago. Actually, it was more like 15 years ago, but he started running into duplicates, so we had to put a 2 or a B or something on the end of them and it was causing major problems.
That's one of the major reasons why I prefer surrogate keys, autonumbers. Also because sometimes you think you've got a number that can never change, but it can change. I knew, not one of my customers, but I had an associate of mine, one of my consultant buddies. He actually had a client who had their social security number changed. I do not remember the whole details, whether it was witness protection or whatever, but they changed it, so it caused havoc in his client's database.
So do not use natural keys as something to make relationships with.
All right, so let's get to it. As an expert class, or an expert to me means you are beyond the basics, but you're not quite a developer. You do not need any programming for today's class. This is all just relationship stuff. If you have not yet watched my relationships video, go watch that first. Very important. Also, we are going to use an update query to update all of those foreign keys. If you have never made an update query before, go watch that first. These are both free videos. They're on my website. Go watch them and then come on back.
Before we get started, make sure you back up your data. Very important.
All right, so here I am in a blank Access database. We are not using the TechHelp database because the TechHelp database is set up correctly. We're going to set up a database incorrectly to begin with.
Let's create a table. Let's create a customer table and an order table. We are going to set up the customer table with a bad primary key.
Go to Table Design. We are going to make the primary key the social security number (SSN), and that has to be short text. Then we have FirstName and LastName and whatever other stuff you want to have for your customer. Save it. If you save it at this point, Access is going to ask you to make an autonumber primary key. We do not want to do that. We're going to just set SSN as the primary key using the primary key button. That will set it as Indexed (No Duplicates), and now we can save the table as our CustomerT.
Let's put some data in. 111-22-3333, Rick James. Jim Kirk. Miles O'Brien. And one more, we'll do Joseph. So we have four customers, and this is set up with the SSN as their primary key.
Now we're going to set up an order table. I'm going to just slide this over so we can still see it. Let's create another table. Create, Table Design.
Let's say at least you were smart enough to make an OrderID in this table and make this one an autonumber. If not, if you have natural keys throughout your entire database, that's okay, you're just going to have to repeat this step multiple times. If you have an OrderNumber that is also somehow a primary key - like I had a client once who was doing it on paper, and he literally was still working off those old preprinted guest checks. Right, you get the pads of paper with preprinted numbers on them. He was using those. Okay, fine. In 1970, that's okay, but that's hard. I've had to drag some people into the 21st century kicking and screaming.
We will assume, and by the way, it is easier to start from the bottom-most tables and work backward. So let's say you've got Customer, you've got Orders, you've got OrderDetails, you've got Products. Start with the bottom table in that chain and replace the product code with a ProductID and then work your way back up. I'm going to show you one relationship. If you have 15, 20, 30 relationships, you'll have to do this multiple times. The good news is you should only have to do this once. Once the database is set up properly, that's it. So this should hopefully be a once-in-a-lifetime thing for you unless you're a consultant like I used to be, in which case I did it about 20 times.
I was a database consultant right in that sweet spot from the 1990s until about 2010 when a lot of companies were still working on old DOS-based systems, even paper systems, and they were computerizing. A lot of you younger developers now are probably working with people who have been computerized, just maybe not the right way.
Anyhow, you can tell I'm in the mood to talk today. I have had too much coffee.
So, we've got the OrderID. Now, how are we relating? How are we going to tell what the customer is who placed this order? Normally we would use a CustomerID, but we do not have one, so we have to have the SSN here. That is also going to be short text. Then, whatever other order information you want, like OrderDate or Amount, I'm not going to worry about those; we're just going to put a couple of fields in here. Save this as my OrderT.
No primary key defined? We put an autonumber in for this one, so we're good for that at least.
Let's put some sample data in here. Let's say that Jim Kirk placed a couple orders. Generally, in your order form, you are going to have a combo box where you can pick the customer by saying their name. That combo box is going to be bound to their social security number instead of an ID. So what's going to get stored in your order table? The customer's social security number. Let's say he placed three orders. Maybe Joe Smith placed a couple orders. Then Jim Kirk placed another order. Rick James came in and placed four orders. Miles O'Brien placed an order. So that's the data as it looks like in your order table. Put some order amounts in here; it doesn't matter. These numbers are whatever, this is all the other order data. You get it.
That's what we have to replace in here with a CustomerID that we do not have yet. You do not want this SSN flying around all your tables. You want it once in the customer table where it belongs.
Let's close the order table for now. Go back into our customer table. What we're going to do now is say, this is no longer the primary key, and we're going to add an autonumber to it. Leave the social security number alone. Do not touch it. Add CustomerID as an autonumber. Now turn off SSN as being the primary key. You can still leave it indexed. In fact, it's going to say Index: No now. If you are going to be doing searches and sorts on this, that's fine. You could even make it Indexed (No Duplicates) if you want to make sure you do not put the same customer social security number in here twice. That's okay. The system will still warn you about a key violation. You just do not want this to be the primary key. You want this to now be the primary key.
I like to slide this up top. I like to keep primary keys at the top of the table. That is just a design choice, a style thing for me. But we can still leave SSN as indexed, Yes (No Duplicates).
Remember, indexing is for speeding up your searches and sorts. That's what indexing is for and to prevent duplicate entries.
Save that. Now go and take a look at what you have. Access automatically applies all those customer IDs for you. Looks good.
Now, the trick is going to be to get that ID that is related to each of those customers into the Order table.
Close this. What we need to do is add that field to our order table. So we need to put CustomerID in here. Now, it's not an autonumber here. It's a number of type Long Integer. It's our foreign key. I am going to slide this up top because I like to keep all my key values at the top of the table. Leave SSN here for now. We still need it. We are going to have to use that to make our relationship. Save it and take a peek at what you have. You have a blank CustomerID field.
If I look at Orders and Customers side by side, you can see there is the social security number. You want to find the ones over here that match up with the ones over there. Let's bring them together in a query.
Close these. Go to Create, Query Design. I like to bring in the Order table first and then the Customer table. It is because the data you are changing is here, and the related information is over here. Now, it made a relationship between CustomerIDs because that's how it is supposed to be set up, but it is not currently set up that way, so we are going to delete that ad hoc relationship.
Ad hoc relationship, on the fly. We did not define any global system relationships, which, by the way, if you have global relationships defined, you are going to want to delete them first before doing this stuff. It will make your job a lot easier.
Now we're going to make our own ad hoc relationship between social security numbers. That way, the social security number in the order table is joining to the social security number in the customer table, like it is in the database right now. We are going to change it.
Now, let's just see what the data looks like. Bring down both stars. I do not usually recommend you bring down both stars since you can cause problems in real databases. I only bring down one star and then the related fields, but just for this example, it is okay so you can see what's going on. Take a peek.
Here is all the order stuff, and here is all the customer stuff that's related to it. Notice that the social security number is related to the social security number in both tables. All the records match up; looks good. The trick now is to get this CustomerID field from the customer table over into the CustomerID field in the order table.
If you only had 11 records, you could just type these in or copy and paste them. But let's pretend we have 11 million records. You do not want to do these by hand, so we are going to use an update query. We are going to update this field and set it to be equal to whatever is in this field.
Go back to Design View. You do not need the * fields now. Just get rid of those. I do that just to take a look at things first before making it into an update query, so I can make sure everything is lined up correctly.
Change the query to an Update Query. What are we going to update? What field are we updating? We are updating the CustomerID field in the order table. Bring that down; that's the field we're updating. What do we want to update it to? We want to update it to the customer table's CustomerID field. In the Update To row, type: [CustomerT].[CustomerID]. If you type it in directly there, you will get the IntelliSense, which is sometimes very helpful. [CustomerT].[CustomerID].
Before you do any of this, it goes without saying, I should have put this at the top of the video. In fact, I'm going to go back now and retroactively put this at the top of the video. I know that Stacy mentioned she is going to back up first - back up your data before messing with your table structure. Make sure you are running a nightly backup anyway. Make sure you back up your table before doing this stuff.
Now we are ready to run this query. Run it. Nothing appears to happen. I have warnings turned off; you may see a warning message. If not, we should be done with this query. I am going to save it just in case I need to do this again in the future for whatever reason. Save this as my UpdateCustomerQ.
Now, let's take a look at our tables side by side. Here is my customer table. Here is my order table. Look at that. There is the customer ID. Just do a spot check - four, two, two, etc. Those look good. The data ties up field for field now, so you can relate back to this field. That's what our goal was.
Now you can get rid of the SSN from the order table. You do not need it anymore. You can look it up by going back to the customer table. Come in here, delete SSN out of the order table. Confirm deletion. Save it. Close it.
Now if you create another query, and bring in Customers and Orders, it makes that relationship. Now you will see that you can get the customer information and the order information, and there they are.
There is your OrderID, the customer for each order, and their social security number if you need it. You can use a relationship or you can look it up if you have to.
We won't save that. You will still have to go through and fix any other forms you have, as you might have forms or combo boxes based on the SSN. You can change these now to use CustomerID. At this point, your tables are good. You have a proper relationship between the customer table and the order table based on CustomerID, which is what you wanted to have. Do this for every set of relationships in your table. Update your queries, forms, and reports. Depending on the complexity of your database, this can take a few days to do. Just double check, back up every step, and test it repeatedly throughout, and you should be okay.
I have had jobs where this took me an hour to do, and jobs where this took me weeks to do. Those were really complicated databases. And yes, the client gets billed for it.
That's about it. If you want to learn more about this kind of stuff, I strongly recommend taking my entire Access Expert series. I go through all kinds of material, lots on relationships and how to properly set up table relationships. I have a whole Relationship Seminar that covers everything you'd want to know about making relationships between tables.
If you want to learn more about action queries, like the update query, I have a series of five courses, Access Expert 13 through 17, that covers action queries - really, really cool stuff you can do with action queries.
If you want to learn more about autonumbers, I have a bunch of videos on different autonumber topics. Here is a big long list of all the things that I cover. Here is the good or bad video I mentioned earlier. This one is important too - autonumbers are not for you. You should not care what those autonumbers are. Let the system generate them. You will never have to see them if you do not want to.
Stacy, Marco, I hope that helps with your problem. Good luck with your databases. That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is a natural key in a database?
A. A key generated by the database system
B. A number or code assigned by Access automatically
C. A value based on real-world data such as a phone number or social security number
D. Any random set of characters chosen for uniqueness
Q2. What is a surrogate key?
A. A key made from combining parts of a person's name and address
B. A key that is based on real-world identifiers
C. A key generated by the database, typically with an autonumber or GUID
D. A handwritten number from a paper record
Q3. In Microsoft Access, what is the most common type of surrogate key used by beginners?
A. Phone number
B. Vendor-provided product code
C. Autonumber
D. Preprinted invoice number
Q4. According to the video, what is the recommended best practice for handling natural keys such as social security numbers?
A. Use them as the primary key and remove them from all other tables
B. Use them as the primary key but do not keep them in the table
C. Use a surrogate key (autonumber) as the primary key and keep the natural key in the table for reference
D. Only use them in forms, not in tables
Q5. What is one major risk of using a natural key like a social security number as your primary key?
A. It is always guaranteed to be unique and never change
B. It may change, causing database integrity issues
C. It automatically creates relationships between tables
D. It uses less storage than an autonumber
Q6. When modifying an existing database to use surrogate keys, what is the first action you should take before making changes?
A. Delete all existing data
B. Back up your database and tables
C. Change all queries to use the new key immediately
D. Remove all indexes
Q7. After adding an autonumber CustomerID to the customer table, what step should you take in the related order table?
A. Remove the old foreign key and do nothing else
B. Add the CustomerID field as an autonumber to the order table
C. Add a CustomerID field as a number (Long Integer) to the order table to serve as a foreign key
D. Ignore the CustomerID field and continue using SSN
Q8. How should you populate the new CustomerID foreign key in the order table?
A. Manually enter the matching CustomerID for each order
B. Use an update query to set the CustomerID in orders equal to the corresponding CustomerID in customers based on the SSN relationship
C. Copy and paste values from one table to the other
D. Create a calculated field in the order table
Q9. After successfully updating the foreign keys, what should be done with the SSN field in the order table?
A. Keep it as a secondary primary key
B. Remove it from the order table since it now exists only in the customer table
C. Make it into an autonumber field
D. Convert it to a foreign key to another table
Q10. Why is it recommended not to use real-world values as primary keys in relationships?
A. They are always unique but take up more storage
B. They are prone to duplication, change, and can cause complex data integrity problems
C. They make designing forms easier
D. They are generated automatically by Access
Q11. What must you do after restructuring your tables to use surrogate keys for all relationships?
A. Delete all your forms and rebuild them from scratch
B. Update all queries, forms, and reports that reference the old natural key to use the new surrogate key instead
C. Change all autonumber fields back to text type
D. Remove relationships between tables
Q12. What is the purpose of indexing a field in Access?
A. To prevent updates to the field
B. To enforce referential integrity between tables
C. To speed up searches and sorts and optionally prevent duplicates
D. To generate new numbers automatically
Q13. Where should foreign keys, such as CustomerID in an order table, typically be placed in the table design?
A. At the top of the table, for organization and clarity
B. At the bottom, after all other fields
C. In a separate table
D. Spread randomly throughout the design
Q14. If you have many related tables with natural keys as primary keys, what is the recommended order to change them to surrogate keys?
A. Start with the top-most parent table and move downward
B. Change all tables at the same time
C. Start with the bottom-most child tables and move upward
D. Leave all relationships as they are
Q15. What must you do in your Access forms and combo boxes after migrating from a natural key to a surrogate key?
A. No changes are needed
B. Change them to use the new surrogate key (e.g., CustomerID) instead of the natural key (e.g., SSN)
C. Replace them with textboxes only
D. Remove all lookup functionality
Answers: 1-C; 2-C; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B; 11-B; 12-C; 13-A; 14-C; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on how to replace a natural key with a surrogate key, specifically using an autonumber in Microsoft Access.
To begin, it's important to recognize the distinction between a natural key and a surrogate key. A natural key is a value that exists in the real world, such as a phone number, social security number, or a product code supplied by a vendor. These are numbers you might encounter outside the database. In contrast, a surrogate key is something generated entirely by the database itself, typically through an autonumber field that can be either incremental or random, or even a globally unique identifier. In Access, especially for those newer to database design, we typically use autonumbers as surrogate keys.
If you were to ask a room full of database professionals which key type is best, you'd probably get a split decision. Personally, I am firmly in the surrogate key camp. I prefer to use autonumbers for all of my primary keys, regardless of whether a natural key exists, because I've found from decades of experience that it avoids many problems. I always keep the natural key in the table for reference, but it doesn't serve as the primary key or for relationships between tables.
For more background on why I prefer autonumbers and a more detailed discussion about this topic, I have a separate video available on my website. If you are interested in the advantages and disadvantages of each method, you should watch that as well.
This tutorial assumes you agree with the surrogate key approach and want to move your database in that direction. I will walk you through the process of taking a table that uses a natural key like a social security number and show you how to replace it with an autonumber as the primary key, while still keeping the original natural key in place for reference. The process involves updating all related foreign keys, so that relationships between your tables are consistent and future-proof.
The inspiration for today's tutorial came from a question raised in my Facebook group. One of my students was preparing to switch a production database's primary key from a value generated outside the system (but manually entered) to an autonumber, as I recommend in my Access Expert classes. The primary concern was how to update all the existing relationships across related tables. She needed to keep the original key for reference, but move the system toward using surrogate keys.
Another student chimed in with a similar situation. He had a primary key made from property IDs, such as a lot ID, which he thought would remain permanent. His format was based on an address, like turning "2575 Smith Lane" into "2575SL," and as the system grew, he started encountering issues with duplicates and scalability. I have encountered plenty of similar stories, where even keys you think can never change eventually do, or you find unexpected duplicates. For example, even social security numbers can change under rare circumstances, which can create chaos if you're using them as relational keys.
This demonstrates why I always advise against using natural keys for relationships, especially as your database evolves.
Let's move to the practical steps. This tutorial is for those who are comfortable with the basics of Access, but you won't need any programming experience today, just a solid grasp of table relationships and update queries. If you're not yet familiar with Access relationships or update queries, I recommend watching those introductory videos first on my website. Both are available for free.
Before proceeding, always make a backup of your database. Any time you are about to make structural changes to your tables, safeguards are essential.
I will walk through a simple example using a blank Access database. The typical scenario starts with a table – in this case, a customer table – where the primary key is a natural key, such as a social security number. This field will likely be short text, not a number. You might then have other fields for first name, last name, and so on.
When you create this table and assign the SSN as the primary key, Access will set it with no duplicates, ensuring uniqueness. You might enter some sample customer data at this stage.
You may also have an order table where, instead of relating to customers with a CustomerID, you relate orders to customers by their SSN. If you were at least forward-thinking enough to use an autonumber for OrderID, that's a good start. However, if you've been using natural keys throughout, you'll need to apply this process to each relationship.
Ideally, you should begin this process with tables at the 'bottom' of your relationship chain (like Products or OrderDetails), then work up through parent tables. I will demonstrate the process with customers and orders as an example, but you will repeat these steps for each set of related tables in your own system.
So, after building the customer and order tables linked by the SSN, it's time for the key change. The safest way to proceed now is to retain the SSN in the customer table, but add a new CustomerID field set as an autonumber. This becomes your primary key. You can keep SSN indexed (No Duplicates) to maintain search and sort performance and prevent accidental duplicates, but it is no longer the primary key.
Once you save these changes, Access will automatically assign unique autonumbers to each customer.
Next, add a CustomerID field to your order table. This field is a number data type (Long Integer), as it is now a foreign key. Leave the original SSN in the order table for the moment, as you will need it to match data and update relationships correctly.
To update the foreign keys, create a query that joins the order and customer tables by SSN. The goal is to update the new CustomerID field in the order table, matching the corresponding value from the customer table. For this, use an update query which sets the order's CustomerID to the customer table's CustomerID wherever the SSNs match.
Before running this update, double check that you have a safe, recent backup in case something goes wrong.
After running the update query, inspect your order and customer tables to ensure the CustomerID values in the order table correctly match those in the customer table. Once verified, remove the SSN field from the order table because it's no longer needed there. Going forward, relationships and lookups should reference CustomerID, not SSN.
From here, you need to update any forms, queries, or reports that previously relied on SSN for relationships or lookups to use CustomerID instead. Depending on how complex your database is, this could be a quick fix or it may take a few days. Just work steadily, back up frequently, and keep testing as you go.
Some projects only take about an hour, while others for larger or more intricate databases can stretch into weeks. Nevertheless, the long-term reliability and manageability of your database will be vastly improved.
If you want to learn more about table relationships, consider checking out my Access Expert series and my comprehensive Relationship Seminar, where all aspects of building strong relational databases are covered. For more on action queries, I recommend my Access Expert 13 through 17 courses, which go deep into these powerful features.
If you would like to understand more about autonumbers, including why you should ignore the actual values and just let the system manage them, there are several detailed videos available on my website.
I hope this lesson helps clarify how and why to update your natural keys to surrogate keys in Access, and gives you a solid method for making those changes safely and effectively.
A complete video tutorial with step-by-step instructions covering everything I discussed here is available on my website at the link below.
Live long and prosper, my friends.Topic List Replacing a natural key with a surrogate key
Definition and examples of natural keys
Definition and examples of surrogate keys
Assigning an autonumber as a surrogate key
Retaining natural key fields for reference
Modifying the primary key in an Access table
Adding an autonumber field to an existing table
Removing a natural key as primary key
Maintaining indexing on natural key fields
Adding a foreign key field to a related table
Populating foreign key fields using an update query
Creating an ad hoc relationship in a query
Joining tables using the natural key in a query
Updating foreign keys to use the new surrogate key
Deleting old natural key fields from related tables
Verifying relationships after migration
Adjusting forms and combo boxes to use the new foreign key
Testing and validating the updated table relationships |
