How to check validity of a reference sys_id

[shlomisividia]

Hi,

I have a table "table1" with field "f1" which is a reference to "table2" sys_id.

If a record in table2 is deleted, I want the value of f1 to get NULL value.

Constraints:

• table2 is a rotating table
• table2 is usually a pretty large table

I prefer not to have a "on delete" BR on table2 that will do that for me.

Also, AFAIK, non of the cascade rule options is suitable for this use case.

The next straight forward solution is to have a job that "cleans" bad references from table1, i.e. queries it's records and extract all f1 values to a list. Then queries table2 to get the valid references, and from there extract the invalid references. I also prefer not to do that...

Any magic?

Cheers,

-Shlomi

[shlomisividia]

Thanks, I appreciate the time the your time here.

That's pretty much the straight forward way to do it. I was looking for some kind of magic way that will save potential hundreds or thousands of queries (however indexed/optimized they are)

[asifnoor]

hehe.. i don't think there is any magic way to do it 🙂

[shlomisividia]

But I think there can be: Say I have a cascade-delete rule "clear" on f1.

Once the referenced record is deleted from table2, it will delete reference in f1, but not the value. It should let me choose if I want to delete the value, or flag is some other way that the referenced record is not valid

Since I know there isn't a cascade rule that gives me this functionality, I was hoping to find the flag I was talking about.