Oracle – Insert Stored Procedure Foreign Key Code Answer

Instructions:  

Create two tables, named employees and departments. Preface the table names with your initials. Link the two tables (foreign key) by a column called dept. Make up a few column names for each table.

Employees Table:

create table bsemployees(
dept        number primary key,
empName     varchar2(20),
salary      number
);

Departments Table:

create table bsdepartments(
dept        number references bsemployees(dept),
deptName    varchar2(20)
);

Write the following stored procedures:

• Insert a row into the employees table. If the department does not exist. Insert it into the departments table.

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;
/

• Insert a row into the departments table.

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;
/

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 departments table I am getting a integrity constraint – parent key not found error.

If I do execute sp_employees(5, 'John Doe', 90000); It will display ***DEPT DOES NOT EXIST*** and will go ahead and insert the data into bsemployees and insert the dept# into bsdepartments and the deptName will be left blank based on my if-then statement. Doing a select(*) shows me this.

However if I go ahead and do execute sp_departments(1, 'human resources'); to place a row into departments 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.

Answer

Your table design isn’t quite correct – the dept primary key needs to be added as a foreign key to employee (not as the primary key), and employee should have its own primary key:

create table bsdepartments(
  dept        number primary key,
  deptName    varchar2(20)
);


create table bsemployees(
  empName     varchar2(20) primary key,
  dept        number references bsdepartments(dept),
  salary      number
);

You can then do the ‘add if not exists’ logic in the check_dept proc:

create or replace procedure sp_check_dept(
    a_dept IN number
)
as
   vCount number
BEGIN
    select count(*)
        into vCount
        from bsdepartments
        where dept = a_dept;

    if (vCount = 0) then
        dbms_output.put_line('**DEPT DOES NOT EXIST**');
        insert into bsdepartments (dept, deptName) values(a_dept, NULL);
    end if;
end;

Which then simplifies the employee insertion proc as it should be guaranteed of a department:

create or replace procedure sp_insertEmployee(
    a_dept IN number, 
    a_empName IN varchar2, 
    a_salary IN number
)
as
BEGIN   
    sp_check_dept(a_dept);

    insert into bsemployees values(a_dept, a_empName, a_salary);
END

Notes

• Recommend that you name the procs in alignment with their purpose, e.g. insertEmployee vs just employees
• As you’ve noted, the problem with the ‘add if not exists’ approach is that you do not have sufficient data to completely populate the department table, hence the null column (but this is what your lecturer asked for)