Two Interesting Things On SQL Server Relationships
At work, yesterday we finished up our migration from SQL Server 7 to 2000. First, we had to move our production database to an interim server while they rebuilt the regular server. They finally got that done, so last night we went and moved everything back.
In our dev and test environments, we were having some issues with migrating our relationships. They were using the simple “Import/Export Data” wizards in Enterprise Manager, and the way it does it is it copies the table structure, copies the data, and then applies the relationships (could be difficult to populate the tables if a primary key doesn't exist yet).
The problem? When we were migrating, it was converting fields with nulls to 0s. Well, in a relationship, a foreign key of null is valid (not participating), however 0 doesn't exist (especially with identity fields). So when it went to apply the relationships, almost half of them were failing. After some investigating, we finally found the cause. The brilliant people that designed the original schema had the default value for a number of fields to be 0. All the stored procedures that insert the data use null, and 0 could never realistically get in there since the relationship would be enforced. But when migrating the data, it uses a regular insert, so it would convert the null to the default value. So! Be sure your default values for fields that are foreign keys isn't 0!
The second thing I learned is that there is a way to get data into a field that does not exist in the primary key table. If you go in and modify a relationship with Enterprise Manager, uncheck the “Enforce relationship for INSERTs and UPDATEs option”, you can go in and modify the field to any value you want, regardless if it exists in the primary key table. Then, you go back in, recheck that option, and it doesn't revalidate the data in the table. It will enforce the constraint on future inserts/updates, but does not validate the data that is already there. Interesting...