CODEWITHSUNDEEP
sqloracle-databasecreate-table
Alex Hummedov
Alex Hummedov

Reputation: 37

SQL Creating Table Dependencies

I've been stuck at creating tables.

Here is the code I am stuck at how to make the code work in loan_type to write office worker or non office worker 

create table loaner 
(
   loan_id number(5) primary key,
   loan_type VARCHAR2 (16),
   loan_start_date date,
   loan_end_date date,
)

create table office_worker 
(
   worker_id number(5) primary_key,
   loan_id number(5) references loaner(loan_id),
   worker_name varchar2(50)
)

create table nonoffice_worker 
(
   nonworker_id number(5) primary_key,
   loan_id number(5) references loaner(loan_id),
   nonworker_name varchar2(50)
);

commit;

Upvotes: 0

Views: 2193

Answers (2)

Aramillo
Aramillo

Reputation: 3226

You can also add a check constraint to your table, but you must check that column loan_type only contains desired values(office worker or non office worker), else this won't work:

alter table loaner add (CONSTRAINT chk_loan_type CHECK 
(loan_type='office worker' or loan_type='non office worker'));

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132620

You can't create a constrain to check that with the existing table structures. A common way to do it is like this:

create table loaner (
loan_id number(5) primary key,
loan_type VARCHAR2 (16),
loan_start_date date,
loan_end_date date,
constraint loaner_uk unique (loan_id, loan_type)
);

create table office_worker (
worker_id number(5) primary_key,
loan_id number(5),
loan_type VARCHAR2 (16),
worker_name varchar2(50),
constraint office_worker_loaner_fk foreeign key (loan_id, loan_type) references loaner (loan_id, loan_type),
constraint office_worker_loan_type_chk check (loan_type = 'OFFICE')
);

create table nonoffice_worker (
nonworker_id number(5) primary_key,
loan_id number(5),
loan_type VARCHAR2 (16),
nonworker_name varchar2(50),
constraint nonoffice_worker_loaner_fk foreeign key (loan_id, loan_type) references loaner (loan_id, loan_type),
constraint nonoffice_worker_loan_type_chk check (loan_type = 'NONOFFICE')
);

That is:

  1. Create a redundant UNIQUE constraint in (load_id, loan_type) in the first table.
  2. Add loan_type to the subtype tables and base the foreign key on (loan_id, loan_type).
  3. Add a check constraint to each subtype table to ensure the correct loan_type is used.

Upvotes: 1

Related Questions