How to create and insert into a table in SQL entity that has two mutually exclusive 1:1 relations?

Question

How do I handle the code for creating these tables and inserting into them?

Tried this but failed :

create table vlasnik
(
    vlasnik_id integer not null constraint vlasnik_pk primary key,
    datum_zakupa date not null
);

create table pravna_osoba
(
    naziv varchar2(20) not null,
    ime_ravnatelja varchar2(20) not null,
    prezime_ravnatelja varchar2(20) not null,
    datum_osnutka date not null,
    OIB_tvrtke varchar2(13) not null,
    pravna_osoba_id number not null 
        constraint pravna_osoba_pk references vlasnik (vlasnik_id) primary key
);

create table fizicka_osoba
(
    ime varchar2(20) not null,
    prezime varchar2(20) not null,
    OIB varchar2(13) not null,
    datum_rodenja date not null,
    primarna_djelatnost varchar2(30) not null,
    broj_sticenika integer not null,
    fizicka_osoba_id number not null  
        constraint fizicka_osoba_pk references vlasnik (vlasnik_id) primary key
);

alter table vlasnik 
    add (constraint vlasnik_pravna_osoba_fk 
         foreign key (PRAVNA_OSOBA_ID) references PRAVNA_OSOBA (PRAVNA_OSOBA_ID),
         constraint vlasnik_fizicka_osoba_fk 
         foreign key (FIZICKA_OSOBA_ID) references FIZICKA_OSOBA (FIZICKA_OSOBA_ID));

