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]

Come to think of it, I guess that having a "on delete" BR on table2 will act similarly to a cascade rule, but if there are any optimizations that are used in a regular cascade rule, this BR will not take advantage of them.

[asifnoor]

Hi,

Do you know which code actually deletes the entry in table2? If yes, then you can write your code there or if there are multiple places, check if there is any common function that is being called and fire an event in that place and then write a script action listening to that event and set f1 value to null in table 1.

Mark the comment as a correct answer and helpful if it helps.

[shlomisividia]

Thanks for the reply.

table2 is almost never deleted manually or by any script or code (although it is allowed).

The rotation does most of the deletion of records.

[asifnoor]

Ah okay.

Not sure how the performance will differ if we use gliderecord get("sys_id") on table2 vs runnin gliderecord next on table2. If the get() is optmized solution, then you can loop through table1 records, get f1 and check in table 2 using get(f1 value). if it returns false, set f1 to null.

Mark the comment as a correct answer and helpful if it helps.