PL/SQL One-to-one Relationship

create table Office
  office_id numeric(10) not null,
  building_name varchar2(20),
  constraint u_office unique(building_name),
  constraint office_pk primary key(office_id)

create table Personel
  personel_id number(10) not null,
  personel_name varchar2(20),
  office_id number(10),
  constraint u_personel unique(office_id), --with this constraint each personel will have just one office id.
  constraint personel_pk primary key(personel_id)

--Here in the blow code block, as you can see in personel_detail table, personel_id is a PK and also it is a FK that references personel table.
--In this condition we can't insert any personel data in to personel detail table which does not have any reference in personel table. For example if there is a personel with personel_id=1 then we can insert one data with this same personel_id-which is 1- into personel_detail table but we can't do the same thing with the personel_id=2, because there is no personel with personel_id=2.

create table personel_detail
  personel_id number(10,0) not null,
  personel_info nvarchar2(20),
  constraint personel_detay_pk primary key(personel_id),
  constraint personel_detay_fk foreign key(personel_id)
  references personel(personel_id)


Popular posts from this blog

Fix for "Websites cannot be started unless both the Windows Activation Service (WAS) and the World Wide Web Publishing Service (W3SVC) are running, Both services are currently stopped"

Solution for "Can not obtain Oracle client information from registry"

How to specify proxy credentials in web.config