Oracle referential integrity against data range in PK table

Question

Consider the following design:

LEGAL_CASE table (columns)
--------------------------------
LEGAL_CASE_ID
APPELLATE_CRT_ID
DISTRICT_CRT_ID
TRIAL_CRT_ID

with all courts being defined in a lookup table

COURT table (data)
--------------------------------------------
CRT_ID     CRT_TYPE      CRT_NAME
--------------------------------------------
1          A             APPELLATE COURT 1
2          A             APPELLATE COURT 2
3          D             DISTRICT COURT 1
4          D             DISTRICT COURT 2
5          T             TRIAL COURT 1
6          T             TRIAL COURT 2

The standard way of doing it, I suppose, would be to have a separate lookup table for each court type but I prefer dumping them all in a single one under different codes, for compactness and elegance. So then I would like to have some form of referential integrity constraint (if the above rules out FK) that will enforce that all it can go into APPELLATE_CRT_ID are the CRT_ID values from the COURT table but only where CRT_TYPE = 'A' etc. A regular FK would allow Ds and Ts as well but I would like to make it more restrictive.

Is there a way to formulate a FK that would restrict a range of values in the primary key table or should I just go with a RULE or another type of CONSTRAINT?

Answer 1

If you want referential integrity given that lookup table, you'd need to add multiple CRT_TYPE columns to the legal_case table and include those as part of the foreign key. Something like

CREATE TABLE legal_case (
  legal_case_id      integer primary key,
  appellate_crt_id   integer,
  appellate_crt_type varchar2(1),
  district_crt_id    integer,
  district_crt_type  varchar2(1),
  ...
  constraint fk_appelate_crt foreign key (appelate_crt_id, appellate_crt_type) references court(crt_id, crt_type),
  constraint fk_district_crt foreign key (district_crt_id, district_crt_type)  references court(crt_id, crt_type),
  ...
);

Of course, this would require that the primary key (or a unique constraint though I dislike foreign keys that reference nno-primary keys) of the court table would be crt_id, crt_type rather than just crt_id.

Otherwise, you'd be looking at writing triggers to verify this sort of thing (which gets rather painful if you want to prevent session A from modifying a court row while session B has uncommitted changes that depend on the existing state of that row) or possibly creating an on commit materialized view with an appropriate set of constraints that combined the data from these two tables.