How do I get action information for a foreign key from the system tables?

[Former Member]

How do I get "action" information for a foreign key from the system tables:

E.g.

...
on update Set null
on delete set null;

...
on update CASCADE
on delete CASCADE;

...
on update CASCADE
on delete CASCADE;

...
on update restrict
on delete restrict;

After I create a foreign key with one of these attributes, which system tables can I query so that I can reverse engineer?

Note that using some tool to do this is not an option. I need to query the system tables.

Thanks,

[Breck_Carter · Accepted solution]

SYSTRIGGER is your friend...

SELECT SYSTRIGGER.event,
       SYSTRIGGER.referential_action,
       *
  FROM SYSFOREIGNKEY 
          LEFT OUTER JOIN SYSTRIGGER
             ON  SYSTRIGGER.foreign_table_id  = SYSFOREIGNKEY.foreign_table_id
             AND SYSTRIGGER.foreign_key_id    = SYSFOREIGNKEY.foreign_key_id 
             AND SYSTRIGGER.event             IN ( 'C', 'D' );

Note the LEFT OUTER JOIN since RESTRICT actions don't have rows in SYSTRIGGER.

Here's what the Help says about those columns:

event 
The event or events that cause the trigger to fire. 
This single-character value corresponds to the trigger event 
that was specified when the trigger was created.
A - INSERT, DELETE 
B - INSERT, UPDATE 
C - UPDATE COLUMNS 
D - DELETE 
E - DELETE, UPDATE 
I - INSERT 
U - UPDATE 
M - INSERT, DELETE, UPDATE

referential_action
The action defined by a foreign key. This single-character 
value corresponds to the action that was specified when the 
foreign key was created.
C    CASCADE 
D    SET DEFAULT 
N    SET NULL 
R    RESTRICT 

To repeat... don't go looking for SYSTRIGGER.referential_action = 'R', it's a magical phantom value, like the pot of gold at the end of the rainbow... i.e., it doesn't exist in reality. It's the default, SQL Anywhere don't need no steenking trigger to throw an exception 🙂