Ошибка 1451 mysql

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` 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`);

Источник

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` 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`);

Источник

Summary: MySQL Error 1451 is a simple issue to fix. This error doesn’t require much technical knowledge to sort out this issue. But you must know some key concepts by which you will be able to fix the error code 1451 MySQL message. However, we are going to explain the best and verified solutions to resolve this issue. Along with this, we have the MySQL Database Recovery tool which is an automatic method that recovers all the SQL files and can resolve any error. Additionally, we discuss some reasons for which this error occurs. So, stay with this blog till last to know more. Download Now   Purchase Now

What is MySQL Error 1451 Code?

MySQL is an open-source relational database software developed to manage data in different forms. It also allows various operations on the data, like linking an entry of one table to another table. The interlinking of this kind is carried out using a foreign key. The different tables are used here as per their applicability. There is a child table in which the foreign key is present that carries out the interlinking. Besides, there is also a parent table that gives all the references. The error 1451 arises in my SQL if any task executed on an entry is utilized as a reference for some other table. The error can also occur in the form of a foreign key constraint error.

Reasons for Error Code 1451 MySQL

MySQL Error 1451 arises due to several reasons. The most common reasons for the error are discussed below.

  • One of the main causes of this error is the deletion of an entry from the parent table. In doing so, the primary key gets linked to the foreign key of the child table. If you delete it directly, the error pops up.
  • The difference in the data type of both the parent and child tables creates this error. It should be the same for both tables. For example, if the data type of the primary table is .int, then the foreign key in the child table should also be .int.
  • The data is stored in different ways in MySQL. The character set or collations is the way the data is stored. The error code 1451 MySQL is generated if the collation of both the child and parent tables of the columns is not the same. Thereby, the collations for both should be the same, be it utf-8 or any other.
  • Error Code 1451 MySQL can also pop up if the signing definitions of the two columns are different. The unsigned option under the details of both keys should be the same to avoid this error.

Solution to Fix Error Code 1451 MySQL

Cannot delete or update a parent row: A foreign key constraint failure can be generated if the correct sequence of the tasks is not followed while deleting the entries from the parent table. The below error pops up on the screen.
Error 1451 – cannot delete or update a parent row: a foreign key constraint fails
Hence, to prevent this error, the user should try to first delete or drop the foreign key and then, delete the primary key. The below-mentioned methods are described in a stepwise manner to delete the foreign key of the child table and resolve MySQL error 1451.

Method 1

By following this method, you will be able to delete the foreign key of the child table. The steps are mentioned briefly for your execution.

  1. Delete the foreign key from the child table by entering the following command.

mysql> DELETE FROM (Child_table_Name)
Where (Row_Name) = (Foreign_key)

  1. Repeat the process as per your required entries in the child table.
  2. Now, type the below entry to check the leftover elements in the Child table.

mysql> select * (Child_table_Name)

  1. After that, remove the Primary key from the Parent table with the help of the below command.

mysql> DELETE FROM (Parent_table_Name)
Where (Row_Name) = (Primary_key)

  1. Check the Parent table through the following command.

mysql> SELECT * (Parent_table_Name)

Method 2

The child table foreign key can also be deleted using the below commands to be entered into MySQL.

  1. Open MySQL and enter the following command.

mysql> ALTER TABLE (Child_table_Name)
DROP FOREIGN KEY (Foreign_Key_Entry)

  1. Enter the same command for every desired foreign key as per your need.
  2. The FOREIGN KEY command is not supported on every MySQL. Therefore, you can use the CONSTRAINT command.
  3. After that, type the below command to delete the primary key from the table.

mysql> DELETE FROM (Parent_table_Name)
Where (Row_Name) = (Primary_key)

Occasionally, the error code 1451 MySQL is not developed due to any of the above-mentioned reasons. It arises due to the corruption present in the files. If the manual methods discussed above do not solve your error or you find them complex and time-consuming, you can go for an alternate solution. The professional method,MySQL Database Recovery, is an automatic method that recovers all the SQL files and can resolve any error. It repairs the damaged files that cause the errors. Also, this MySQL Recovery tool fixes MySQL error 1045 (28000) access denied for users.

Conclusion

Here, we have analyzed the reasons that give rise to the error code 1451 MySQL. The error can be resolved by the manual methods that have been discussed. These manual methods fix ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails. Moreover, these methods are not apt for some users and are not always effective. So, you can directly opt for a professional solution to recover the corrupt database files and it also fixes errors like MySQL error 1451 and MySQL 1146 table does not exist error. Thanks for reading this blog. I hope you found it helpful.

Related Post

Источник

I want to delete a record based on an id. But I can’t since foreign key constraint fails…that means that maybe I need to implement DELETE on Cascade…but I don’t know how that works. Bottom Line I need to fix the error, and/or I need to implement Delete on Cascade since I searched through the site and that is the most possible solution.
This is the query:

SET SQL_SAFE_UPDATES = 0;
DELETE `rentals`, `rental_movie`
FROM `rentals`
LEFT JOIN `rental_movie`
    ON `rentals`.`idRentals` = `rental_movie`.`Rentals_idRentals`
