title: Oracle - Insert Stored Procedure Foreign Key
body: <p><strong>Instructions:</strong> 
  &nbsp;</p>
  
  <p>Create two tables, named <strong><em>employees</em></strong> and <strong><em>departments</em></strong>. Preface the table names with your initials. Link the two tables (foreign key) by a column called <strong><em>dept</em></strong>. Make up a few column names for each table.</p>
  
  <p>Employees Table:</p>
  
  <pre><code>create table bsemployees(
  dept        number primary key,
  empName     varchar2(20),
  salary      number
  );
  </code></pre>
  
  <p>Departments Table:</p>
  
  <pre><code>create table bsdepartments(
  dept        number references bsemployees(dept),
  deptName    varchar2(20)
  );
  </code></pre>
  
  <p>Write the following stored procedures:</p>
  
  <p>•   Insert a row into the <strong><em>employees</em></strong> table. If the department does not exist. Insert it into the <strong><em>departments</em></strong> table.</p>
  
  <pre><code>create or replace procedure sp_employees(
      a_dept IN number, 
      a_empName IN varchar2, 
      a_salary IN number
  )
  as
      vCount  number;
  BEGIN   
      sp_check_dept(a_dept,vCount);
  
      insert into bsemployees values(a_dept, a_empName, a_salary);
  
      if vCount = 0 then
          dbms_output.put_line('**DEPT DOES NOT EXIST**');
          insert into bsdepartments (dept, deptName) values(a_dept, NULL);
      end if;
  END;
  /
  
  create or replace procedure sp_check_dept(
      a_dept IN number,
      vCount OUT number
  )
  as
  BEGIN
      select count(*)
      into vCount
      from bsdepartments
      where dept = a_dept;
  end;
  /
  </code></pre>
  
  <p>•  Insert a row into the departments table.</p>
  
  <pre><code>create or replace procedure sp_departments(
      a_dept IN number, 
      a_deptName IN varchar2
  )
  as
  BEGIN
      insert into bsdepartments values(a_dept, a_deptName);
  END;
  /
  </code></pre>
  
  <p>I've got it pretty much all down for this assignment except for the fact that when I try to insert a row into the <strong><em>departments</em></strong> table I am getting a integrity constraint - parent key not found error.</p>
  
  <p>If I do <code>execute sp_employees(5, 'John Doe', 90000);</code> It will display <code>***DEPT DOES NOT EXIST***</code> and will go ahead and insert the data into <strong><em>bsemployees</em></strong> and insert the dept# into <strong><em>bsdepartments</em></strong> and the deptName will be left blank based on my if-then statement. Doing a select(*) shows me this.</p>
  
  <p>However if I go ahead and do <code>execute sp_departments(1, 'human resources');</code> to place a row into <strong><em>departments</em></strong> I get the parent key error. I understand that I am trying to insert something that has no parent key but I do not know how to fix it.</p>