Q: How do I set the primary key in a Studio 3T Migration from MongoDB to SQL? I want to set my own primary key field name and start it at a specific value.
Welcome to the latest Studio 3T Ask Manatees Anything – #Studio3T_AMA. Want to know how to use Studio 3T to make your MongoDB life better? Just tweet and use #Studio3T_AMA or email [email protected].
A: You can set your SQL primary key through the Migration Mappings interface. When you do an SQL Migration with Studio 3T you are able to configure a whole range of mapping operations that can split your data’s arrays and embedded documents across multiple tables. We added support for setting the migration’s primary key name and initial value in Studio 3T 2022.8. Don’t forget to upgrade!
Setting a migration’s primary key
At the top of the mapping tree will typically be the core table that you are migrating to. It’s here where you can set that table’s new primary key. Select edit on the table entry and you’ll see this dialog:
Because it represents the root document and how it maps to a table, there will not be a lot to set here. What you will see at the bottom though, is the Relationship section. It’s here where you can set the new Primary key name and its initial value. For this migration, we call the primary key customer_id here and start its value at 1000. Setting and applying that primary key reflects in the preview:
Beyond the main SQL table
Setting the primary key is not just for the root table. Where an object or array maps to another table, you can edit the primary key for that new table. For example, the address field in our collection maps to an address table. If we look up that table in the mappings we can see what it is creating to achieve this:
There’s more to be set here, as it handles the mapping of the data in the address field. Look down to the relationship section and you’ll see the options to set the primary key and its initial values. Again, we’ve set this to the more explanatory address_id and started it from 5000. In the preview, we’ll see that being applied:
Wrapping up
As a result of the addition of primary key and value settings to Studio 3T 2022.8, it is now much easier to make much more readable and numerically separated ID fields when exporting to SQL.