WHERE `Movie_idMovie` = 1;

I have the Error 1451 in MySQL:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`project`.`rental_movie`, CONSTRAINT `fk_Rental_Movie_Rentals1` FOREIGN KEY (`Rentals_idRentals`) REFERENCES `rentals` (`idRentals`) ON DELETE NO ACTION ON UPDATE NO ACTION)

This is my database:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `project` DEFAULT CHARACTER SET utf8 ;
USE `project` ;

-- -----------------------------------------------------
-- Table `project`.`customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`customer` ;

CREATE TABLE IF NOT EXISTS `project`.`customer` (
  `idCustomer` INT(11) NOT NULL AUTO_INCREMENT,
  `CustomerName` VARCHAR(20) NOT NULL,
  `CustomerLastName` VARCHAR(20) NOT NULL,
  `CustomerAddressl` VARCHAR(45) NOT NULL,
  `ZipCode` INT(11) NOT NULL,
  `CustomerPueblo` VARCHAR(20) NOT NULL,
  `CustomerTel` DECIMAL(10,0) NOT NULL,
  PRIMARY KEY (`idCustomer`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `project`.`movie`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`movie` ;

CREATE TABLE IF NOT EXISTS `project`.`movie` (
  `idMovie` INT(11) NOT NULL AUTO_INCREMENT,
  `TitleMovie` VARCHAR(45) NOT NULL,
  `Genre` VARCHAR(45) NOT NULL,
  `ReleaseDate` VARCHAR(45) NOT NULL,
  `RunTime` TIME NOT NULL,
  `Rated` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`idMovie`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `project`.`rentals`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`rentals` ;

CREATE TABLE IF NOT EXISTS `project`.`rentals` (
  `idRentals` INT(11) NOT NULL AUTO_INCREMENT,
  `Customer_idCustomer` INT(11) NOT NULL,
  `RentedDate` DATE NOT NULL,
  `ReturnDate` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`idRentals`),
  INDEX `fk_Rentals_Customer_idx` (`Customer_idCustomer` ASC),
  CONSTRAINT `fk_Rentals_Customer`
    FOREIGN KEY (`Customer_idCustomer`)
    REFERENCES `project`.`customer` (`idCustomer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `project`.`rental_movie`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`rental_movie` ;

