title: Is there a way to check whether a new row in a table agrees with entries in other tables? [SQL]
body: <p>I am using MySQL Workbench CE for a college assignment. I have 4 tables: <code>Applicants, Universities, Job, Interview</code>. <code>Interview</code> has foreign keys in the other 3 tables: <code>appID</code> (applicant ID), <code>uniID</code> (university ID) and <code>jobID</code> (job ID). Additionally, the <code>job</code> table has a <code>uni</code> column, which is a foreign key to <code>universities.id</code>. Essentially, each job has a job number and a university associated with it. Each interview has an applicant, a job and a university, in addition to some other information. </p>
  
  <p>I am trying to figure out how to check whether the job offering (job number + its associated university) entered into the <code>interview</code> table actually exists in the <code>job</code> table.</p>
  
  <p>For example, if the only row in <code>job</code> is:</p>
  
  <p><code>ID  |  JobID  |  UniID | ..
   1       1         1      .</code></p>
  
  <p>Then the <code>interview</code> table <strong>shouldn't</strong> accept something like:</p>
  
  <p><code>ID  | AppID | JobID | UniID | ...
   x      x      1       2      .</code></p>
  
  <p>as there is no job with <code>JobID = 1</code> and <code>UniID = 2</code>.</p>
  
  <p><code>SELECT * FROM job as J LEFT JOIN interview as I ON J.uni = I.uniid WHERE J.id = I.jobid</code> is a check I came up with for this issue, however, I'm not sure whether I should attempt to use it in a trigger or as part of an assertion. </p>