I am trying to create a table serving as a log of calculations. Upon entering data, I would like to check if the data entered in some columns exists in another table. In this other table, the data is in fact the primary key, so I could create a FOREIGN KEY constraint - however, I only want the consistency with this "foreign key" to be checked once for each row after newly inserting it, but never again. I do not want to create an actual parent-child relationship of these tables, as the 'child' should keep records regardless of changes to the 'parent'.
I have attempted to implement this using a CHECK constraint, e.g.:
CONSTRAINT CSTR_CALCLOG1 CHECK (USERID in(select USERID from USER_TABLE))
Resulting in the error:
ORA-02251: subquery not allowed here
It seems that check constraints are fairly limited as described here, and therefore not the right tool for the job:
http://www.dba-oracle.com/t_oracle_check_constraint.htm
How can this be achieved?