Sub query in check "not allowed here"

Question

I am trying to add a constraint onto one of my tables to ensure that my workers maintain a station if and only if that station is in the state which the worker is currently employed. However, it seems that Oracle does not like subqueries inside constraints.

I remember from a class i took while back that sqlplus does not like checks across tables, when I do this:

ALTER TABLE STATION ADD CONSTRAINT Check_Worker CHECK (Maintainer IN (
    SELECT *
        FROM 
            STATION s,
            FOREST f,
            COVERAGE c,
            STATE st,
            WORKER w
        WHERE
            s.x BETWEEN f.MBR_XMin AND f.MBR_XMax AND 
            s.y BETWEEN f.MBR_YMin AND f.MBR_YMax AND 
            f.Forest_Id = c.Forest_Id AND
            st.State = w.employing_state
        )
    );

it yells at me for:

'Question 3'
    SELECT *
    *
ERROR at line 2:
ORA-02251: subquery not allowed here. 

So, do I have any options to easily get around this? Possibly multiple constraints?

Answer 1

One way to solve this is with the data model. Use an intersection table to join WORKER and STATION.

The trick is to create unique composite keys which add STATE to the actual primary keys of WORKER and STATION.

So, something like this:

alter table WORKER add constraint wrk_state_uk unique (worker_id, employing_state) ;

alter table STATION add constraint stn_state_uk unique (station_id, state) ;

Then we create an intersecting table with lots of constraints :). ( Obviously I'm taking a guess at the datatypes here, and the column names too.)

create table station_maintainer (
    station_id number not null
    , station_state number not null
    , worker_id number not null
    , employing_state number not null
    , constraint stn_mnt_pk primary key (station_id, worker_id)
    , constraint stn_mnt_uk unique (station_id )
    , constraint stn_mnt_stn_fk foreign key (station_id)
          references station (station_id)
    , constraint stn_mnt_wrk_fk foreign key (worker_id)
          references worker (worker_id)
    , constraint stn_mnt_state_ck check (station_state = employing_state)

;

The UNIQUE key ensures each station has only one maintainer. I presumer a work can maintain more than one station,

We need the unique keys on the two tables so that the foreign keys can reference the states as well as the actual primary keys. This is a dodge but it does ensure that the STATE columns on the intersection table match the STATE columns on the parents.

---

The actual tables in the CHECK you posted are confused. There's nothing joining WORKER and STATE to STATION, soI have made a guess as to how the tables may be related. Please don't complain if my guess does not match your actual model: instead, amend your question to provide accurate and sufficient detail.