Delete From in delete after trigger not deleting from another table

Question

I am trying to make a DELETE TRIGGER so that when a row is deleted from a table Bike it will delete a row in another table called Available as long as it is not in a different table called in Rental. If it is in Rental table then it will cancel the delete (if that's possible I'm very new to sql).

BEGIN
DELETE FROM Available 
Where old.bnumber = Available.bnumber;
END

right now I am getting

1451: Cannot delete or update a parent row: a foreign key constraint fails (cecs535project.available, CONSTRAINT bnumber FOREIGN KEY (bnumber) REFERENCES bike (bnumber))

bnumber is a foreign key in Available that references Bike.

Any help is appreciated.

CREATE TABLE `Bike` (
  `bnumber` int NOT NULL,
  `make` varchar(64) DEFAULT NULL,
  `color` varchar(8) DEFAULT NULL,
  `year` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`)
)

CREATE TABLE `Available` (
  `bnumber` int NOT NULL,
  `rack-id` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`),
  KEY `bnumber_idx` (`rack-id`),
  KEY `bnumber_idx1` (`bnumber`),
  CONSTRAINT `bnumber` FOREIGN KEY (`bnumber`) REFERENCES `Bike` (`bnumber`),
  CONSTRAINT `rack-id` FOREIGN KEY (`rack-id`) REFERENCES `Rack` (`id`)
) 

CREATE TABLE `Rental` (
  `date` date NOT NULL,
  `time` time NOT NULL,
  `bnumber` int NOT NULL,
  `cust-id` int NOT NULL,
  `src` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`,`cust-id`,`date`,`time`),
  KEY `bnumber_idx` (`bnumber`),
  KEY `cust-id_idx` (`cust-id`),
  KEY `src_idx` (`src`),
  CONSTRAINT `bike` FOREIGN KEY (`bnumber`) REFERENCES `Bike` (`bnumber`),
  CONSTRAINT `cust-id` FOREIGN KEY (`cust-id`) REFERENCES `Customer` (`id`),
  CONSTRAINT `src` FOREIGN KEY (`src`) REFERENCES `Rack` (`id`)
) 

Answer 1

2 possible approaches

1. Use signal to identify where rental exists an throw an error https://dev.mysql.com/doc/refman/8.0/en/signal.html
2. test for rental existence in delete;

In both a before trigger is used since the constraint test occurs before an after trigger fires.

DROP TABLE IF EXISTS AVAILABLE;
drop table if exists rental;
drop table if exists BIKE;

CREATE TABLE `Bike` (
  `bnumber` int NOT NULL,
  `make` varchar(64) DEFAULT NULL,
  `color` varchar(8) DEFAULT NULL,
  `year` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`)
);

insert into bike values
(10,'aaa','red',2020),(20,'bbb','yell',2020);

CREATE TABLE `Available` (
  `bnumber` int NOT NULL,
  `rack-id` int DEFAULT NULL,
  PRIMARY KEY (`bnumber`),
  KEY `bnumber_idx` (`rack-id`),
  KEY `bnumber_idx1` (`bnumber`),
  CONSTRAINT `bnumber` FOREIGN KEY (`bnumber`) REFERENCES `Bike` (`bnumber`)#,
  #CONSTRAINT `rack-id` FOREIGN KEY (`rack-id`) REFERENCES `Rack` (`id`)
) ;

insert into available values
(10,100),(20,200);

CREATE TABLE `Rental` (
  #`date` date NOT NULL,
  #`time` time NOT NULL,
  `bnumber` int NOT NULL,
  #`cust-id` int NOT NULL,
  #`src` int DEFAULT NULL,
  #PRIMARY KEY (`bnumber`,`cust-id`,`date`,`time`),
  KEY `bnumber_idx` (`bnumber`),
  #KEY `cust-id_idx` (`cust-id`),
  #KEY `src_idx` (`src`),
  CONSTRAINT `bike` FOREIGN KEY (`bnumber`) REFERENCES `Bike` (`bnumber`)#,
  #CONSTRAINT `cust-id` FOREIGN KEY (`cust-id`) REFERENCES `Customer` (`id`),
  #CONSTRAINT `src` FOREIGN KEY (`src`) REFERENCES `Rack` (`id`)
) ;

insert into rental values
(10);

Approach 1

drop trigger if exists t;
delimiter $$
create trigger t before delete on bike
for each row 
begin
    if exists (select 1 from rental r where r.bnumber = old.bnumber) then
           SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'An error occurred Rental exists';
    end if;
    delete a from available a where a.bnumber = old.bnumber;

end $$
delimiter ;

delete b  from bike b;
ERROR 1644 (45000): An error occurred Rental exists

+---------+------+-------+------+
| bnumber | make | color | year |
+---------+------+-------+------+
|      10 | aaa  | red   | 2020 |
|      20 | bbb  | yell  | 2020 |
+---------+------+-------+------+
2 rows in set (0.001 sec)

select * from available;
+---------+---------+
| bnumber | rack-id |
+---------+---------+
|      10 |     100 |
|      20 |     200 |
+---------+---------+
2 rows in set (0.001 sec)
select * from rental;
+---------+
| bnumber |
+---------+
|      10 |
+---------+
1 row in set (0.001 sec)

Approach 2

drop trigger if exists t;
delimiter $$
create trigger t before delete on bike
for each row 
begin
    delete a from available a where a.bnumber = old.bnumber;

end $$
delimiter ;

delete b  from bike b  where 
            #bnumber = 10 and 
            not exists(select 1 from rental r where r.bnumber = b.bnumber);
            
select * from bike; 

 +---------+------+-------+------+
| bnumber | make | color | year |
+---------+------+-------+------+
|      10 | aaa  | red   | 2020 |
+---------+------+-------+------+
1 row in set (0.001 sec)

select * from available;
+---------+---------+
| bnumber | rack-id |
+---------+---------+
|      10 |     100 |
+---------+---------+
1 row in set (0.001 sec)

select * from rental;
+---------+
| bnumber |
+---------+
|      10 |
+---------+
1 row in set (0.001 sec)

Answer 2

Creating an AFTER DELETE instead could also help to skip the Foreign Key Constraint issue

CREATE TRIGGER trigger_name
    AFTER DELETE
    ON table_name FOR EACH ROW
trigger_body;

ref: https://www.mysqltutorial.org/mysql-triggers/mysql-after-delete-trigger/

Answer 3

I am not a big fan of your current design, and I think it can be simplified. Consider just having a single table for all bike assets, with one column maintaining whether or not it be currently rented out, e.g.

Bike
id | name  | type     | rented (bit)
1  | bike1 | road     | 1
2  | bike2 | mountain | 0
...

Now to record a bike being rented or not, you simply have to update the rented bit column above. Should you want to delete from your inventory, refraining from doing so if the bike be on loan, you can use:

DELETE
FROM Bike
WHERE rented = 0;  -- AND your other conditions here