Not all that much in Google groups about Msg 1046, but I still have a hard time understanding the restriction that you can't use a subquery in a check constraint. If this for performance reasons? Because if you can use a scalar UDF, I promise you that I can build one that performs very poorly.
This came up because someone wanted to ensure that a column value existed in metadata (e.g. CHECK table_name IN (SELECT name FROM sys.tables)). You can't have a subquery (or a table-valued function) in a check constraint, and of course you can't place a foreign key against a system view. So their only alternative now is a trigger. It's a workaround, but it's not perfect.