title: How to Check a table1 field depending on a table2 field? Check constraints don't accept subqueries
body: <p>Let me wish you a good day, even if I am having a hard time with the check constraint.
  Want I want to do is check if the value of field1 is less than the value in the field2 of another table. I have tried this</p>
  
  <pre><code> ALTER TABLE [dbo].[Cote]
   ADD CONSTRAINT CoteMarksLessThanTestMax 
   CHECK(Marks&lt;=(select sum(t.Max) from Test as t WHERE TestID=IdTest) )
  </code></pre>
  
  <p>This returns an error saying : subqueries are not allowed in this context. How can I do it otherwise? I mean without using the <code>check</code> constraint?</p>
  
  <p><strong>COTE</strong> (CoteID,IdTest,Marks)</p>
  
  <p><strong>TEST</strong> (TestID,Max)</p>