Why 'Delete from table' ignoring foreign key constraints?

Question

I have tables: authors and rewards (one to many relations). And I would like make next rules:

reward has link to author_id;
restrict deletion reward if it has author_id foreign key;
when I delete the author, then I want to cascade deletion rows from rewards table for this author_id.

I have used

constraint foreign key (author_id) 
references authors (author_id)
on delete cascade on update cascade

but anyway I can delete row or all rows from rewards. How can I make deletion from rewards only when I delete author?

Created tables:

create table authors (
            author_id bigint not null auto_increment,
            birth_date datetime,
            first_name varchar(255),
            last_name varchar(255),
            sex varchar(10),
            primary key (author_id)
        );

create table rewards (
                reward_id bigint not null auto_increment,
                title varchar(255),
                year integer not null,
                author_id bigint not null,
                primary key (reward_id),
                constraint foreign key (author_id) references authors (author_id)
                on delete cascade on update cascade
                );

Then insert values:

    Insert into authors(
birth_date, first_name, last_name, sex) values
('1941-05-24', 'Bob', 'Dylan', 'male'),
('1870-10-22', 'Ivan', 'Bunin', 'male');

Insert into rewards(year, title, author_id) values 
(2016, 'Nobel Prize for Literature', 1),
(1933, 'Nobel Prize for Literature', 2),
(1903, 'Pushkin Prize', 2)
;

"restrict deletion reward if it has author_id foreign key". Since author_id is defined as NOT NULL this would mean that you cannot delete any reward at all unless you delete the whole author. Is that right? — Progman, Aug 06 '17 at 11:31
I don't understand the question. You say: "How can I avoid deletion from rewards?" But right above that you specify that you want cascading deletes. — Gordon Linoff, Aug 06 '17 at 11:39
@GordonLinoff yes, I mean I want restrict `delete from rewards where..`. And allow cascade deletion from authors — Nikolas, Aug 06 '17 at 11:46
@Nikolas Why do you want to prevent delete of rewards? Like, you added a reward by mistake and then you are in a spot "Ah crap... okay... apparently he does have a nobel prize now, nothing I can do about that..." — Progman, Aug 06 '17 at 11:55
@Progman hmm...I agree with you. I thinking about tables architecture and want to choose correct decision. I have two more tables: books and author_books for many to many relations between authors and books. So I decided to make some rules that my future system will be consistent. I agree that I should have rights to delete rows added by mistake. Maybe there are best practices for restrictions for many to many relations? — Nikolas, Aug 06 '17 at 12:12
For example I would like to: 1) add book and then add record to authors_books (because, there are no books without author) 2) when delete author, then cascade delete records from author_books 3) delete author only when there are no records in authors_books — Nikolas, Aug 06 '17 at 12:14

score 0 · Answer 1 · answered Aug 06 '17 at 12:31

Easy enough with a trigger, but you would need an override option perhaps based on another table.

drop trigger if exists delete_rewards;
delimiter $$

CREATE DEFINER=`root`@`localhost` TRIGGER `delete_rewards` BEFORE delete ON `rewards` 
FOR EACH ROW 
begin
    declare msg varchar(100);
    declare found int;
    set found = 0;
    select count(*) into found from authors a where a.author_id = old.author_id;

    if found > 0 then
        set msg = 'Delete Not allowed';
        signal sqlstate '45000' set message_text = msg;
    end if;


END $$

delimiter ;

aschoerk · Answer 2 · 2017-08-06T12:44:22.297

As you formulated the constraint, you cannot delete an author without deleting all rewards. But you can delete rewards as you want. This is a typical one to many relationship where one is author and many is rewards. That makes sense, because you don't want to delete the one as long as any of the many are referring to it. The cascading will work.

If you want to prevent manipulation of the reward-records, as soon as they are assigned, I recommend triggers and possibly history tables.

If you want one reward and many authors than you must reverse the relationship. So the authors have to refer to their reward. If it is many to many you will need an extra table to handle this. But seeing your last condition, I suspect that is both not the case.

Why 'Delete from table' ignoring foreign key constraints?

2 Answers2