If you are building an ASPNET MVC application (or Rails, or CakePHP) that leverages migrations, the productive speed is pretty amazing…until you do something that is not on the critical path. One area that I THINK falls in this category is adding a NEW table that an EXISTING table will use as a foreign key (e.g., the EXISTING table needs to add a reference to the NEW table).
Here is how I did it.
- Create the NEW model / class / table
- Update the model for the EXISTING table having a reference to the NEW table
- NOTE: do NOT add a reference from the EXISTING table to the NEW table, because this will break referential integrity tests
- Create a migration for that NEW table and updated EXISTING table
- Update the migration to specify that the default value for foriegn key (e.g., typically it is 0 for ASPNET MVC, and my first record will be a value of 1, so I specified a default value of 1)
- Run the migration so that your NEW table will appear in your database
- Create the CRUD scaffolding for the NEW table (so that you can create a proper entity
- you can just insert a record into the database, but if you have relationships to user accounts or other logic that may be tricky
- Using the web interface, create your first entry for your NEW table
- You will need to do this for each environment…dev/test/production…where you want it to apply; if you just push the final solution to production, you will get an error until you manually create the first record and populate the reference to the first record
- NOTE: my favorite approach for updating different environments is to point my local dev instance at the production database, run the migrations, and even run the web app – not good for significant implementations, but super fast for smaller projects
- If needed, manually update your database for the EXISTING table so that all of them point to that newly created record
- Now add the reference from the NEW table to the EXISTING table, create another migration, and update the database again
It seems a bit hacky, particularly because you cannot just deploy. But, given that it took me longer to write it up than to do it, I went with the approach.
If you have a better approach, I would love to hear it.