How do you add a NOT NULL FOREIGN KEY column to an existing (populated) table in MS SQL?

Question

I need to add a NOT NULL column to an existing (populated) table that will be a foreign key to another table. This brings about two problems:

1. When you add the column, its value cannot be null - using a default is not an option (unless it is removed later on) because the database logic is used in the server side validation when a user enters a new record i.e. when the application tries to add a record with this field having a null value, the database throws an error that the application catches and returns to the user, prompting them to correct it.

2. The column has a foreign key constraint, meaning its value MUST exist in the foreign table as well.

What is the best way to go about this?

Answer 1

Create the column, but allow NULL. Populate the column with the correct data from the foreign key table. Alter the column add not null. Add the foreign key constraint.