Hi there,

I wonder when you turn on the foreign key constraint, is there a way to just get the report which row has the violation, without physically delete and move the row to the exceptional table?

Thanks

You will have to export the data to some external csv or delimited file. Then drop the table and recreate it again and try loading the data using load command using the for exception option of the load command and specify the name of the exception table in which the rows violating the constraint will be inserted.

Assuming you're on z/OS, just use the "delete no" option of the "check data" utility.

Here is the syntax for check data:
.-----------------------------------. |
| V | .-DELETE--NO----------------. |
'-FOR--EXCEPTION----IN--table-name1--USE--table-name2-+--+---------------------------+-'
| .-LOG--YES-. |
'-DELETE--YES--+----------+-'
'-LOG--NO--'

Sorry, I don't know how much you can see it , but anyway, should I still create and specify the exception table "table-name2"?

Basically I just want to check which rows have the violation and don't want to make any data changes.

In that case, just don't use any "FOR EXCEPTION" or "DELETE" options in the statement.