How to delete a row from Parent table without delete a row from Child table?

Question

Am having parent table which has foreign three foreign key but i want to delete row in parent table alone ,how to achieve it ?? The query that am using currently is

DELETE FROM TableName WHERE ColumnName= '89416'

The query show error: Cannot delete or update a parent row: a foreign key constraint fails

Don't do it! You can drop foreign keys for the time being and drop from parent row, but this will create orphan records which is a bad thing. — shree.pat18, Jul 16 '14 at 07:47
Well, that's why it's called Referential Integrity, it's used to prevent such stupid things :-) — dnoeth, Jul 16 '14 at 07:49
If we are deleting parent, Why that should be associated to a child..??? Just break the association then perform deletion if u want... Which is not recommendable anyway.. — Learner, Jul 16 '14 at 07:50
Read http://stackoverflow.com/questions/8982172/delete-primary-key-row-which-is-foreign-key-of-another-table — Sathish, Jul 16 '14 at 08:01

score 1 · Answer 1 · answered Jul 16 '14 at 07:51

You should rework on your requirement, we maintain parent child relation table relation because we need that connected data .

But anyway if you want to delete , then one option is there(Disable Constraints and then enable after delete). But again it will give a problem .

score 0 · Answer 2 · edited May 23 '17 at 10:29

Change your child table by creating it as shown in the sample below:

CREATE TABLE Worker (
 WorkerID smallint auto_increment,
 WorkerType  varchar(45) NOT NULL,
 WorkerName  varchar(45) NOT NULL,
 Position    varchar(45) NOT NULL,
 TaxFileNumber int NOT NULL,
 Address    varchar(100) ,
 Phone      varchar(20) ,
 SupervisorID  smallint ,
 PRIMARY KEY (WorkerID),
 FOREIGN KEY (SupervisorID) REFERENCES Worker(WorkerID)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

You just add ON DELETE SET NULL or NO ACTION. Before use, take a look at this answer on a similar question, that explains the different behaviors.

How to delete a row from Parent table without delete a row from Child table?

2 Answers2