A foreign key constraint fails on database

Question

I'm trying to make a relational database where most of the values change, but I need a few tables that have static content. The static content will need to be stored for each individual entry in the main table.

List of values that change: 3 sets of numeric values name value

List of static content. beats per minute song arrangement type of song

Right now i've got a database set up where i have a separate table for each type of value and a "main" table that holds values of the ID for that particular song.

For beats per minute I basically wanted an incremented list from 80-250 going up by 5's, but when i try to add these to the table it tells me that it can't add an additional one. I think it's because I don't have that many rows in my main table.

Error I receive:

mysql> insert into c_tempo (tempo) values (110);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`production`.`c_tempo`, CONSTRAINT `c_tempo_ibfk_1` FOREIGN KEY (`id`) REFERENCES `p_main` (`id`))

Any help on how to fix this?

Partial table syntax:

-- Create syntax for TABLE 'c_song'
CREATE TABLE `c_song` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `song_vis` varchar(60) DEFAULT NULL,
  `song_hid` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Create syntax for TABLE 'c_tempo'
CREATE TABLE `c_tempo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tempo` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tempo` (`tempo`),
  CONSTRAINT `c_tempo_ibfk_1` FOREIGN KEY (`id`) REFERENCES `p_main` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Create syntax for TABLE 'p_main'
CREATE TABLE `p_main` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `song` int(11) unsigned DEFAULT NULL,
  `tempo` int(11) unsigned DEFAULT NULL,
  `signature` int(11) unsigned DEFAULT NULL,
  `arrangement` int(11) unsigned DEFAULT NULL,
  `cue` int(11) unsigned DEFAULT NULL,
  `colors` int(11) unsigned DEFAULT NULL,
  `description` int(11) unsigned DEFAULT NULL,
  `img` int(11) unsigned DEFAULT NULL,
  `mp3` int(11) unsigned DEFAULT NULL,
  `style` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_song_id` (`song`),
  KEY `fk_signature_id` (`signature`),
  KEY `fk_arrangemtn` (`arrangement`),
  KEY `fk_cue` (`cue`),
  KEY `fk_colors` (`colors`),
  KEY `fk_description` (`description`),
  KEY `fk_img` (`img`),
  KEY `fk_mp3` (`mp3`),
  KEY `fk_style` (`style`),
  CONSTRAINT `fk_arrangemtn` FOREIGN KEY (`arrangement`) REFERENCES `c_arrange` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_colors` FOREIGN KEY (`colors`) REFERENCES `c_colors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cue` FOREIGN KEY (`cue`) REFERENCES `c_cue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_description` FOREIGN KEY (`description`) REFERENCES `c_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_img` FOREIGN KEY (`img`) REFERENCES `c_img` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_mp3` FOREIGN KEY (`mp3`) REFERENCES `c_mp3` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_signature_id` FOREIGN KEY (`signature`) REFERENCES `c_signature` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_song_id` FOREIGN KEY (`song`) REFERENCES `c_song` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_style` FOREIGN KEY (`style`) REFERENCES `c_style` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Welcome to the Treehouse Community

Looking to learn something new?

zach sisk

zach sisk

A foreign key constraint fails on database