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
, CONSTRAINTcountry_fk
FOREIGN KEY (id
) REFERENCEScountry
(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?
Well, I find the answer, the solution, my english is not very well but I think can explain you. I get this error after try to create a trigger, My database was created in phpmyadmin, and this error was make me crazy, the problem was that I before create the trigger, I load a lot of data in my tables, and in my child table was some data that have no match in my parent table, ej: my child table "chat" have a "id_jugador=1" and in my parent table there wasn't that "id_jugador", that was my mistake, I hope help you, Argentina Rulz ;)