CREATE TABLE IF NOT EXISTS `project`.`rental_movie` (
  `Movie_idMovie` INT(11) NOT NULL,
  `Rentals_idRentals` INT(11) NOT NULL,
  PRIMARY KEY (`Movie_idMovie`, `Rentals_idRentals`),
  INDEX `fk_Rental_Movie_Rentals1_idx` (`Rentals_idRentals` ASC),
  CONSTRAINT `fk_Rental_Movie_Movie1`
    FOREIGN KEY (`Movie_idMovie`)
    REFERENCES `project`.`movie` (`idMovie`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Rental_Movie_Rentals1`
    FOREIGN KEY (`Rentals_idRentals`)
    REFERENCES `project`.`rentals` (`idRentals`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `project`.`transaction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`transaction` ;

CREATE TABLE IF NOT EXISTS `project`.`transaction` (
  `idTransaction` INT(11) NOT NULL AUTO_INCREMENT,
  `idRentals` INT(11) NOT NULL,
  `DaysRented` INT(11) NULL DEFAULT NULL,
  `Cost` DECIMAL(10,0) NULL DEFAULT NULL,
  `TotalCost` DECIMAL(10,0) NULL DEFAULT NULL,
  PRIMARY KEY (`idTransaction`),
  INDEX `idRentals_idx` (`idRentals` ASC),
  CONSTRAINT `idRentals`
    FOREIGN KEY (`idRentals`)
    REFERENCES `project`.`rentals` (`idRentals`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;

USE `project` ;

-- -----------------------------------------------------
-- Placeholder table for view `project`.`new_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project`.`new_view` (`idRentals` INT, `Customer_idCustomer` INT);

-- -----------------------------------------------------
-- Placeholder table for view `project`.`rentals_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project`.`rentals_view` (`idRentals` INT, `idCustomer` INT, `CustomerName` INT, `idMovie` INT, `TitleMovie` INT, `RentedDate` INT);

-- -----------------------------------------------------
-- procedure PName
-- -----------------------------------------------------

USE `project`;
DROP procedure IF EXISTS `project`.`PName`;

DELIMITER $$
USE `project`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PName`(cid INT)
BEGIN

  SELECT * FROM rentals;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- View `project`.`new_view`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `project`.`new_view` ;
DROP TABLE IF EXISTS `project`.`new_view`;
USE `project`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `project`.`new_view` AS select `project`.`rentals`.`idRentals` AS `idRentals`,`project`.`rentals`.`Customer_idCustomer` AS `Customer_idCustomer` from `project`.`rentals`;

-- -----------------------------------------------------
-- View `project`.`rentals_view`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `project`.`rentals_view` ;
DROP TABLE IF EXISTS `project`.`rentals_view`;
USE `project`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `project`.`rentals_view` AS select `r`.`idRentals` AS `idRentals`,`c`.`idCustomer` AS `idCustomer`,`c`.`CustomerName` AS `CustomerName`,`m`.`idMovie` AS `idMovie`,`m`.`TitleMovie` AS `TitleMovie`,`r`.`RentedDate` AS `RentedDate` from (((`project`.`rentals` `r` join `project`.`customer` `c` on((`r`.`Customer_idCustomer` = `c`.`idCustomer`))) join `project`.`rental_movie` `rm` on((`rm`.`Rentals_idRentals` = `r`.`idRentals`))) join `project`.`movie` `m` on((`rm`.`Movie_idMovie` = `m`.`idMovie`)));

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Источник

Error code 1451 MySQL is a simple issue to fix. It does not require deep technical knowledge to resolve this issue. However, if you don’t the reasons and some key concepts, you will not be able to fix this error message. So, let’s learn some basics of MySQL.

  Download     Buy Now

MySQL is a relational database software designed to manage data in various forms. You can also perform various operations on this data. One such feature is linking an entry of one table to some other entry of another table. This method is performed using a foreign key.

The two tables used here are the Parent table and child table. The table in which the foreign key is available is known as the child table and the table from which the reference is taken is known as the parent table. If you perform any action on an entry that is used as a reference of some other table, you will face a foreign key constraint error or MySQL error 1451,

You will learn how to delete any entry so that this issue will never arise. But before, you need to know some other reasons for MySQL 1451 error.

Why does this error arise?

You can fix the error code 1451 MySQL easily. This error is caused by various reasons but some reasons are more prominent than others. They appear in most cases. Before jumping to the methods to resolve this issue, you need the know the major reasons for this error.

  • The first and most often reason for this error is when you try to delete an entry from the parent table. The primary key will get linked to the foreign key of the child table. You can not delete it directly. Otherwise, the foreign key constraint error will appear on the screen.
  • The data type of both the parent table and child table are not the same. If the data type of the primary key in the parent table is .int, the foreign key in the child table must have .int data type.
  • Error code 1451 MySQL also arises when the collation of columns of both parent and child tables are different. Collation or character set means how the data is sorted. So, if you select utf-8 for the primary key, the collation of the foreign key must also be utf-8.
  • If the signing definition of these two columns is different, the foreign key constraint error occurs in MySQL. When you open the details of the primary and foreign key you will see an option of unsigned. It must be the same for both keys.

Also Read: How to Fix Outlook Error 0x800ccc0d?

Methods to Fix Error code 1451 MySQL

The error code 1451 MySQL or foreign key error arises because you are not following the correct order. If you are trying to delete an entry from the parent table which is used as a foreign key in some other table, you will see the following text on the screen.

#ErrorCode 1451 – can’t delete or update a parent row: a foreign key constraint fails

So, you should first delete or drop the foreign key. After that, you can delete the primary key.

Following are the methods to delete the foreign key of the child table:

#Method 1

  1. First, delete the foreign key from the child table by using the following command:

mysql> DELETE FROM (Child_table_Name)             Where (Row_Name) = (Foreign_key)

  1. Repeat the process for desired entries in the child table.
  2. After that, Enter the following entry to check the remaining elements in the Child table:

mysql> select * (Child_table_Name)

  1. Delete the Primary key from the Parent table using the following command:

mysql> DELETE FROM (Parent_table_Name)            Where (Row_Name) = (Primary_key)

  1. Check the Parent table by using the following command:

mysql> SELECT * (Parent_table_Name)

# Method 2

  1. Give the following command in MySQL:

mysql> ALTER TABLE (Child_table_Name)            DROP FOREIGN KEY (Foreign_Key_Entry)

  1. Repeat the command for every desired foreign key.
  2. Some MySQL does not support the FOREIGN KEY command, so you can use CONSTRAINT instead.
  3. Give the following command to delete the primary key from the table

mysql> DELETE FROM (Parent_table_Name)            Where (Row_Name) = (Primary_key)

Sometimes, the error code 1451 MySQL does not arise due to any of the above reasons. It may be due to file corruption. Now, you can repair the MySQL database files using manual methods, but they are difficult and time-consuming. Also, these methods are not completely reliable. So, the best solution to this problem is an automatic method. MysQL Database Recovery Software is a suitable utility that repairs the damaged data files.

Conclusion

The error code 1451 MySQL appears a tough error but it can be resolved easily. It generally arises when you want to delete an entry from the parent table which is a reference for another table. There are also some other reasons. So, the best way to solve the issue is to delete the foreign key from the child table first. Then, you can perform any operation of the primary key of the parent table. Moreover, this professional tool also allows to Fix SQL Server Error 15105 with ease.

Related Post

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 1450 форд эксплорер 5
  • Ошибка 1450 при запуске unturned
  • Ошибка 143 вольво икс си 90
  • Ошибка 1450 недостаточно системных ресурсов для завершения операции
  • Ошибка 145 опель астра j