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

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?

Answer

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.