If I delete a record from a table it is deleted at many other places

Question

I am working on a database built by the previous team. I have to delete certain records from a table (Example shown below).

DELETE FROM table WHERE id = 5541

While doing this process, some records from the other tables with the same id is getting deleted. Could someone help how to overcome this problem?

Probably the intended behavior. (ON DELETE CASCADE foreign keys.) — jarlh, Apr 12 '21 at 09:15
Does this answer your question? [remove ON DELETE CASCADE](https://stackoverflow.com/questions/26604810/remove-on-delete-cascade) — Peter B, Apr 12 '21 at 09:21
@Mystogan The referenced question is about MySQL, this question is about SQL server. Furthermore this is quite a dangerous advise, as it will most certainly lead to inconsisten data, unless one really know what he is doing (which for OP -- no offense -- does not seem to be the case) — derpirscher, Apr 12 '21 at 09:23
@Mystogan I strongly advice to NOT EVER disable foreign keys. It would lead to inconsistent data and make your data in your database corrupt. — GuidoG, Apr 12 '21 at 12:08

Madhukar · Accepted Answer · 2021-04-12T09:25:02.970

3

In SQL Server, there is statement called ON DELETE CASCADE which deletes the record from the child table if the record is deleted from the parent table. This can be set using the ALTER STATEMENT as shown below. To remove the cascading, try altering the child table back to default. The default is NO CASCADING.

ALTER TABLE ChildTable
ADD CONSTRAINT FKey
    FOREIGN KEY (col1, col2, ... coln)
    REFERENCES ParentTable (Pcol1, Pcol2, ... Pcoln)
    ON DELETE CASCADE

There is UPDATE CASCADE as well if the data in the child table should be updated when the parent table is updated.

edited Apr 12 '21 at 09:25

answered Apr 12 '21 at 09:15

Madhukar

1,194
1
13
29

This shows how to *add* `ON DELETE CASCADE`; not how to remove it. – Peter B Apr 12 '21 at 09:20
@PeterB added a note to address this. – Madhukar Apr 12 '21 at 09:25
1

@Madhukar . . . `ON DELETE CASCADE` is not specific to SQL Server. It is part of standard SQL. – Gordon Linoff Apr 12 '21 at 11:00

score 1 · Answer 2 · answered Apr 12 '21 at 09:19

You database most certainly contains foreign key constraints with cascading deletes see docs.

You may be able to remove these foreign keys, but of course, then deleting some rows will leave you with inconsistent data.

Another possibilty is to just remove the cascading deletes. But then of course, you won't be able to delete any rows which are referenced by records from other tables, as SQL server will ensure the consistency of your data.

If I delete a record from a table it is deleted at many other places

2 Answers2