The main limitation of CHECK constraint is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub queries.
