CODEWITHSUNDEEP
mysqlsql
Kishore Indraganti
Kishore Indraganti

Reputation: 1322

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

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

Upvotes: 2

Views: 3397

Answers (2)

Mahdi Rostami
Mahdi Rostami

Reputation: 305

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.

Upvotes: 0

Rudra21
Rudra21

Reputation: 342

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 .

Upvotes: 1

Related Questions