Search code examples
mysqlinnodbdelete-row

Delete a row from the table

I created a schema in mysql InnoDB with the help of the following .sql:

CREATE DATABASE IF NOT EXISTS sailors;
USE sailors;
DROP TABLE IF EXISTS reserves;
DROP TABLE IF EXISTS sailors;
DROP TABLE IF EXISTS boats;

CREATE TABLE sailors
(
sid    INTEGER NOT NULL ,
sname  VARCHAR(20) NOT NULL ,
rating INTEGER NULL ,
age    DECIMAL(5,2) NULL, 
CONSTRAINT sailors_pk PRIMARY KEY (sid)
);

CREATE TABLE boats
(
bid    INTEGER NOT NULL ,
bname  VARCHAR(20) NOT NULL ,
color  VARCHAR(10) NOT NULL ,
CONSTRAINT boats_pk PRIMARY KEY (bid)
);

CREATE TABLE reserves
(
sid    INTEGER NOT NULL ,
bid    INTEGER NOT NULL ,
day    DATE NOT NULL ,
CONSTRAINT reserves_pk PRIMARY KEY (sid,bid,day)
, foreign key (sid) references sailors (sid)
, foreign key (bid) references boats  (bid)
);

INSERT INTO sailors VALUES(22, 'Dustin', 7, 45.0);
INSERT INTO sailors VALUES(29, 'Brutus', 1, 33.0);
INSERT INTO sailors VALUES(31, 'Lubber', 8, 55.5);
INSERT INTO sailors VALUES(32, 'Audy',   8, 25.5);
INSERT INTO sailors VALUES(58, 'Rusty', 10, 35.5);
INSERT INTO sailors VALUES(64, 'Horatio',7, 35.0);
INSERT INTO sailors VALUES(71, 'Zorba', 10, 16.0);
INSERT INTO sailors VALUES(74, 'Horatio',9, 35.0);
INSERT INTO sailors VALUES(85, 'Art',    3, 25.5);
INSERT INTO sailors VALUES(95, 'Bob',    3, 63.5);

INSERT INTO boats VALUES(101, 'Interlake', 'blue');
INSERT INTO boats VALUES(102, 'Interlake', 'red');
INSERT INTO boats VALUES(103, 'Clipper',   'green');
INSERT INTO boats VALUES(104, 'Marine',    'red');
INSERT INTO boats VALUES(105, 'Clipper',   'white');

INSERT INTO reserves VALUES(22, 101, DATE '2012-10-10');
INSERT INTO reserves VALUES(22, 102, DATE '2012-10-10');
INSERT INTO reserves VALUES(22, 103, DATE '2012-08-10');
INSERT INTO reserves VALUES(22, 104, DATE '2012-07-10');
INSERT INTO reserves VALUES(31, 102, DATE '2012-10-11');
INSERT INTO reserves VALUES(31, 103, DATE '2012-06-11');
INSERT INTO reserves VALUES(31, 104, DATE '2012-12-11');
INSERT INTO reserves VALUES(64, 101, DATE '2012-05-09');
INSERT INTO reserves VALUES(64, 102, DATE '2012-08-09');
INSERT INTO reserves VALUES(74, 103, DATE '2012-08-09');

Now I want to delete all the information from all of the table about the sailor with the name Lubber. I do the following:

DELETE FROM sailors
WHERE sname = 'Lubber';

and expect him to be deleted, but instead I am getting an error:

00:27:03    DELETE FROM sailors WHERE sname = 'Lubber'  Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`sailors`.`reserves`, CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `sailors` (`sid`))    0.016 sec.

So, then I try another approach I know, but I fail here as well. Here is the approach and its failure.

DELETE sailors, reserves
FROM sailors 
INNER JOIN reserves ON (sailors.sid = reserves.sid)
WHERE sname = 'Lubber';

00:28:22    DELETE sailors, reserves FROM sailors  INNER JOIN reserves ON (sailors.sid = reserves.sid) WHERE sname = 'Lubber'   Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`sailors`.`reserves`, CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `sailors` (`sid`))    0.000 sec

I am really stuck and do not know what else can I try. Will appreciate any help, thank you for the attention.

Solution

  • Your seller table has relationship with reserve table . Normally innodb will throw the error when you try to delete the parent record to avoid the orphan data. Following are some alternatives

    1. Delete the child record 1st using application logic

    2. Use cascading to delete all child records when parent is deleted.(can be dangerous eg: you might lose all reserves when you delete sailers)

    3. Use soft deletion(set flag like is_deleted to Yes or No)(highly recommended)

    you can do the following for cascading which will delete all child records when parent is deleted

    CREATE TABLE reserves
    (
    sid    INTEGER NOT NULL ,
    bid    INTEGER NOT NULL ,
    day    DATE NOT NULL ,
    CONSTRAINT reserves_pk PRIMARY KEY (sid,bid,day)
    , foreign key (sid) references sailors (sid) ON DELETE CASCADE 
    , foreign key (bid) references boats  (bid) ON DELETE CASCADE 
    )