In Oracle SQL 19c, how can I INSERT a new record into a supertype and its related subtypes in one statement?

Question

I'm designing a property management database that will hold - OWNER, CUSTOMER, and STAFF information.

As these use many of the same attributes, I intend to create tables for PERSON, ADDRESS and CONTACT.

The primary key for PERSON (personID) will be auto-incremented and used as the foreign key for ADDRESS, CONTACT, OWNER, CUSTOMER, and STAFF.

When it comes to adding a new record, is there a way to INSERT into PERSON, ADDRESS, CONTACT, and OWNER/CUSTOMER/STAFF at the same time using a simple statement? Or is there another way to retrieve the generated personID and use it to insert into the other tables?

I'm testing on Oracle SQL Live 19c.

Answer 1

The generated personID can be returned into a bind variable with the returning_clause. Example:

SQL> create table parent(id number generated always as identity not null primary key,
       c1 number, c2 varchar2(20 char));

Table created.

SQL> create table child(id number generated always as identity not null primary key,
       parent_id number not null, constraint fk_parent foreign key (parent_id) 
                                  references parent(id));

Table created.

SQL> variable generated_id number
SQL> insert into parent(c1, c2) values (10, 'HELLO') returning id into :generated_id;

1 row created.

SQL> print :generated_id

GENERATED_ID
------------
           1
SQL> insert into child (parent_id) values (:generated_id);

1 row created.

SQL> commit;

Commit complete.

Result:

SQL> select * from parent;

        ID         C1 C2
---------- ---------- --------------------
         1         10 HELLO

SQL> select * from child;

        ID  PARENT_ID
---------- ----------
         1          1

SQL>