0

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.

3
  • Each table requires a separate insert statement, though you can place a single commit at the end of all of them, so they will process within the scope of a single transaction.
    – pmdba
    Jun 14 at 15:58
  • Why do you want to insert in one statement? Do you want atomicity? In this case I would recommend using a transaction instead. If not: What's the problem with multiple inserts?
    – Cookie04
    Jun 14 at 15:59
  • Okay, separate inserts then. Apprciated. However, the primary key for PERSONS will be auto-incremented. So I guess I will need to retrieve this before inserting into other relevant tables? Is there a simple way to do this?
    – Gospel77
    Jun 14 at 16:05

1 Answer 1

0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.