How do I check that I removed required data only?

I have a really big database (running on PostgreSQL) containing a lot of tables with sophisticated relations between them (foreign keys, on delete cascade and so on). I need remove some data from a number of tables, but I'm not sure what amount of data will be really deleted from database due to cascade removals.

How can I check that I'll not delete data that should not be deleted?

I have a test database - just a copy of real one where I can do what I want :)

The only idea I have is dump database before and after and check it. But it not looks comfortable. Another idea - dump part of database, that, as I think, should not be affected by my DELETE statements and check this part before and after data removal. But I see no simple ways to do it (there are hundreds of tables and removal should work with ~10 of them). Is there some way to do it?

Any other ideas how to solve the problem?

Answers


You can query the information_schema to draw yourself a picture on how the constraints are defined in the database. Then you'll know what is going to happen when you delete. This will be useful not only for this case, but always.

Something like (for constraints)

select table_catalog,table_schema,table_name,column_name,rc.* from
information_schema.constraint_column_usage ccu, 
information_schema.referential_constraints rc 
where ccu.constraint_name = rc.constraint_name

Using psql, start a transaction, perform your deletes, then run whatever checking queries you can think of. You can then either rollback or commit.


Need Your Help

How would I make a dialog look different in a C# Windows program like those in Visual Studio 2010?

c# winforms styles

Visual Studio 2010 has a completely different visual style on the child dialogs, the ones that you can move around and re-size within the program.

TCPDF Output() function method not working if script is called through AJAX, but works if script is called in browser

javascript php jquery ajax pdf

I'm using TCPDF to dynamically generate PDF documents. I am using jQuery's $.ajax() method to call the PDF generation script (pdf_output.php) which saves the PDF to the server's file system using T...