Help with constraints

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 Received on Wed Dec 06 2006 - 23:31:49 CST