Here's one way to approach this:
- Add an "order management" property to Jedi units with values SELF and MANAGED (or similar)
- Propagate this jedi_guard_contingent and when this is :
- MANAGED ensure fk_output is not null
- SELF ensure fk_output is null
- Create a unique constraint over ( fk_jedi_unit, fk_outpost )
And you're done!
create table outpost (
id integer not null primary key
);
insert into outpost values ( 1 );
insert into outpost values ( 2 );
create table jedi_unit (
id integer not null primary key,
members integer default 10 not null ,
order_management varchar2(10) not null,
unique (id, order_management)
);
insert into jedi_unit values ( 1, 10, 'SELF' );
insert into jedi_unit values ( 2, 10, 'MANAGED' );
insert into jedi_unit values ( 3, 10, 'SELF' );
create table jedi_guard_contingent (
id integer not null primary key,
fk_jedi_unit integer not null,
order_management varchar2(10) not null,
fk_outpost integer,
used_members integer not null,
constraint jgc_fk_unit foreign key ( fk_jedi_unit, order_management )
references jedi_unit( id, order_management ),
constraint jgc_fk_outpost foreign key ( fk_outpost ) references outpost( id ) ,
constraint jgc_output_managed_c check (
case when order_management = 'MANAGED' then fk_outpost else 1 end is not null
),
constraint jgc_output_self_c check (
case when order_management = 'SELF' then fk_outpost end is null
),
unique (fk_jedi_unit, fk_outpost)
);
insert into jedi_guard_contingent values ( 1, 1, 'SELF', null, 5 );
insert into jedi_guard_contingent values ( 2, 1, 'SELF', 1, 5 );
insert into jedi_guard_contingent values ( 3, 2, 'MANAGED', 1, 5 );
insert into jedi_guard_contingent values ( 4, 2, 'MANAGED', 2, 5 );
insert into jedi_guard_contingent values ( 5, 3, 'SELF', null, 10 );
insert into jedi_guard_contingent values ( 6, 1, 'SELF', 3, 5 );
insert into jedi_guard_contingent values ( 7, 2, 'MANAGED', null, 5 );
insert into jedi_guard_contingent values ( 8, 1, 'SELF', null, 5 );
select * from jedi_guard_contingent;
ID FK_JEDI_UNIT ORDER_MANAGEMENT FK_OUTPOST USED_MEMBERS
1 1 SELF <null> 5
3 2 MANAGED 1 5
4 2 MANAGED 2 5
5 3 SELF <null> 10 Depending on how you want to manage changes to jedi_guard_contingent, you may want to create the FK to jedi_unit deferrable. This allows you to update the order management property of a unit. Then changes its assignments.
I thought about using triggers, but I think it won't be reliable with multiple session then, would it?It's possible to do this with triggers. But it's tricky, making mistakes likely.
And thanks for the cool test case ;)