0

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?

ThoseDarnKids
  • 103
  • 2

1 Answers1

1

Your way is how I would do it - nullable column, update values, set to non-nullable.

The middle step (in SQL Server) can be achieved by doing something like:

UPDATE
   e
SET
   e.BranchId = s.BranchId
FROM
   ExistingTable e
   INNER JOIN SourceTable s
      ON e.Column = s.Column

Since I don't know what your ERD looks like, I am assuming your JOIN columns are not necessarily the Branch Name you described in the question.

It may not be elegant, but it avoids putting in a step to get an ID value from the Branch table for the default value.

So my answer is to trust your instinct.

  • Thanks. That's what I ended up doing (nullable, update, non-nullable). That created a long change script, but that's no big deal. – ThoseDarnKids Oct 21 '09 at 14:10