Issues of Database Evolution

If you change the structure of a deployed database, you must take special care that your alteration is compatible with the existing data and database structure.  You might need to take special steps when you make the following modifications:

• Adding a Constraint   If you add a constraint, the database might already contain data that does not satisfy it.  When you try to save the new constraint, the Save Incomplete dialog box informs you that the database server could not create the constraint.  To force the database to accept the new constraint, you can clear the Check existing data on creation check box.  For more information, see Checking Existing Data When Creating a Check Constraint.
  
  
• Adding a Relationship   If you add a relationship, the database might already contain rows of the foreign-key table that do not have corresponding rows in the primary-key table.  That is, the existing data might not satisfy referential integrity.  When you try to save the new relationship, the Save Incomplete dialog box informs you that the database server could not save the revised foreign-key table.  To force the database to accept the modification, you can clear the Check existing data on creation check box.  For details, see Checking Existing Data when Creating a Relationship.
  
  
• Modifying a Table Contributing to an Indexed View   If you modify a table that contributes to an SQL Server indexed view, the indexes on the view will be lost.  For more information about recreating indexes, see Rebuilding an Index.

No matter how you alter the database design, you should retain a history of the alterations.  One approach is to retain SQL scripts for all modifications that you ever make to your production database.  For more information about scripts, see Working with Scripts.