Error 1452 Sqlstate 23000 Er_no_referenced_row_2
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site error 1452: cannot add or update a child row: a foreign key constraint fails About Us Learn more about Stack Overflow the company Business Learn more about mysql error 1452 foreign key constraint fails hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss error code 1452 mysql workbench Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Mysql error 1452
A Foreign Key Constraint Fails Mysql Insert
- Cannot add or update a child row: a foreign key constraint fails up vote 179 down vote favorite 53 I'm having a bit of a strange problem, I'm trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there cannot add or update a child row a foreign key constraint fails on delete cascade on update cascade) is a foreign key on a different table referencing the one I am trying to reference. Here is a picture of my table relationships, generated via phpMyAdmin: Relationships I've done a SHOW CREATE TABLE query on both tables, sourcecodes_tags is the table with the foreign key, sourcecodes is the referenced table. CREATE TABLE `sourcecodes` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `language_id` int(11) unsigned NOT NULL, `category_id` int(11) unsigned NOT NULL, `title` varchar(40) CHARACTER SET utf8 NOT NULL, `description` text CHARACTER SET utf8 NOT NULL, `views` int(11) unsigned NOT NULL, `downloads` int(11) unsigned NOT NULL, `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `language_id` (`language_id`), KEY `category_id` (`category_id`), CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 CREATE TABLE `sourcecodes_tags` ( `sourcecode_id` int(11) unsigned NOT NULL, `tag_id` int(11) unsigned NOT NULL, KEY `sourcecode_id` (`sourcecode_id`), KEY `tag_id` (`tag_id`), CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`)
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of mysql replication error 1452 this site About Us Learn more about Stack Overflow the company Business
Set Foreign_key_checks=0 Mysql
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask
Mysql Foreign Key Constraint Fails
Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa up Yii on adding new record error : 1452 Cannot add or update a child row: a foreign key constraint fails up vote 0 down vote favorite Adding new record it gives an error: 1452 Cannot add or update a child row: a foreign key constraint fails public function relations() { return array( 'data' => array(self::HAS_ONE, 'Data', 'id'), ); } Here is my http://stackoverflow.com/questions/12427789/yii-on-adding-new-record-error-1452-cannot-add-or-update-a-child-row-a-foreig code for adding a new record: public function actionAdd_Record() { $users = new Users(); $data = new Data(); if (isset($_POST['Users']) && isset($_POST['Data'])) { if(!empty($_POST['Users_password'])) $_POST['Users']['password']=md5($_POST['Users_password']); $users->created_date=date('Y-m-d H:i:s'); CActiveForm::validate(array($users, $data)); $users->attributes = $_POST['Users']; $data->attributes = $_POST['Data']; $valid=$users->validate(); $valid=$data->validate() && $valid; if($valid){ $users->save(); $data->save(); $this->redirect( array('view_record', 'id'=> $users->id) ); } } $this->render( 'add_record', array( 'users'=> $users, 'data'=>$data ) ); } Here is the first table: CREATE TABLE IF NOT EXISTS `data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `investment_amount` float DEFAULT '0' COMMENT '投資額', `withdrawals` float DEFAULT '0' COMMENT '引出額', `investment_yield` float DEFAULT '0' COMMENT '運用利回り', `account_balance` float DEFAULT '0' COMMENT '口座残高', `status_account` enum('open','closed') DEFAULT 'open', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; and the second table: CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(128) NOT NULL, `password` varchar(128) NOT NULL, `name` varchar(300) NOT NULL COMMENT '氏名', `email` varchar(200) NOT NULL, `user_type` enum('normal','admin') NOT NULL DEFAULT 'normal', `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; and it gives this error: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint
me: None Category:MySQL Server: Errors Severity:S4 (Feature request) Version:all OS:Any (all) Assigned to: Triage: Triaged: D5 (Feature request) View Add Comment Files Developer Edit Submission https://bugs.mysql.com/bug.php?id=21403 View Progress Log Contributions [1 Aug 2006 19:21] Matt Lord Description: Right now http://www.sqlserverf1.com/tag/error-1452-sqlstate-23000-er_no_referenced_row_2/ we group all 23nnn codes as 23000 when we could be more specific by using subcodes. Here's an example list: Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s' Error: 1048 SQLSTATE: 23000 (ER_BAD_NULL_ERROR) Message: Column '%s' cannot be null Error: 1052 SQLSTATE: 23000 (ER_NON_UNIQ_ERROR) foreign key Message: Column '%s' in %s is ambiguous Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry '%s' for key %d Error: 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE) Message: Can't write, because of unique constraint, to table '%s' Error: 1216 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW) Message: Cannot add or update a child row: a foreign key constraint fails Error: 1217 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED) Message: Cannot delete or foreign key constraint update a parent row: a foreign key constraint fails Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2) Message: Cannot delete or update a parent row: a foreign key constraint fails (%s) Error: 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2) Message: Cannot add or update a child row: a foreign key constraint fails (%s) How to repeat: n/a Suggested fix: We could change the SQLSTATE values in class 23. A common list (possibly ultimately from X/Open) is: 23000 INTEGRITY CONSTRAINT VIOLATION 23001 RESTRICT VIOLATION 23502 NOT NULL VIOLATION 23503 FOREIGN KEY VIOLATION 23505 UNIQUE VIOLATION 23514 CHECK VIOLATION [3 Aug 2006 14:56] Matt Lord I just wanted to note that class 23nnn was used as an example. It would be great if we could apply this logic to all the various SQL state classes. [10 Oct 2006 16:20] Alexandre Lima what I can change the sqlstate? [3 May 2010 11:37] Chris Wilson Error: 1052 SQLSTATE: 23000 (ER_NON_UNIQ_ERROR) Message: Column '%s' in %s is ambiguous This is an error in the query. It has nothing to do with a constraint violation of any kind below the SQL syntax level. It c
Tag: Error: 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2) MYSQL Error Messages and Failure Codes from Error 1451 to 1460 February 6, 2015 SQLServerF1 Leave a comment Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2) Message: Cannot delete or update a parent row: a foreign key constraint fails (%s) Error: 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2) Message: Cannot add or update a child row: a foreign key constraint fails (%s) Error: 1453 SQLSTATE: 42000 (ER_SP_BAD_VAR_SHADOW) Message: Variable ‘%s' must be quoted with `…`, or renamed Error: 1454 SQLSTATE: HY000 (ER_TRG_NO_DEFINER) Message: No definer attribute for trigger ‘%s'.'%s'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. Error: 1455 SQLSTATE: HY000 (ER_OLD_FILE_FORMAT) Message: ‘%s' has an old format, you should re-create the ‘%s' object(s) Error: 1456 SQLSTATE: HY000 (ER_SP_RECURSION_LIMIT) Message: Recursive limit %d (as set by the max_sp_recursion_depth variable) was exceeded for routine %s Error: 1457 SQLSTATE: HY000 (ER_SP_PROC_TABLE_CORRUPT) Message: Failed to load routine %s. The table mysql.proc is missing, corrupt, or contains bad data (internal code %d) Error: 1458 SQLSTATE: 42000 (ER_SP_WRONG_NAME) Message: Incorrect routine name ‘%s' Error: 1459 SQLSTATE: HY000 (ER_TABLE_NEEDS_UPGRADE) Message: Table upgrade required. Please do "REPAIR TABLE `%s`" or dump/reload to fix it! Error: 1460 SQLSTATE: 42000 (ER_SP_NO_AGGREGATE) Message: AGGREGATE is not supported for stored functions Above are MYSQL Error Codes from Error 1451 to 1460 received while performing certain operation against MYSQL Database or related products. What are MYSQL Errors? MySQL programs have access to several types of error informa