title: SQL Assertion: The manager complex
body: <p>I have the following schema:</p>
  
  <p>Emp(<strong>eid</strong> int, ename varchar(50), salary float, email varchar(80))</p>
  
  <p>Works(<strong>eid</strong> int, <strong>did</strong> int, pct_time float)</p>
  
  <p>Dept(<strong>did</strong> int, budget float, managerid int)</p>
  
  <p>eid is for employee id.
  did is for department id.
  I've bolded primary keys.
  managerid is a foreign key, as well as eid and did in Works.</p>
  
  <p>Now, I'd like to add a "manager complex" assertion that ensure a manager will always have a higher salary than any employee that he/she manages.</p>
  
  <p>Here's what I've been thinking:</p>
  
  <pre><code>CREATE ASSERTION managerComplex
  CHECK
  ( NOT EXISTS (SELECT  M.salary  
                FROM Dept D, Emp M 
                WHERE D.managerid = M.eid) &lt;= 
                                             (SELECT E.salary
                                              FROM Works W, Emp E
                                              WHERE W.eid = E.eid) AND
                                                                       D.did = W.did);
  </code></pre>
  
  <p>Is this even close to right?
  Also, maybe I should have just made a couple CHECKs instead of an insertion?
  I feel like multiple CHECKs is sloppier but would probably be easier to get right.</p>
  
  <p>EDIT: The main reason I am asking is because I don't know if I'm correctly understanding NOT EXISTS</p>