sql oracle-database database-design one-to-one

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

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

Solution

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>