CODEWITHSUNDEEP
sqlsql-serverdelete-row
Bruno Croys Felthes
Bruno Croys Felthes

Reputation: 1203

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.

Upvotes: 0

Views: 190

Answers (5)

user142253
user142253

Reputation: 524

Something like this may help you:

/* Table X is the child table Table Y is the parent table */

DELETE FROM TABLE_X where Y_ID not in (SELECT Y_ID FROM TABLE_Y)

Upvotes: 1

srini.venigalla
srini.venigalla

Reputation: 5145

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.

Upvotes: 1

Mark Monforti
Mark Monforti

Reputation: 473

DElete from x where col = 0 and primarykey not in ( PrimaryKey from x Inner Join Y on x.FKey = Y.PrimaryKey )

Upvotes: 0

zmbq
zmbq

Reputation: 39023

You can check the system tables, parse the constraints and use them to build the JOINs dynamically. This is not going to be easy.

Another way would be to delete the rows one by one (SELECT all the IDs, then delete each row using its primary key value). The DELETE operations that fail are those deleting a line with a constraint.

Do that inside a transaction and rollback at the end, so you don't actually delete the lines.

By the way, I don't recommend this practice, it's weird and inefficient. But it will work.

Upvotes: 0

usr
usr

Reputation: 171206

There is no generic way. You need to use joins (or generate joins at runtime from metadata which is generic but not built-in).

Upvotes: 0

Related Questions