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



Comments

Popular posts from this blog

Solution for the fault: "System.Diagnostics.Process.Start is not working on IIS, but working on ASP.NET Development Server"

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