Oracle sql constraint subquery?

Question

Hi,
Basically im trying to have a constraint on field on one table based on another table.
So in my datbase i have a police table and appointment table.

In my police table there are many tuples with different ranks.
In the appointmnet table there is a police_id as a foreign key. However i need to make it so that only sergeants can make appointmnets (rank_id = 2)
when i put a constraint as

CHECK( POLICE_ID IN ( SELECT police_id from police where rank_id = 2))

But oracle doesnt allow constraints with subqueries.
can anyone help me get around this?
Cheers,
Ben

Answer 1

You could create in insert and update trigger on your appointment table.

The trigger can validate the new police_id (and it will be allowed to do a subquery, no problem.

If the police_id isn't valid, you can throw an exception that will prevent the insert/update from happening.

Answer 2

That is correct:check constraints in Oracle are fairly limited compared to other databases. The only check you can do is on columns of the current table.



The usual way (as indicated by rachetr is to use a trigger). Something like the following. Note that you cannot use a subquery directly in a IF statement, so you need a separate select to check the existence of your POLICE_ID.



create or replace trigger police_id_check before insert or update of police_id on your_table

for each row

declare

police_id_count number;

begin

select count(*) into police_id_count

from police

where police_id = :NEW.police_id

and rank_id = 2;

if police_id_count = 0 then

raise_application_error (-20000, 'police_id not in police table');

end if;

end;

/

show errors



If you try and update or insert a row with an invalid police_id, you will get an error. For example:

update your_table set police_id = 9999 where ....

(where 9999 does not exist in the POLICE table - or exists with a RANK other than 2) will fail like this:



ERROR at line 1:

ORA-20000: 'police_id not in police table

ORA-06512: at "SCOTT.POLICE_ID_CHECK", line 9

ORA-04088: error during execution of trigger 'SCOTT.POLICE_ID_CHECK'

Answer 3

You could use a function returning the result of

SELECT police_id from police where rank_id = 2

and call this function in constraint instead of subquery.