I get "a foreign key constraint fails" error when attempting to join two classes/tables

Question

I have these classes, abbreviated for practical reasons:

class CV {
  Date dateCreated
  static hasMany=[proposals: Proposal]
}  

class Proposal {
  String name
  Date date_started
  static hasMany = [CVs: CV]
  static belongsTo = CV
}

Grails creates tables for both these classes, and a third class named "cv_proposals" joining them. So far, so good. I have data in both the CV and the Proposal tables, they both have autoincremented "id" values. All good.

in Oracle MySQL Workbench, I try to manually add values to the joining table to get some dummy data to work with. I get an error message with this trace:

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (cvreg_utv.cv_proposals, CONSTRAINT FK17D946F55677A672 FOREIGN KEY (cv_id) REFERENCES cv (id))

I made sure both the tables had several lines of data in them, and that I could edit both of them separately.

After trying dropping and recreating the table, altering the classes back and forth, I'm kind of convinced that this operation somehow has to be done through a running Grails application. So I write this script in a controller and run it:

def g = CV.get(1)
Proposal proposal = g.addToProposals(new Proposal(
name: "SavingTest", 
date_started: new Date())).save()

I still get the same error, though. Is this not the right way to define a proposal that is connected to a certain CV? Am I wrong in using a many-to-many connection here somehow?

Edit: adding the schema-create script for the joining table

delimiter $$
CREATE TABLE `cv_proposals` (  
  `proposal_id` bigint(20) NOT NULL,  
  `cv_id` bigint(20) NOT NULL,    
  PRIMARY KEY (`cv_id`,`proposal_id`),  
  KEY `FK17D946F55677A672` (`cv_id`),  
  KEY `FK17D946F5F7217832` (`proposal_id`),  
  CONSTRAINT `FK17D946F5F7217832` FOREIGN KEY (`proposal_id`) REFERENCES `proposal` (`id`),  
  CONSTRAINT `FK17D946F55677A672` FOREIGN KEY (`cv_id`) REFERENCES `cv` (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$  

And the CV table:

CREATE TABLE `cv` (  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `version` bigint(20) NOT NULL,  
  `user_id` bigint(20) NOT NULL,  
  `version_name` varchar(255) DEFAULT NULL,  
  `date_created` datetime NOT NULL,  
  `last_updated` datetime NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `FKC734A9AB992` (`user_id`)  
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1$$  

And the Proposal table:

CREATE TABLE `proposal` (  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `version` bigint(20) NOT NULL,  
  `date_ended` datetime NOT NULL,  
  `date_started` datetime NOT NULL,  
  `description` varchar(500) DEFAULT NULL,  
  `name` varchar(500) NOT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1$$  

This is the insert script I tried to run:

INSERT INTO `cvreg_utv`.`cv_proposals` (`proposal_id`, `cv_id`) 
VALUES ('1', '1');

Answer 1

You crated the tables manually? It's interesting that cv table is using MyISAM engine and the others uses InnoDB.

I think you want to use InnoDB to all your tables, since this engine is transactional. In my test, I also was unable to create the cv_proposals table until I changed the cv creation:

 CREATE TABLE cv (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    version bigint(20) NOT NULL,
    user_id bigint(20) NOT NULL,
    version_name varchar(255) DEFAULT NULL,
    date_created datetime NOT NULL,
    last_updated datetime NOT NULL,
    PRIMARY KEY (id),
    KEY FKC734A9AB992 (user_id)
    ) ENGINE=InnoDB AUTO_INCREMENT=101

After that, the insert's worked smoothly.