I don't really know how to handle foreign keys while inserting into the tables. The tables 'pravna_osoba' and 'fizicka_osoba' need to pull the vlasnik_id as their primary key, but then that new primary key needs to be a foreign key in table 'vlasnik'. — Matea_my, Jul 15 '23 at 13:07
You may be looking for deferable constraints. As is you cannot insert into the main table, because it demands rows in the detail tables to exist, but you cannot instert into the detail tables either, because they demand that the row in the master table exists. Deferable constraints only fire at the end of the transaction (i.e. `COMMIT`), so you have time to insert the master row with missing child IDs, then insert the child rows and then update the master row. Or vice versa. Add `initially deferred deferrable` at the end of your constraints. (See https://stackoverflow.com/a/21784303/2270762.) — Thorsten Kettner, Jul 15 '23 at 13:15
Should I add those initially deferred deferrable constraints along with other code in create table vlasnik or should it be in alter table as it is? Also, what's the next step? — Matea_my, Jul 15 '23 at 13:51

score 1 · Accepted Answer · answered Jul 15 '23 at 20:38

From my point of view, you're doing it wrong.

If you wanted to alter table vlasnik the way you put it, then it should also contain two additional columns: pravna_osoba_id and fizicka_osoba_id because you're trying to apply foreign key constraint on columns that don't exist. That's not a problem. Then, in alter table vlasnik, you'd add initially deferred deferrable clause to make the whole thing work (as Thorsten commented):

SQL> create table vlasnik
  2  (
  3      vlasnik_id integer not null constraint vlasnik_pk primary key,
  4      pravna_osoba_id number not null,
  5      fizicka_osoba_id number not null,
  6      datum_zakupa date not null
  7  );

Table created.

SQL> create table pravna_osoba
  2  (
  3      naziv varchar2(20) not null,
  4      ime_ravnatelja varchar2(20) not null,
  5      prezime_ravnatelja varchar2(20) not null,
  6      datum_osnutka date not null,
  7      OIB_tvrtke varchar2(13) not null,
  8      pravna_osoba_id number not null
  9          constraint pravna_osoba_pk references vlasnik (vlasnik_id) primary key
 10  );

Table created.

SQL> create table fizicka_osoba
  2  (
  3      ime varchar2(20) not null,
  4      prezime varchar2(20) not null,
  5      OIB varchar2(13) not null,
  6      datum_rodenja date not null,
  7      primarna_djelatnost varchar2(30) not null,
  8      broj_sticenika integer not null,
  9      fizicka_osoba_id number not null
 10          constraint fizicka_osoba_pk references vlasnik (vlasnik_id) primary key
 11  );

Table created.

SQL> alter table vlasnik
  2      add (constraint vlasnik_pravna_osoba_fk
  3           foreign key (PRAVNA_OSOBA_ID) references PRAVNA_OSOBA (PRAVNA_OSOBA_ID)
  4           initially deferred deferrable,
  5           constraint vlasnik_fizicka_osoba_fk
  6           foreign key (FIZICKA_OSOBA_ID) references FIZICKA_OSOBA (FIZICKA_OSOBA_ID)
  7           initially deferred deferrable);

Table altered.

Inserting:

SQL> insert into vlasnik (vlasnik_id, pravna_osoba_id, fizicka_osoba_id, datum_zakupa)
  2  values (1, 1, 1, sysdate);

1 row created.

SQL> insert into fizicka_osoba (ime, prezime, oib, datum_rodenja, primarna_djelatnost,
  2    broj_sticenika, fizicka_osoba_id)
  3    values ('Little', 'Foot', '123', sysdate, 'None', 111, 1);

1 row created.

SQL> insert into pravna_osoba (naziv, ime_ravnatelja, prezime_ravnatelja, datum_osnutka,
  2    oib_tvrtke, pravna_osoba_id)
  3    values ('Moja firma d.o.o.', 'Big', 'Foot', sysdate, '456', 1);

1 row created.

SQL> commit;

Commit complete.

That's it.

However: why are you doing it that way?

What is vlasnik table's purpose? Apart from a primary key column, it only contains datum_zakupa. What about it? There's - at least - a subject missing - what is being zakupljeno (leased)?
Why do you want to create bi-directional foreign keys?
pravna_osoba and fizicka_osoba contain pretty much the same column list. Did you consider maintaining only one table (let's call it osoba) with additional flag column which says whether that particular row represents fizicka or pravna osoba?
- if you'll ever want to know whether certain OIB leased something, you'll have to query two tables because OIB itself doesn't tell if it is about a person (fizicka osoba) or a company (pravna osoba). If your answer is "I'll create a view", fine - then you can create one table as well and make your life simpler.
are you sure that broj_sticenika and primarna_djelatnost belong to fizicka osoba? What is your own "broj sticenika"?

So: here's my suggestion, see if it helps or not. If not, switch back to your own idea now that you know how to properly set foreign key constraints.

SQL> create table osoba
  2    (osoba_id       integer constraint osoba_pk primary key,
  3     oib            varchar2(13) not null,
  4     vrsta          varchar2(1) constraint ch_osoba_vrsta check (vrsta in ('F', 'P')),
  5     naziv          varchar2(50) not null,
  6     ravnatelj      varchar2(50),
  7     datum          date not null,
  8     djelatnost     varchar2(30),
  9     broj_sticenika number,
 10    --
 11    constraint ch_rav check ((vrsta = 'F' and ravnatelj      is     null) or
 12                             (vrsta = 'P' and ravnatelj      is not null)),
 13    constraint ch_dje check ((vrsta = 'F' and djelatnost     is     null) or
 14                             (vrsta = 'P' and djelatnost     is not null)),
 15    constraint ch_sti check ((vrsta = 'F' and broj_sticenika is     null) or
 16                             (vrsta = 'P' and broj_sticenika is not null))
 17    );

Table created.

SQL> comment on column osoba.vrsta is 'F - fizicka, P - pravna osoba';

Comment created.

SQL> comment on column osoba.naziv is 'Naziv pravne osobe ili ime i przeime fizicke osobe';

Comment created.

SQL> comment on column osoba.ravnatelj is 'Ime i prezime ravnatelja (samo za pravne osobe)';

Comment created.

SQL> comment on column osoba.datum is 'Datum osnutka pravne, odn. rodjenja fizicke osobe';

Comment created.

SQL> comment on column osoba.djelatnost is 'Primarna djelatnost (samo za pravne osobe)';

Comment created.

SQL> comment on column osoba.broj_sticenika is 'Samo za pravne osobe';

Comment created.

SQL> create table vlasnik
  2    (vlasnik_id   integer constraint vlasnik_pk primary key,
  3     osoba_id     integer constraint fk_vl_oso references osoba (osoba_id) not null,
  4     datum_zakupa date not null
  5    );

Table created.

A few inserts (intentionally wrong - checking whether check constraints work): fizica osoba first:

SQL> insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
  2    values (1, '123', 'F', 'Little Foot', 'Rav Natelj', sysdate, null, 2);
insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_STI) violated


SQL> insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
  2    values (1, '123', 'F', 'Little Foot', 'Rav Natelj', sysdate, null, null);
insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_RAV) violated


SQL> insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
  2    values (1, '123', 'F', 'Little Foot', null, sysdate, null, null);

1 row created.

Pravna osoba:

SQL> insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
  2    values (2, '456', 'P', 'Big Foot', 'Rav Natelj', sysdate, null, 2);
insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_DJE) violated


SQL> insert into osoba (osoba_id, oib, vrsta, naziv, ravnatelj, datum, djelatnost, broj_sticenika)
  2    values (2, '456', 'P', 'Big Foot', 'Rav Natelj', sysdate, 'Dje latnost', 2);

1 row created.

Vlasnik:

SQL> insert into vlasnik (vlasnik_id, osoba_id, datum_zakupa)
  2    values (10234, 1, sysdate);

1 row created.

SQL>

Thank you so much for the suggestions! The thing is - I wasn't the one who made the MEV. It was given to me by my professor to practice on and it was equally as confusing to me, too. I mustn't change the way it was given. — Matea_my, Jul 16 '23 at 09:08

How to create and insert into a table in SQL entity that has two mutually exclusive 1:1 relations?

1 Answers1