Howto get list of foreign keys including ON UPDATE and ON DELETE setting

[Chris26]

I am looking for a way to get a list of all foreign key relations in a database (ASA 16) including the setting for ON UPDATE and ON DELETE. I looked at several system views and stored procedures but none of those seem to include the ON UPDATE and ON DELETE setting

Anybody have a clue how to get that information?

[VolkerBarth · Accepted solution]

Try adding the SYSTRIGGER system view to your query. Rows with an according foreign_table_id describe the refaction in column "referential_action", such as "C" (for CASCADE) or "N" (for SET NULL).

Note: The doc page lists also "R" as type for restrict actions but I don't find such entries - I guess that's because for restricted actions, no system triggers are created...

So I guess a left join over SYSTRIGGER would allow to conclude that missing SYSTRIGGER entries mean those FKs have RESTRICT action.

[VolkerBarth · Accepted solution]

And now I see it has already been answered before...:)

Memo to myself: Don't ignore the "Related questions" list on the rigt pane...