CODEWITHSUNDEEP
mysqlreferenceinsertforeign-keys
user1781482
user1781482

Reputation: 633

MySQL insert data into table with foreign keys that are no the primary key of the reference table

I have a table song that contains the artistname and genre as a foreign key, but they are not the primary keys in the artist and genre tables. I am hoping to enter the data into the song table and auto populate in the other two tables, unless my logic is wrong. So this is the song table:

 CREATE  TABLE IF NOT EXISTS `project`.`song` (
`idsong` INT(11) NOT NULL AUTO_INCREMENT ,
`artistname` VARCHAR(45) NULL DEFAULT NULL ,
`title` VARCHAR(45) NULL DEFAULT NULL ,
`genre` VARCHAR(15) NULL DEFAULT NULL ,
`year` INT(4) NULL DEFAULT NULL ,
`lyrics` TEXT NULL DEFAULT NULL ,
`url` VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY KEY (`idsong`) ,
INDEX `title` (`title` ASC) ,
INDEX `genre_idx` (`genre` ASC) ,
INDEX `artistname_idx` (`artistname` ASC) ,
CONSTRAINT `genre`
FOREIGN KEY (`genre` )
REFERENCES `project`.`genres` (`genre` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `artistname`
FOREIGN KEY (`artistname` )
REFERENCES `project`.`artist` (`artistname` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB

and the other two tables:

CREATE  TABLE IF NOT EXISTS `project`.`genres` (
`genre` VARCHAR(15) NOT NULL ,
 PRIMARY KEY (`genre`) )
 ENGINE = InnoDB

 CREATE  TABLE IF NOT EXISTS `project`.`artist` (
`idartist` INT(11) NOT NULL AUTO_INCREMENT ,
`artistname` VARCHAR(45) NULL DEFAULT NULL ,
`bio` TEXT NULL DEFAULT NULL ,
PRIMARY KEY (`idartist`) ,
INDEX `artistname` (`artistname` ASC) )
ENGINE = InnoDB

This is my query that is not working:

INSERT INTO song (artist, title, genre, year, lyrics, url)
    VALUES
    ('Jason Aldean', 'Night Train', 'Country', '2012', 'This is the song lyrics', '9ADpkg1dkDU');

Any ideas what's wrong? I'm a newbie so i'm sure it is the user :)

Upvotes: 2

Views: 4098

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65274

This will not work the way you think: A foreign key constraint can do ON DELETE CASCADE and ON UPDATE CASCADE, but it can not do something like ON INSERT CASCADE.

What you need is a BEFORE INSERT trigger on the song table, that will populate the referenced tables if necessary.

Upvotes: 1

Related Questions