sql - How do I ensure integrity between unrelated tables? -

i started learning database design, , i'm working oracle 11g , sql developer.

i have these 3 business rules db:

• each officer must enroll in 1 , 1 insurance companies. each insurance company may enroll 1 or more officers
• each insurance company must provide @ least 5 different types of insurance types. each type of insurance may provided 4 insurance companies or none @ all
• each insurance type may subscribed 1 or more officers. each officer may subscribe 5 different insurance covers provided same company.

. . .

so far, good, came 5 tables (ins_coy, offr, ins_type, provide, , subscribe). provide , subscribe came composite tables since relationships between ins_coy , ins_type, , offr , ins_type both m:m relationships.

pk , fk attributes each of table below:

ins_coy table
coy_id - pk

offr table
offr_id - pk
coy_id - (fk referencing ins_coy.coy_id))

ins_type table
type_id - pk

provide
coy_id , type_id - (composite pk)
coy_id - (fk referencing coy.coy_id)
type_id - (fk referencing ins_type.type_id)

subscribe
naf_no , type_id - (composite pk)
naf_no - (fk referencing offr.offr_id)
type_id (fk referencing ins_type.type_id)

.
.
.

the tables have been sucessfully created, , sample data inserted.

so, problem - on subscribe table, how ensure integrity type_id attached offr_id ins_type provided coy enrolled in?

sample data tables

i.e ...from tables, "offr 4250" enrolled in "coy 1", , "coy 1" doesn't provide "ins_type 13", however, because there's no constraint check this, "offr 1" subscribed "ins_type 13" on subscribe table.

you can using controlled redundancy , composite fk constraints:

create table offr (     offr_id int not null,     coy_id int not null,     primary key (offr_id),     foreign key (coy_id) references ins_coy (coy_id),     unique key (offr_id, coy_id) ); 

i added composite unique key (offr_id, coy_id) support composite fk constraint on subscribe table.

create table provide (     coy_id int not null,     type_id int not null,     primary key (coy_id, type_id),     foreign key (coy_id) references ins_coy (coy_id) ); 

the composite primary key here perfect composite fk constraint on subscribe table.

create table subscribe (     naf_no int not null,     coy_id int not null,     type_id int not null,     primary key (naf_no, type_id),     foreign key (naf_no, coy_id) references offr (offr_id, coy_id),     foreign key (coy_id, type_id) references provide (coy_id, type_id) ); 

overlapping composite fk constraints ensure officer can subscribe insurance offered company he/she enrolled in. coy_id logically redundant required integrity , there's no risk of update anomalies due fk constraints.

alternatively, use triggers check values related via inner joins:

create trigger check_subscribe before insert or update on subscribe each row when not exists (     select 1     offr     inner join provide on offr.coy_id = provide.coy_id     offr.offr_id = new.naf_no , provide.type_id = new.type_id ) raise_application_error (num => -20000, msg => 'officers can subscribe types provided company'); 

disclaimer: unable test on sqlfiddle , don't have oracle installed, it'll point in right direction.