cancel
Showing results for 
Search instead for 
Did you mean: 

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

Chris26
Explorer
1,763

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?

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor
0 Kudos

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
Contributor
0 Kudos

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

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

Answers (0)