So in summary:

If you have an integrity rule that you want to enforce on a database and you can write a query that returns one or more rows if there is something that invalidates this rule. Just write the query with schema binding cross joining to a two row table and add a unique index to it.

Yes, that's a pretty neat way enforcing almost any integrity constraint you want on your database. You could use it to catch the problem where you have an EffectiveFromDate and EffectiveUntilDate on your table and you don't want any rows with overlapping dates.