Menu
This has me baffled, here's hoping someone can help.
Query:
insert into `shelter_pages` (`userid`,`relid`)
select :userid, `id` from `shelter` where `stage`='egg' order by rand() limit 30
Simple, right? Take 30 random rows meeting a condition, and save them in the "pages" table along with the user id.
The error:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_name`.`shelter_pages`, CONSTRAINT `shelter_pages_ibfk_2` FOREIGN KEY (`relid`) REFERENCES `shelter` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Well how can that be? It's literally taking these `shelter`.`id` values in the SELECT query and INSERTing them, how can the foreign key check possibly fail?
If it's of any significance, the table in question is fairly "busy" - it is part of a gameplay element where any player can "adopt" from the shelter, thus deleting the row. Is this as simple as a race condition in what I thought would be an atomic operation?
309
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB. Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
FIX: A conflict with the foreign key constraint occurs when you update the case of the column values in the primary key table or you pad column values in the primary key table in SQL Server 2005.
MySQL FOREIGN KEY Constraint. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the ...
Notice that setting foreign_key_checks to 1 does not trigger any validation of the existing table data. In other words, MySQL will not verify the consistency of the data that was added during the foreign key check disabled. The table cities has a foreign key constraint that refers to the column country_id of the table countries.
INSERT with SELECT statement for columns with FOREIGN KEY constraint in MySQL with examples. In this blog post, we’ll look at an example of INSERT with SELECT syntax to honor those FOREIGN KEY constraints we have in place that ensure referential integrity between rows in separate tables.
Solution : foreign keys declared in table should not set to be not null. reference : If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL. ( ref ) Show activity on this post. Second right data type give to foreign key ? Show activity on this post.
This is likely happening because you have your transaction isolation level set to "dirty read" (i.e. READ UNCOMMITTED). This means that the SELECT could be reading uncommitted data that would fail the foreign key constraints during the INSERT.
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
