Search code examples
sqlsql-serverdelete-row

Delete rows in a table, but preserve rows with FK contraints

I need to delete rows in a table, such as 

DELETE FROM TABLE_X WHERE COL_A = 0

But I want to not delete any row that can have a constraint violation, and I need to know what rows have this violation...

I know that I can do joins with the relation tables, but i want to know if there exists any generic way to know what lines upon delete will have a constraint violation.

Solution

  • Assuming you use JDBC, there is a Metadata function named GetExportedKeys()

    http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getExportedKeys%28java.lang.String,%20java.lang.String,%20java.lang.String%29

    Using that, you first get the Foreign Key constraints. Using that information, you construct a Join Statement to catch the violations. You run the statement to get the actual rows that would violate the constraints.