ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

Question

I have created two tables in MySQL 5.6.11 as shown below by means of MySQL Workbench 5.2.47.

The country table:

delimiter $$

CREATE TABLE `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INC

REMENT=2 DEFAULT CHARSET=utf8$$

The state_table:

delimiter $$

CREATE TABLE `state_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_name` varchar(45) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `country_fk` FOREIGN KEY (`id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=''$$

There is one row in the country table with the id 1. It allows only one (child) row to be inserted into its child table state_table. If more rows are attempted, then the following error occurs.

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (social_networking.state_table, CONSTRAINT country_fk FOREIGN KEY (id) REFERENCES country (id) ON DELETE CASCADE ON UPDATE CASCADE)

SQL Statement:

INSERT INTO `social_networking`.`state_table` (`id`, `state_name`, `country_id`) VALUES ('2', 'xxx', '1')

Actually, I'm trying to map these tables using an ORM (JPA) where I always see only OneToOne relationship.

What am I missing?

Answer 1

I think you have a typo in your foreign key constraint, country_id should probaby be the foreign key to country. When id is the foreign key, you can only insert one row since it just happens to get id=1 which is the same id as the row in country;

CONSTRAINT `country_fk` FOREIGN KEY (`id`) 
    REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

should probably be

CONSTRAINT `country_fk` FOREIGN KEY (`country_id`) 
    REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

An SQLfiddle to test with.