Using WITH NOCHECK
In SQL Server 2000, there is a facility for you to add a constraint using the WITH NOCHECK option to avoid checking of existing data that can possibly violate the constraint. This is useful in situations wherein you feel that a constraint was not required earlier and then later on, you decided that you need the constraint. WITH NOCHECK ensures that existing data is not checked, however, when any updates are made and the resultant resultset violates the check, the constraint will fire. For new data that is being inserted, the constraint is anyways enforced.
Recently, I came across this posting at Microsoft that talks about the woes of using this option: WITH NOCHECK Issues. As per this article it says that using WITH NOCHECK can return incorrect results, since SQL Server assumes that the check is enforced (the article talks about an example using FKs). This could be bad news! However, the bug is supposed to be fixed in SP4 of SQL Server 2000. How about the same in SQL Server 2005, well its fixed there :-)