CODEWITHSUNDEEP
sqlsql-servert-sqlforeign-keys
varun kumar dutta
varun kumar dutta

Reputation: 202

How to delete records from parent table which is referenced by multiple child tables?

I have a table which is referenced by multiple tables (around 52) and further,few of the child tables have multiple foreign keys also that is referencing other tables too.

I want to delete a record from parent table, I am unable to do so, as I am getting error "The DELETE statement conflicted with the REFERENCE constraint "FK_xxx". The conflict occurred in database "MyDB", table "dbo.A", column 'x'."

I want a generalized T-SQL solution which is irrespective of tables and number of references.

Upvotes: 1

Views: 4423

Answers (2)

Freddie Bell
Freddie Bell

Reputation: 2287

Your problem is this: A FK constraint is designed to prevent you from creating an orphaned child record in any of the 52 tables. I can provide you with the script you seek, but you must realise first that when you try to re-enable the FK constraints the constraints will fail to re-enable because of the orphaned data (which the FK constraints are designed to prevent). For your next step, will have to delete the orphaned data in each of the 52 tables first anyway. It is actually much easier just to redo the constraints with ON DELETE CASCADE, or drop the constraints and forget about referential integrity altogether. You can't have it both ways.

Upvotes: 0

chickahoona
chickahoona

Reputation: 2034

You have to look at the "on delete" keyword which is a part of the foreign key constraint definition. Basically you have 4 options:

  • NO ACTION (does nothing)
  • CASCADE (deletes the child aswell)
  • SET NULL (sets the reference field to null)
  • SET DEFAULT (sets the reference field to the default value)

An example would be:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON DELETE CASCADE -- replace CASCADE with your choice
) ENGINE=INNODB;

(for this example and more details look here: http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html )

If you now want to modify your constraint, you first have to drop it, and create a new one like for example:

ALTER TABLE child 
ADD CONSTRAINT fk_name 
FOREIGN KEY (parent_id) 
REFERENCES parent(id) 
ON DELETE CASCADE; -- replace CASCADE with your choice

I hope this helped. Also to mention it, you should think about maybe not really deleting your parent, and instead creating another boolean column "deleted", which you fill with "yes" if someone clicks the delete. In the "Select"-query you filter then by that "deleted" column. The advantage is, that you do not lose the history of this entry.

Upvotes: 2

Related Questions