I'm a web developer, with admin rights on our SQLServer, and I need to add a non-nullable integer column (foreign key) to an existing table. I'm looking for the best way to create this column and populate it with data.
Example of the new column:
branchId (int) non-nullable with a foreign key to the parent table
Example of the existing parent table, Branch:
id (int) auto-incrementing primary key branchName (varchar)
I know I could simply set the Default Value to point to the id of the proper Branch record, but I'm hesitant to do that because the Branch id might vary between environments.
My idea is to first create the new branchId column as nullable, then update all records with the correct id by getting the proper branchId from a query like SELECT id FROM Branch WHERE branchName="Branch 4". Then, I'd go back and change the column to non-nullable, which should be fine as they all have data now.
My database change scripts are all kept in source control, so that's the main reason for needing an elegant solution that could be run at any time in the future, regardless of what the Branch id.
What's the best way to do this?