Actually, it is possible to have the same schema in both places, and even the same data, and experience different behavior. How? Because constraints (CHECK and FOREIGN KEY) can be disabled. Yup.
To see if the Foreign Key in question is enabled or disabled, just run the the following, making sure to put the name of the FK in question in the WHERE clause:
SELECT *
FROM sys.foreign_keys
WHERE [name] = N'{name_of_FK_in_question}';
Look at the column named is_disabled. I suspect that it is set to 1 on Server A (where everything "works") and is 0 on Server B (where things "don't work"). I put "works" and "don't work" in quotes, because if this is truly the case, then reality is the opposite of what you are experiencing. Meaning, the system with the FK enabled and getting the error is actually working as that is what FKs should do. The system not getting an error is possibly allowing orphaned (i.e. bad) data in.
To enable the FK, run the following:
ALTER TABLE {table_name}
WITH CHECK -- verifies the data currently in the table
CHECK CONSTRAINT [{name_of_FK_in_question}];
Of course, if the bad data is there, you either need to:
Delete the bad data first, or
Specify WITH NOCHECK on the ALTER so that it will accept the bad data:
ALTER TABLE {table_name}
WITH NOCHECK -- accept the bad data aleady there
CHECK CONSTRAINT [{name_of_FK_in_question}];
However, this does not solve the issue 100%. If you run that SELECT query (noted above) again, you should see that the is_disabled field is now set to 0. BUT, if you look at the is_not_trusted field, it will be set to 1. If a constraint is enabled yet not trusted, it will enforce its rule as expected, but the Query Optimizer (Q.O.) will ignore it, which is generally not a great thing as the constraints are actually used not just to enforce data integrity rules, but also as clues for the Q.O. to logically reduce certain steps in some queries (i.e. they sometimes help increase performance). In order to get the constraint to be "trusted", you will have to delete the bad data and verify all constraints on the table via:
ALTER TABLE {table_name}
WITH CHECK -- verifies the data currently in the table
CHECK CONSTRAINT [{name_of_FK_in_question}];
However, if for some reason you need the "bad" data, then you will just have a Foreign Key that enforces data integrity but has no potential for improving performance (which is still much better than having no FK defined :).
For more info, please see the MSDN page for ALTER TABLE.
For the sake of completeness I will also mention that it is possible that on Server A (where there is no error), that the FK is defined with the option ON DELETE CASCADE which auto-deletes and related data prior to removing the row(s) from the table that is being deleted from, while Server B (where there is an error) does not have the ON DELETE action specfied (or is specified as NO ACTION). This, however, is something that should show up when doing a schema compare (unless specifying to ignore the ON DELETE and ON UPDATE actions), whereas whether the constraint is enabled or disabled is more likely to be ignored in a schema compare.
