Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.