SQL INSERT INTO child entity

Question

I have two entities, a parent EMPLOYEE table and a child DOCTOR table, as follows:

CREATE TABLE EMPLOYEE(
Emp_Id VARCHAR2(3 CHAR) CONSTRAINT employee_emp_id PRIMARY KEY,
Emp_Fname VARCHAR2(20 CHAR) NOT NULL,
Emp_Lname VARCHAR2(20 CHAR) NOT NULL,
Emp_DOB DATE NOT NULL,
Emp_Address VARCHAR2(40 CHAR),
Emp_Phone VARCHAR2(10 CHAR) NOT NULL,
Emp_Email VARCHAR2(40 CHAR) NOT NULL,
Emp_Type VARCHAR2(1 CHAR) check (Emp_Type in ('D', 'N', 'R')));

CREATE TABLE DOCTOR(
Doc_Id VARCHAR2(3 CHAR) CONSTRAINT doctor_doc_id PRIMARY KEY,
Emp_Id VARCHAR2(3 CHAR) REFERENCES EMPLOYEE(emp_id),
Doc_Spec VARCHAR2(10 CHAR) NOT NULL, 
Doc_Med_Lic VARCHAR2(10 CHAR) NOT NULL, 
Doc_Fee NUMBER(10,2) NOT NULL, 
Doc_Avail VARCHAR2(3 CHAR) check(Doc_Avail in ('Yes'/'No')));

How would I go about inserting rows into the DOCTOR table such that it is also entered into the EMPLOYEE table, or at least references an existing entry in the EMPLOYEE table? Inserting rows into the EMPLOYEE table is simple enough:

INSERT INTO EMPLOYEE (Emp_Id, Emp_Fname, Emp_Lname, Emp_DOB, Emp_Address, Emp_Phone, Emp_Email, Emp_Type)
VALUES (emp_id_no_seq.NEXTVAL,'John', 'Le Grange', '15-MAY-65','15 Maple Ave.', '0112562314', 'jlg@rockhealth.com', 'D');

I'm just a bit hazy on the relational INSERT INTO for the child entity. Any help clearing this up or referring me to a thread that addresses this question would be greatly appreciated.

Why is MySQL/SQL server tagged? `VARCHAR2` and `(3 CHAR)` is invalid MySQL/SQL server syntax.. This looks to be Oracle database syntax.. — Raymond Nijland, May 13 '18 at 17:59
What's keeping you from inserting into `DOCTOR` the analog way you do for `EMPLOYEE` after you've inserted into `EMPLOYEE`. Please edit the question and clarify what you're asking for. — sticky bit, May 13 '18 at 21:03

score 0 · Answer 1 · answered May 13 '18 at 23:27

Since DOCTOR references EMPLOYEE, you have to have a row in your employee table first and insert a row into doctor using the employee id. If you're doing this in PL/SQL, that's easy enough with the RETURNING INTO clause, which returns the value that was actually inserted into the table:

DECLARE
    l_id    employee.id%TYPE;
BEGIN
    INSERT INTO employee ( id, ... )
    VALUES ( emp_id_no_seq.NEXTVAL, ... )
    RETURNING id INTO l_id;

    INSERT INTO doctor ( id, emp_id, ... )
    VALUES ( doc_id_so_seq.NEXTVAL, l_emp_id, ... );
END;

SQL INSERT INTO child entity

1 Answers1