Delete from a table with many foreign keys

Question

Currently I am working in a DB with a tree-like structure. Basically there is one top level table, and there are about 10 tables that have a foreign key on that table. Each of these tables has another 10 tables with a foreign key constraint. Now my goal is to delete some records from the main table, and also delete the children, grandchildren etc. but of course the foreign keys make this a painstaking task.

I have searched a bit and came across checked this question on using cascade delete and I guess this would do the trick if I was dealing with only a few tables. However, in my current setting it would still not be pleasant to alter over 100 tables one by one.

Therefore I am asking whether there is an easy way to delete values that have foreign keys when many tables are involved.

As I have some control over the relatively small DB, query efficiency and the risk of values being deleted by others are not really a concern. So it may be easy to apply cascade delete on all tables, but I am also open to completely different solutions (Using T-SQL in microsoft server studio 2008).

It depends, although you delete the parent records, do you want to keep these values in children for *history keeping* purpose? — bonCodigo, Jan 23 '13 at 16:14

score 2 · Answer 1 · edited May 23 '17 at 12:30

2

have you looked at this? This guy is autogenerating the delete statement based on foreign key relationships in database:

Generate Delete Statement From Foreign Key Relationships in SQL 2008?

I am not personally a big fan of it but it's something to try.

edited May 23 '17 at 12:30

Community

1
1

answered Jan 23 '13 at 16:27

Farzad

1,132
9
21

I understand that I should replace `MyTableName` with my table name. But how can I indicate that I want to remove the records where the field `IsActive = 0`. And do I need to do anything to make sure the db is 'safe' again after I am done? – Dennis Jaheruddin Jan 24 '13 at 08:52

score 1 · Answer 2 · answered Jan 23 '13 at 16:14

1

I think the best way to do something like this, in a different way, is to use a logic deletion, with a column named "status" or so that controls if that field is still to be considered under your program logic. The status column has to be created only on the top-level table. When in an SQL statement, you insert an "where status = 1" or something like that.

Hope that helps, since it's an entirely different approach to the question.

answered Jan 23 '13 at 16:14

Tiago César Oliveira

812
8
18

this means changing every single query they have in their app to account for that logically deleted column. cascading deletes would be a lot cheaper to do – Farzad Jan 23 '13 at 16:20
I think I understand this, but if that means I still have to create the joins to trace this value it does not seem to be a usefull solution. – Dennis Jaheruddin Jan 23 '13 at 16:20
Expensive. Good idea though. – Matt Jan 23 '13 at 16:25
After reading it again, I believe we already have something like this. On several levels the record can be made inactive after which it and its children no longer influence the program. However this is not the 'deletion' I am looking for. Thanks for your answer though! – Dennis Jaheruddin Jan 23 '13 at 16:43

score 1 · Accepted Answer · edited May 23 '17 at 12:14

The delete cascade would be a good solution for a "main" table.

To avoid altering manually all your tables, you can use a script, as found here with a minor change (last line, change <yourTable> by your table Name).

select
  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
,  CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + 
      '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + 
      ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  sys.objects.[name] + ']([' +
  sys.columns.[name] + ']) ON DELETE CASCADE; '
 from sys.objects
  inner join sys.columns
    on (sys.columns.[object_id] = sys.objects.[object_id])
  inner join (
    select sys.foreign_keys.[name] as ForeignKeyName
     ,schema_name(sys.objects.schema_id) as ForeignTableSchema
     ,sys.objects.[name] as ForeignTableName
     ,sys.columns.[name]  as ForeignTableColumn
     ,sys.foreign_keys.referenced_object_id as referenced_object_id
     ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
     from sys.foreign_keys
      inner join sys.foreign_key_columns
        on (sys.foreign_key_columns.constraint_object_id
          = sys.foreign_keys.[object_id])
      inner join sys.objects
        on (sys.objects.[object_id]
          = sys.foreign_keys.parent_object_id)
        inner join sys.columns
          on (sys.columns.[object_id]
            = sys.objects.[object_id])
           and (sys.columns.column_id
            = sys.foreign_key_columns.parent_column_id)
    ) ForeignKeys
    on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
     and (ForeignKeys.referenced_column_id = sys.columns.column_id)
 where (sys.objects.[type] = 'U')
  and (sys.objects.[name]  = '<yourTable>')

You copy the first column result in a Management Studio query window, execute, then do the same with the second column, and voilà, you're done.

I understand that I should replace `yourtable` with my table name. But how can I indicate that I want to remove the records where the field `IsActive = 0`. And how would I set the foreign keys to their original state afterwards? — Dennis Jaheruddin, Jan 24 '13 at 08:48
@DennisJaheruddin this is not a version where "IsActive = 0". This is a version to enable cascade delete. So the records will be deleted (no question of "IsActive" or not. So the idea is not to put the foreign keys back to their original state. But if you want to, just repass the script, moving the "ON DELETE CASCADE" part. — Raphaël Althaus, Jan 24 '13 at 08:53
Thanks, restoring the foreign keys appears straightforward. So do I understand correctly that the script does not actually delete anything, but just makes it possible? And afterwards I can just delete the entries myself? — Dennis Jaheruddin, Jan 24 '13 at 08:58
@DennisJaheruddin yes, you understood well. It just enables the ON DELETE CASCADE behaviour. — Raphaël Althaus, Jan 24 '13 at 09:00

score 0 · Answer 4 · answered Jan 23 '13 at 16:29

Unless you are willing to sacrifice data integrity, utilize the T_SQL CASCADE DELETE option. You can create an "ALTER TABLE PARENT1 ADD FOREIGN KEY" and copy and paste your way to a safe solution.

This will take far less time than developing and testing a programmatic solution, which will not be executed if a delete statement is issued through a third-party program - like Mgmt. Studio.

score -1 · Answer 5 · answered Jan 23 '13 at 16:28

-1

As was stated in the question you referenced you can automatically take care of the deletion through the use of the ON DELETE CASCADE.

answered Jan 23 '13 at 16:28

Matt

1,441
1
15
29

But how can I do this without visiting numerous tables (and more may be added later) and altering them one by one? – Dennis Jaheruddin Jan 23 '13 at 16:30
Alter scripts are easy enough. A little database administration now goes a long way later. The alternatives would be 1) Delete information from the underlying tables first with the key from the parent table, then delete the information in the parent table. 2) Utilize the delete flag as suggested in another answer. Both either harder to maintain or more expensive than my suggestion. – Matt Jan 23 '13 at 16:45

Delete from a table with many foreign keys

5 Answers5