Help with constraints

[sony....@gmail.com]

Hello :
Im trying to setup a constraint between two tables in Oracle 9
TABLE_A has a column( number ) named ROLE_ID

TABLE_B has three columns named ROLE_KEY, REGION and COMPANY

I want to set up a constraint on TABLE_A so that user can only insert
ROLE_IDs such that all the following three conditions are met
1. the value of ROLE_ID should be equal to one of teh existing values
of ROLE_KEY from TABLE_B
2. REGION='US' ( REGION value for teh matching ROLE_KEY should be US )
3. COMPANY='ABCD' ( COMPANY value for teh matching ROLE_KEY should be
ABCD )

In other words...
The ROLE_ID being inserted should EXIST in the result of teh query
"select ROLE_KEY from TABLE_B where REGION='US' and COMPANY='ABCD'"

I would preferably have teh solution as a single constraint.

Thanks a lot for reading

--sony

[orastef]

you have to do the constraint with a (update, insert and delete before)
trigger on Table A, which checking the conditions you want. But a
trigger make your insert slow, because there has to be checked
something. But there is no other solution.