Creating Table How to key in more than one primary key

Question

My question is how to make this work basically an office and non office workers can have more than one loan but since our loan id primary key I can't more than one any ideas how to do this?

  CREATE TABLE loaner 
  (
      l_id NUMBER(10) primary key,
      type VARCHAR2(20),
      no_days_overdue NUMBER(3),
      loan_start_date date,
      loan_end_date date,
      fine_imposed NUMBER(55),
      constraint loaner_uk unique (l_id, type),
      constraint chk_type check (type='office' or type='nonoffice')
    );

    commit;

CREATE TABLE office 
(
  office_id NUMBER(5) primary key,
  l_id NUMBER(10) ,
  type VARCHAR2(10),
  office_forname VARCHAR2(30),
  office_surname VARCHAR2(30),
  email VARCHAR2(50),
  address VARCHAR2 (100),
  constraint office_fk foreign key (l_id, type) references loaner (l_id, type),
  constraint office_type_chk check (type='OFFICE')
);
commit;

CREATE TABLE nonoffice 
(
  nonoffice_id NUMBER(5), 
  l_id NUMBER(5),
  type VARCHAR2(10),
  non_forname VARCHAR2(30),
  non_surname VARCHAR2(30),
  constraint nonoffice_loaner_fk foreign key (l_id, type) references loaner (l_id, type),
  constraint nonoffice_type_chk check (brw_type='nonoffice')
);
commit;

Your question is a little hard to follow but from what it sounds like, you want a nonoffice and an office to have more then one loan? If this is the case, add PK of office to loaner as a FK and add the PK of nonoffice to loaner as a FK instead of adding loaner PK to office and nonoffice as FK — Stc5097, Dec 02 '14 at 16:40
@Stc5097 then there will a problem if loaner will have no pk... — Alex Hummedov, Dec 02 '14 at 16:43
loaner will still have a PK. You want a one to many relationship from office to loaner and from nonoffice to loaner. With this relationship, the PK of the ONE side must be present as a FK in the many side. You just have it reversed — Stc5097, Dec 02 '14 at 16:45
so basically you mean like this? (sorry for making it more complicated i just want to understand and learn) 1. keep the loaner id as a pk in loaner table 2. put the loaner id into both office and non office table? @Stc5097 — Alex Hummedov, Dec 02 '14 at 16:51

score 0 · Answer 1 · answered Dec 03 '14 at 12:46

        CREATE TABLE loaner 
          (
              l_id NUMBER(10) primary key,
              type VARCHAR2(20),
              no_days_overdue NUMBER(3),
              loan_start_date date,
              loan_end_date date,
              fine_imposed NUMBER(55),
              office_id NUMBER(5),
              nonoffice_id NUMBER(5),
              constraint office_fk foreign key (office_id) references office(office_id),
              constraint nonoffice_fk foreign key (nonoffice_id) references nonoffice(nonoffice_id)
            );

            commit;

        CREATE TABLE office 
        (
          office_id NUMBER(5) primary key,
          l_id NUMBER(10) ,
          type VARCHAR2(10),
          office_forname VARCHAR2(30),
          office_surname VARCHAR2(30),
          email VARCHAR2(50),
          address VARCHAR2 (100),
        );
        commit;

        CREATE TABLE nonoffice 
        (
          nonoffice_id NUMBER(5) primary key, 
          l_id NUMBER(5),
          type VARCHAR2(10),
          non_forname VARCHAR2(30),
          non_surname VARCHAR2(30),
        );
        commit;

Creating Table How to key in more than one primary key

1 Answers1