Mysql Sql Error 1451 Sqlstate 23000
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 cannot delete or update a parent row a foreign key constraint fails mysql of this site About Us Learn more about Stack Overflow the company sql cannot delete row not unique Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges
Cascade Delete Mysql
Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute:
The Row Values Updated Or Deleted Either Do Not Make
Sign up Can not delete a row in the table (MySQL) up vote 0 down vote favorite Dear all, I have a problem in MySQL: I failed to execute DELETE FROM users where user_id ='1'; in the MySQL CLI. So I tried in the phpMyAdmin: Use the GUI to delete a row, I got this: SQL query: DELETE FROM `health_portal`.`users` WHERE `users`.`user_id` delete from sql =1 MySQL said: Documentation Cannot delete or update a parent row: a foreign key constraint fails (`health_portal`.`users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`user_id`)) I looked up this error in the Mysql website and got: Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2) Message: Cannot delete or update a parent row: a foreign key constraint fails (%s) I don't know what is wrong here, hope anyone can give me a heads-up. Many thx! sql mysql mysql-error-1451 share|improve this question edited Dec 22 '10 at 2:30 OMG Ponies 199k37360417 asked Dec 17 '10 at 17:40 manxing 73232043 add a comment| 6 Answers 6 active oldest votes up vote 4 down vote accepted You have a doctor that references this user through a foreign key with on delete no action or on delete restrict. Either change the foreign key to on delete set null (which sets the referencing doctor's user_id to null) or on delete cascade (which deletes the doctor along with the user), or manually assign the doctor to a different user, then re-run your query. share|improve this answer answered Dec 17 '10 at 17:46 tdammers 16.1k24
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 About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Cannot delete or update a parent http://stackoverflow.com/questions/4473265/can-not-delete-a-row-in-the-table-mysql row: a foreign key constraint fails up vote 63 down vote favorite 27 When doing: DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1 It errors: #1451 - Cannot delete or update a parent row: a foreign key constraint fails (paymesomething.advertisers, CONSTRAINT advertisers_ibfk_1 FOREIGN KEY (advertiser_id) REFERENCES jobs (advertiser_id)) Here are my tables: CREATE TABLE IF NOT EXISTS `advertisers` ( `advertiser_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` http://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails varchar(255) NOT NULL, `password` char(32) NOT NULL, `email` varchar(128) NOT NULL, `address` varchar(255) NOT NULL, `phone` varchar(255) NOT NULL, `fax` varchar(255) NOT NULL, `session_token` char(30) NOT NULL, PRIMARY KEY (`advertiser_id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; INSERT INTO `advertisers` (`advertiser_id`, `name`, `password`, `email`, `address`, `phone`, `fax`, `session_token`) VALUES (1, 'TEST COMPANY', '', '', '', '', '', ''); CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `advertiser_id` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, `shortdesc` varchar(255) NOT NULL, `longdesc` text NOT NULL, `address` varchar(255) NOT NULL, `time_added` int(11) NOT NULL, `active` tinyint(1) NOT NULL, `moderated` tinyint(1) NOT NULL, PRIMARY KEY (`job_id`), KEY `advertiser_id` (`advertiser_id`,`active`,`moderated`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; INSERT INTO `jobs` (`job_id`, `advertiser_id`, `name`, `shortdesc`, `longdesc`, `address`, `active`, `moderated`) VALUES (1, 1, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0); ALTER TABLE `advertisers` ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`); sql mysql mysql-error-1451 share|improve this question edited Jan 18 '14 at 19:13 Eric Leschinski 47k23221190 asked Dec 15 '09 at 6:09 steven 2,523102837 add a comment| 10 Answers 10 active oldest votes up vote 41 down vote accepted As is, you must delete the row
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as http://dev.mysql.com/doc/en/error-messages-server.html a Document Store Tutorial MySQL Programs MySQL Server Administration Security Backup and https://coderanch.com/t/550180/ORM/databases/Simple-Crazy-Cascade Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL Cluster NDB 7.5 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables MySQL Performance Schema MySQL sys Schema Connectors and APIs Extending MySQL cannot delete MySQL Enterprise Edition MySQL Workbench MySQL 5.7 Frequently Asked Questions Errors, Error Codes, and Common Problems Sources of Error Information Types of Error Values Server Error Codes and Messages Client Error Codes and Messages Problems and Common Errors How to Determine What Is Causing a Problem Common Errors When Using MySQL Programs Access denied Can't connect to [local] mysql sql error MySQL server Lost connection to MySQL server Client does not support authentication protocol Password Fails When Entered Interactively Host 'host_name' is blocked Too many connections Out of memory MySQL server has gone away Packet Too Large Communication Errors and Aborted Connections The table is full Can't create/write to file Commands out of sync Ignoring user Table 'tbl_name' doesn't exist Can't initialize character set File Not Found and Similar Errors Table-Corruption Issues Administration-Related Issues Problems with File Permissions How to Reset the Root Password What to Do If MySQL Keeps Crashing How MySQL Handles a Full Disk Where MySQL Stores Temporary Files How to Protect or Change the MySQL Unix Socket File Time Zone Problems Query-Related Issues Case Sensitivity in String Searches Problems Using DATE Columns Problems with NULL Values Problems with Column Aliases Rollback Failure for Nontransactional Tables Deleting Rows from Related Tables Solving Problems with No Matching Rows Problems with Floating-Point Values Optimizer-Related Issues Table Definition-Related Issues Problems with ALTER TABLE TEMPORARY Table Problems Known Issues in MySQL Restrictions and Limits Indexes MySQL
details. Win a copy of Penetration Testing Basics this week in the Security forum! Post Reply Bookmark Topic Watch Topic New Topic programming forums Java Java JSRs Mobile Certification Databases Caching Books Engineering Languages Frameworks Products This Site Careers Other all forums Forum: Object Relational Mapping Simple But Crazy Cascade Problem Gokcen Guner Greenhorn Posts: 9 posted 5 years ago Although I'm using javax.persistence.CascadeType.ALL, org.hibernate.annotations.CascadeType.ALL, and OnDeleteAction.CASCADE I couldn't manage to delete parent row. My code is like that: @Entity @Inheritance(strategy = InheritanceType.JOINED) public class Person { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int personId; //other fields getters setters } @Entity public class Patient extends Person { // bi-directional many-to-one association to Doctor @ManyToOne(optional = false, targetEntity=Doctor.class) @JoinColumn(name = "doctorId", nullable = false) private Doctor doctor; //getter/setters, no other fields, just doctor } @Entity public class Doctor extends Person { // bi-directional many-to-one association to Patient @OneToMany(mappedBy = "doctor", orphanRemoval = true, cascade = { javax.persistence.CascadeType.ALL }) @Cascade({ CascadeType.ALL }) @ForeignKey(name = "FK_DOCTOR") @OnDelete(action = OnDeleteAction.CASCADE) private List