mysql - Records are not getting deleted from the related tables- ON DELETE CASCADE does not work

Here are my three tables

    CREATE TABLE IF NOT EXISTS Song (
        song_id INT(10) NOT NULL,
        song_name VARCHAR(255) NOT NULL,
        lyrics TEXT,
        genre_id INT(10) NOT NULL,
        year_released INT(10),
        FOREIGN KEY gfk_id(genre_id)
        REFERENCES Genre(genre_id) ON DELETE CASCADE
        ON UPDATE CASCADE,
        PRIMARY KEY (song_id)
    );

    CREATE TABLE IF NOT EXISTS Artist (
        artist_id VARCHAR(25) NOT NULL,
        artist_fname VARCHAR(10),
        artist_lname VARCHAR(10),
        PRIMARY KEY (artist_id)
    );


    create table Song_Artist
    (
       song_id int(10) NOT NULL,
       artist_id varchar(25) NOT NULL,
       FOREIGN KEY fk_song(song_id)
       REFERENCES Song(song_id)  ON DELETE CASCADE
       ON UPDATE CASCADE,
       FOREIGN KEY fk_artist(artist_id)
       REFERENCES Artist(artist_id)  ON DELETE CASCADE
       ON UPDATE CASCADE
    );

Song and Artist tables have many to many relationship and I have used cross reference approach to join those two tables using a separate table called Song_Artist

My doubt is, If I delete a record from the song table then does it mean the corresponding record pertaining to deleted song(song_id) should also be removed from the Song_Artist table ?? Or should I set the value of song_id after deleting to Null in the Song_Artist Table.

I tried using this trigger

DELIMITER $$
CREATE TRIGGER song_id_delete BEFORE DELETE ON Song
FOR EACH ROW
BEGIN
DELETE FROM Song_Artist 
WHERE song_id = OLD.song_id;
END 
DELIMITER ;

MySql workbench does not show that this trigger is created. And there are no errors shown.

1 Answer

  1. Brandon- Reply

    2019-11-15

    Seems to work for me; I set default for genre_ID to 0 and removed FK relationship since I didn't have a genre table. But you can see the deletes and updates work. artist 1 and song 1 are updated to 10 and artist 5 and song 4 deleted.

    I personally think Song_artist should get a composite primary key; but your call.

    Is the issue you don't have a genre table created before the song table is created? thus it can't create the FK reference? I doubted it as you said the deletes were not working; not that you encountered a table create error...

    demo on 5.7.12 Not sure how to debug the error when I can't recreate it.

        CREATE TABLE IF NOT EXISTS Song (
        song_id INT(10) NOT NULL,
        song_name VARCHAR(255) NOT NULL,
        lyrics TEXT,
        genre_id INT(10) NOT NULL default 0,
        year_released INT(10),
        PRIMARY KEY (song_id)
    );
    
    CREATE TABLE IF NOT EXISTS Artist (
        artist_id VARCHAR(25) NOT NULL,
        artist_fname VARCHAR(10),
        artist_lname VARCHAR(10),
        PRIMARY KEY (artist_id)
    );
    
    
    create table Song_Artist
    (
       song_id int(10) NOT NULL,
       artist_id varchar(25) NOT NULL,
       #PRIMARY KEY PK_song_artist (song_ID, artist_id), 
       FOREIGN KEY FK_Song(song_id) 
         REFERENCES Song(song_id) 
         ON DELETE CASCADE 
         ON UPDATE CASCADE,
       FOREIGN KEY FK_Artist(artist_id) 
         REFERENCES Artist(artist_id) 
         ON DELETE CASCADE 
         ON UPDATE CASCADE
        );
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>