Ошибка 1451 sql

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

Techniques to Fix Error Code 1451 MySQL

MySQL Error 1451 is an error that can be fixed without any prior technical know-how. It is also sometimes called the foreign key constraint error. You can solve this issue even if you have the basic information about MySQL. However, if you do not make yourself familiar with the concepts and the reasons for the error, you will not be able to fix the error.

MySQL is an open-source relational database software developed to manage the 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.

In this blog, we will guide you to fix Error 1451 in MySQL database Cannot delete or update a parent row: a foreign key constraint fails. But before that, let us go through the reasons that generate the MySQL error code 1451.

Reasons for Error Code 1451 My SQL

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 table 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 the key should be the same to avoid this error.

Cannot delete or update a parent row: a foreign key constraint fails 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.

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 fix 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 fix errors like MySQL error 1451, 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

Cannot delete or update a parent row: a foreign key constraint fails is an error about foreign key constraint violation, but there are other error codes for violation of foreign key constraints. Nevertheless, you need not worry, because we’ve got you covered.

Keep on reading this article, as we’ll show you the different causes of this error, and how you can fix it.

You cannot delete or update a parent row because of the following reasons:

• You are trying to delete a parent row referenced in another table
• You want to add a non-existent foreign key to a child table
• You want to create a foreign key constraint on a non-existent table
• There is an error in your Laravel schema
• You want to drop a table that has references in another table

– Trying To Delete a Parent Row Referenced in Another Table

An attempt to delete a referenced parent row causes a foreign key constraint violation. That’s because the job of a foreign key is to ensure data consistency across linked tables. Though you might turn to a search engine with a search query like cannot delete or update a parent row drop table, but be aware that MySQL or SQL Server will not permit deletion of the referenced table.

As a result, you’ll get MySQL error 1451 (23000) er_row_is_referenced_2. For example, you can have TableA and TableB in a database, but a child row in TableB has a foreign key constraint to a parent row in TableA. If you try to delete the parent row in TableA, you’ll get an error about a foreign key constraint violation.

– Adding a Non-existent Foreign Key to a Child Table

In an attempt to add a non-existence foreign key to a child table, you’ll get the foreign key constraint error. That’s because a foreign key links a parent row and other child rows and any data that tries to violate this link causes a foreign key constraint error. Besides, MySQL or SQL server consider such a behavior a constraint violation.

For example, TableA consists of the names of cities, and TableB contains the names of their Mayors. At the same time, a foreign key constraint exists between the two tables. So, you’ll get a “foreign key constraint fails” message when you try an update on TableB with a false mayor.

– Creating a Foreign Key Constraint on a Non-existent Table

You could have a series of SQL queries that’ll populate a certain database table. Therefore, an SQL query could create a foreign key reference from one child to another parent row. But such a query should not come before the query for the parent row creation. If it does, you’ll get a foreign key constraint violation.

If SQL Server creates the child table, it’ll create the foreign key constraint. At that point, the required table for the foreign key constraint does not exist. So, you get a warning message like #1217 – cannot delete or update a parent row: a foreign key constraint fails. The latter is the same as sql error 1217 sqlstate 23000.

– There Is an Error in Your Laravel Schema

An error in your Laravel schema can cause a foreign key constraint violation, and you’ll get the 1451 cannot delete or update a parent row: a foreign key constraint fails laravel error. Now, the question is: what can cause an error in my Laravel schema? To answer your question, we’ll reproduce the key constraint fails error.

The following is the first schema. We’ll call it sitePosts:

Schema::create(‘sitePosts’, function (Blueprint $tbl) {
$tbl->increments(‘post_id’);
$tbl->string(‘post_title’);
$tbl->text(‘post_body’);
$tbl->integer(‘user_id’)->unsigned();
$tbl->foreign(‘user_id’)->references(‘id’)->on(‘users’);
$tbl->timestamps();
});

The following is the second schema. We’ll call it postLikes:

Schema::create(‘postLikes’, function (Blueprint $tbl) {
$tbl->increments(‘id’);
$tbl->integer(‘post_id’)->unsigned();
$tbl->integer(‘user_id’)->unsigned();
$tbl->foreign(‘post_id’)->references(‘post_id’)->on(‘sitePosts’);
$tbl->foreign(‘user_id’)->references(‘id’)->on(‘users’);
$tbl->softDeletes();
$tbl->timestamps();
});

You could implement a PHP controller that should delete a post with likes but with the current structure of the above schemas, a user will not be able to delete a post with likes. Any attempt to do such will result in a foreign key constraint error. That’s because the foreign key constraint on postLikes.post_id stops deletion in sitePosts.

– You Want To Drop a Table That Has References in Another Table

An attempt to drop TableA with a reference in TableB results in a “foreign key constraint fails” error. That’s because a foreign key constraint allows consistency among linked database tables and an attempt to drop such a table initiates a delete foreign key procedure. So, the database will issue either the 1217 or the 1451 error code.

The latter is peculiar to MariaDB, while the former is for MySQL. For example, TableJ and TableK consist of sample datasets for houses in a community. TableJ contains names of house owners, while TableK consists of their electricity consumption. Meanwhile, in Table K, there is a table row that has a foreign key constraint to a parent row in Table J.

As a result, you cannot delete Table J if there exists a foreign key constraint. An attempt to do so results in a “foreign key constraint fails” error. That’s because you are about to break a relationship that the database won’t allow.

How To Fix Foreign Key Constraint Fails Warning

There are a couple of proven ways to fix a “key constraint fails” issue. It ranges from the right order of table deletion to a right Schema in Laravel. You should also not try to insert a non-existent foreign key into a database table. All these, among others, are what we’ll discuss in detail in this section.

– Delete Data in the Child Table Before Deleting the Parent Row

If you want to drop a parent row that has a reference in a child table, delete the child first. When you do this, you won’t get the “key constraint fails” error when you delete the parent. This saves you time using search queries like MySQL delete row with relations. That’s because you already know what’s going on and how you can fix it.

– Add a Foreign Key to a Parent Row/Table Before Using It in a Child Table

To prevent the “foreign key constraint fails” error, a foreign key should exist in the parent table. For example, TableX can contain rows of data having row IDs and data like May, June, July, and August. Meanwhile, TableY can have rows that are foreign keys to the associated data in TableX. So, do not attempt to add data into TableY that’ll reference a non-existence month in TableX.

– Reorder Your SQL Queries for Creating Tables

If you are creating tables using raw SQL, ensure the SQL queries for parent tables come first. As a result, the parent table gets created before the child tables and a table will exist for the child table as a reference for a foreign key constraint. The foreign key will link without causing a “foreign key constraint fails” error.

– Update Your Laravel Schema

When a foreign key constraint fails error occurs in Laravel, update your schema. Such an update should utilize cascade deletion on linked tables. For example, earlier in the article, we showed you two schemas, but it’s the structure of the postLikes schema causing the error. As a reminder, here it is:

Schema::create(‘postLikes’, function (Blueprint $tbl) {
$tbl->increments(‘id’);
$tbl->integer(‘post_id’)->unsigned();
$tbl->integer(‘user_id’)->unsigned();
$tbl->foreign(‘post_id’)->references(‘post_id’)->on(‘sitePosts’);
$tbl->foreign(‘user_id’)->references(‘id’)->on(‘users’);
$tbl->softDeletes();
$tbl->timestamps();
});

The following is the updated portion of the postLikes schema. When you run your PHP controller code on the schema, you’ll have no error. Check the “foreign key constraint fails” error.

Schema::create(‘postLikes’, function (Blueprint $tbl) {
$tbl>integer(‘post_id’)->unsigned();
$tbl>foreign(‘post_id’)->references(‘post_id’)->on(‘sitePosts’)->onDelete(‘cascade’);
});

Also, be aware the cascade update and delete has a maximum depth of fifteen. If you exceed the maximum depth, you’ll get the error that states foreign key cascade delete/update exceeds max depth of 15.

– Drop the Child Table Before Dropping the Parent

If there is a reference between a parent and child table, you should drop the child table before the parent. Ensure you do this before you perform an update parent row procedure.

Conclusion

This article detailed what causes constraint errors when working with a database and learned how to fix it. The following are the takeaway from this article:

• Do not try to delete a parent row referenced in another table, as it’ll lead to an error.
• When creating tables with SQL queries, a SQL query for a parent table should come before a child table.
• If you have the 1451 database error in Laravel, check your schema for constraints.
• Delete data in the child table before deleting the parent row.

Now that you know how to fix constraint errors, don’t forget to share our article with your colleagues.

Simple Ways to Fix Error Code 1451 MySQL

February 28, 2021 Posted in

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.

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.

Источник

Ошибки внешних ключей: errno 150, MySQL error 1005 и другие

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Функционирование внешних ключей в MySQL имеет много нюансов и ограничений из-за чего существует немало возможностей получить ошибку при работе с ними. Одна из проблем состоит в том, что сообщения об ошибках содержат мало полезной информации и не указывают причину возникновения ошибки. В данной статье дается объяснение как получить дополнительную информацию об ошибке и приведен полный список причин возникновения ошибок внешних ключей. Каждая причина снабжена уникальным буквенно-цифровым кодом (А4, Б1, ..), использующимся в сводной таблице в конце статьи, которая поможет вам быстро диагностировать проблему.

Внешний ключ — это поле (или набор полей) в таблице, называемой дочерней, которое ссылается на поле (или набор полей) в таблице, называемой родительской. Дочерняя и родительская таблицы могут совпадать, т.е. таблица будет ссылаться на саму себя. Внешние ключи позволяют связать записи в двух таблицах по определенным полям так, что при обновлении поля в родительской автоматически происходит изменение записи в дочерней таблице.

В MySQL внешние ключи не реализованы на уровне сервера, их поддержка зависит от используемого хранилища данных. Содержание статьи справедливо для InnoDB (в том числе и для XtraDB).

Как получить больше данных об ошибке

После получения ошибки выполните SHOW ENGINE INNODB STATUS и смотрите содержимое секции LATEST FOREIGN KEY ERROR. Этот способ имеет следующие недостатки:

• требует привилегии SUPER
• содержит информацию о последней ошибке, связанной с внешними ключами, из-за чего нужно выполнять SHOW ENGINE INNODB STATUS сразу после возникновения ошибки, что не всегда удобно/возможно
• используются внутренние имена таблиц (например, ‘test.#sql-d88_b’), что затрудняет диагностику
• порой содержит мало полезной информации или таковая вообще отсутствует.

Альтернатива: использовать MariaDB версий больше 5.5.45 и 10.0.21, в которых сообщения об ошибках значительно улучшены и указывают причину возникновения ошибки.

Errno 150

Если в сообщении об ошибке содержится errno 150 (или errno 121), значит парсер MySQL не смог распознать ошибку и передал команду (create/alter) на выполнение в InnoDB. В этом разделе перечислены ситуации, приводящие к ошибкам, содержащим errno 150.

А1. Нет индекса в родительской таблице. Набор полей, на которые ссылается дочерняя таблица, должен быть проиндексирован (или являться левой частью другого индекса). Порядок полей в индексе должен быть таким же как в определении внешнего ключа. Сюда же относится случай отсутствия нужной колонки в родительской таблице (нет колонки, нет и индекса).

Неочевидный момент: на колонке родительской таблицы есть индекс — полнотекстовый (fulltext). Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть обычным (btree).

Другой неочевидный момент: на колонке родительской таблицы есть индекс — префиксный. Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть определен на всей длине колонки.

Строго говоря, поля в дочерней таблице тоже должны быть проиндексированы, но если нет подходящего индекса, MySQL автоматически его создаст при добавлении внешнего ключа (в совсем уж древних версиях требовалось предварительное создание индекса).

create table t1 ( a int , b int , index ( a ) ) engine = innodb ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) , foreign key ( a ) references t1 ( b ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -16 06 : 37 : 39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key ( a ) references t1 ( b ) ) engine = innodb :
Cannot find an index in the referenced table where the
referenced columns appear as the first columns , or column types
in the table and the referenced table do not match for constraint .
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB -4.1 .12 , and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/ 5.7 /en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————

— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1

create table t2 ( a int ) engine = innodb ;
alter table t2 add foreign key ( a ) references t1 ( a ) , add foreign key ( a ) references t1 ( b ) ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.#sql-a64_1’ ( errno: 150 )

— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)

alter table t2 add foreign key ( a ) references t1 ( a ) , add foreign key ( a ) references t1 ( b ) ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— аналогично и для оператора CREATE

drop table t2;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) , foreign key ( a ) references t1 ( b ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

Обратите внимание, если внешний ключ уже существует и в результате изменений (alter table) возникает ситуация отсутствия индекса в родительской таблице, то код ошибки будет 1025:

create table t1 ( a int , b int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

alter table t1 drop a;
ERROR 1025 ( HY000 ) : Error on rename of ‘. t est # sql-d6c_5’ to ‘. t est t 1’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220 7 : 14 : 25 Error in foreign key constraint of table test/t2:
there is no index in referenced table which would contain
the columns as the first columns , or the data types in the
referenced table do not match the ones in table . Constraint :
,
CONSTRAINT «t2_ibfk_1» FOREIGN KEY ( «a» ) REFERENCES «t1» ( «a» )
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/ 5.5 /en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB : Renaming table `test`.` #sql-d6c_5` to `test`.`t1` failed!
———

А2. Родительская таблица не найдена в словаре данных InnoDB. Это означает, что родительская таблица должна существовать и быть постоянной InnoDB таблицей. Не временной InnoDB таблицей, так как информация о временных таблицах не сохраняется в словаре данных InnoDB. И уж тем более не представлением.

mysql> create table t1 ( a int , index ( a ) ) engine = myisam ;

mysql> create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 ( HY000 ) : Cannott create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -17 16 : 30 : 09 0x364c Error in foreign key constraint of table world/t2:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot resolve table name close to :
( a ) ) engine = innodb
————

А3. Синтаксическая ошибка. Внешние ключи реализованы на уровне хранилища, и в старых версиях парсер сервера MySQL не распознавал синтаксические ошибки внешних ключей, из-за чего их было трудно идентифицировать.

Например, в определении внешнего ключа количество столбцов дочерней таблицы не совпадает с количеством столбцов родительской таблицы:

create table t1 ( id int not null primary key , b int , key ( b ) ) engine = innodb ;
Query OK, 0 rows affected ( 0.22 sec )

alter table t1 add foreign key ( id,b ) references t1 ( id ) ;
ERROR 1005 ( HY000 ) : Can ‘t create table ‘ test. #sql-d88_b’ (errno: 150)

— понять, что причина в синтаксической ошибке
— можно только из:

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22 : 28 : 23 Error in foreign key constraint of table test/ #sql-d88_b:
foreign key ( id,b ) references t1 ( id ) :
Syntax error close to :

— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 ( 42000 ) : Incorrect foreign key definition for ‘foreign key without name’ : Key reference and table reference don ‘t match

Другой пример: попробуем создать внешний ключ на поле типа text:

create table t1 ( a text , index ( a ( 50 ) ) ) engine = innodb ;

create table t2 ( a text , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1170 ( 42000 ) : BLOB / TEXT column ‘a’ used in key specification without a key length

— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:

create table t2 ( a text , foreign key ( a ( 50 ) ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.

А4. Несовпадение типов данных. Столбцы дочерней таблицы, входящие в определение внешнего ключа, должны иметь такие же типы данных, что и столбцы родительской таблицы, на которые они ссылаются, вплоть до атрибутов: знак и кодировка/сопоставление.

— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 ( a int unsigned , index ( a ) ) engine = innodb ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 ( HY000 ) : Cannott create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -26 03 : 00 : 47 0x10894 Error in foreign key constraint of table world/t2:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot find an index in the referenced table where the
referenced columns appear as the first columns , or column types
in the table and the referenced table do not match for constraint .
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB -4.1 .12 , and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/ 5.7 /en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————

Если несоответствие типов данных возникает во время изменения таблицы при уже существующем внешнем ключе, то ошибка будет иметь вид:

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

MariaDB [ test ] > alter table t1 modify a int unsigned ;
ERROR 1025 ( HY000 ) : Error on rename of ‘. t est # sql-d6c_6’ to ‘. t est t 1’ ( errno: 150 )

А5. Некорректно задано действие внешнего ключа. Если в определении внешнего ключа указано ON UPDATE SET NULL и/или ON DELETE SET NULL, то соответствующие столбцы дочерней таблицы не должны быть определены как NOT NULL.

create table t1 ( a int not null , index ( a ) ) engine = innodb ;

create table t2 ( a int not null , foreign key ( a ) references t1 ( a ) on delete set null ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— в старых версиях будет:
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -26 06 : 24 : 42 0x10894 Error in foreign key constraint of table world/t2:
foreign key ( a ) references t1 ( a ) on delete set null ) engine = innodb :
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL .
————

Если коллизия возникает при уже существующем внешнем ключе, то:

create table t1 ( a int not null , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) on delete set null ) engine = innodb ;

alter table t2 modify a int not null ;
ERROR 1025 ( HY000 ) : Error on rename of ‘. t est # sql-d6c_6’ to ‘. t est t 2’ ( errno: 150 )

А6. Дочерняя таблица является временной InnoDB таблицей. Внешние ключи можно создавать только в постоянной, несекционированной InnoDB таблице.

create table t1 ( a int , index ( a ) ) engine = innodb ;

create temporary table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 4 : 22 : 26 Error in foreign key constraint of table temp/ #sql318_4_1:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot resolve table name close to :
( a ) ) engine = innodb
———

— в новых версиях ошибка будет иметь вид:
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

А7. Родительская таблица является секционированной таблицей. На данный момент (MySQL 5.7 и MariaDB 10.1) внешние ключи не поддерживаются для секционированных таблиц (partitioned tables). Иными словами, ни родительская, ни дочерняя таблица не должны иметь секции. В случае, когда внешний ключ ссылается на секционированную таблицу диагностика ошибки затруднена ошибкой вывода show engine innodb status:

create table t1 ( a int , index ( a ) ) partition by range ( a )
( partition p0 values less than ( 10 ) ,
partition p1 values less than ( 20 ) ,
partition p2 values less than maxvalue ) ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19 : 38 : 14 Error in foreign key constraint of table test/t2:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot resolve table name close to :
( a ) ) engine = innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)

— в новых версиях ошибка будет иметь вид:

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

Если разбивать на секции родительскую таблицу после создания внешнего ключа, то

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

alter table t1 PARTITION BY HASH ( a ) PARTITIONS 8 ;
ERROR 1217 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails

show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR

Errno 121

Такой результат возникает только в одном случае.

Б1. Неуникальное имя ограничения. Обратите внимание: речь не о имени внешнего ключа. Если при создании внешнего ключа вы указываете не обязательное ключевое слово CONSTRAINT, то идущий после него идентификатор должен быть уникальным в пределах базы данных.

create table t1 ( a int , index ( a ) ) engine = innodb ;

create table t2 ( a int , CONSTRAINT q1 foreign key ( a ) references t1 ( a ) ) engine = innodb ;

create table t3 ( a int , CONSTRAINT q1 foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t3’ ( errno: 121 )

— в 5.7 будет другая ошибка
ERROR 1022 ( 23000 ) : Cannot write ; duplicate key in table ‘t3’

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 3 : 31 : 11 Error in foreign key constraint creation for table `test`.`t3`.
A foreign key constraint of name `test`.`q1`
already exists . ( Note that internally InnoDB adds ‘databasename’
in front of the user-defined constraint name. )
Note that InnoDB FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case , then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
———

Нет ошибок

Внешний ключ не создается, и нет никаких ошибок. Это может происходить по следующим причинам:

В1. Дочерняя таблица не является InnoDB таблицей. В этом случае для совместимости с другими субд парсер MySQL просто проигнорирует конструкцию внешнего ключа.

create table t1 ( a int , index ( a ) ) engine = innodb ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = myisam ;
Query OK, 0 rows affected ( 0.33 sec )

MariaDB [ test ] > show create table t2G
*************************** 1 . row ***************************
Table : t2
Create Table : CREATE TABLE `t2` (
`a` int ( 11 ) DEFAULT NULL ,
KEY `a` ( `a` )
) ENGINE = MyISAM DEFAULT CHARSET =latin1
1 row in set ( 0.00 sec )

В2. Не соответствует синтаксису MySQL. Стандарт SQL разрешает указывать внешний ключ сразу при объявлении колонки с помощью конструкции REFERENCES (например, . a int references t1(a), . ), однако MySQL игнорирует такую форму записи. Единственный способ создать в нем внешний ключ — это использовать отдельный блок FOREIGN KEY:

[ CONSTRAINT [ symbol ] ] FOREIGN KEY
[ index_name ] ( index_col_name, . )
REFERENCES tbl_name ( index_col_name. )
[ ON DELETE reference_option ]
[ ON UPDATE reference_option ]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Несоответствие данных

В этой части собраны ошибки, которые возникают из-за нарушения ссылочной целостности, т.е. наличие в дочерней таблице записей, которым нет соответствия в родительской таблице.

Г1. Удаление родительской таблицы. Нельзя удалить родительскую таблицу при наличии внешнего ключа.

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

drop table t1;
ERROR 1217 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails

Удаление следует понимать в расширенном варианте как удаление из множества InnoDB таблиц. Например, если мы сменим (alter table) движок родительской таблицы на MyISAM, то с точки зрения ограничения внешнего ключа родительская таблица перестанет существовать (т.к. она должна быть постоянной innodb таблицей):

Сначала нужно удалить внешний ключ (или всю дочернюю таблицу, что удалит в том числе и внешний ключ). Если вы не знаете какие таблицы являются дочерними для заданной таблицы, то это можно определить через запрос к information_schema:

Г2. Изменение данных в родительской таблице. Если в определении внешнего ключа не задано действие при update/delete, то такие операции над родительской таблицей могут привести к несогласованности данных, т.е. появлению в дочерней таблице записей не имеющих соответствия в родительской таблице.

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

insert into t1 values ( 1 ) ;
insert into t2 values ( 1 ) ;

update t1 set a= 2 ;
ERROR 1451 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails ( `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY ( `a` ) REFERENCES `t1` ( `a` ) )

Г3. Изменение данных в дочерней таблице. Если insert/update записи в дочерней таблицы приводит к несогласованности данных, то

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

insert into t2 values ( 15 ) ;
ERROR 1452 ( 23000 ) : Cannot add or update a child row: a foreign key constraint fails ( `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY ( `a` ) REFERENCES `t1` ( `a` ) )

Г4. Добавление внешнего ключа на не пустую таблицу. При попытке добавить внешний ключ на таблицу, в которой есть записи, не удовлетворяющие условию внешнего ключа (т.е. не имеющие соответствия в родительской таблице), будет ошибка:

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , index ( a ) ) engine = innodb ;

insert into t2 values ( 2 ) ;

alter table t2 add foreign key ( a ) references t1 ( a ) ;
ERROR 1452 ( 23000 ) : Cannot add or update a child row: a foreign key constraint fails ( `test`.` #sql-3f0_4`, CONSTRAINT `#sql-3f0_4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

Г5. Не уникальный ключ в родительской таблице. По стандарту SQL набор полей, на которые ссылается внешний ключ, должен быть уникальным. Однако, реализация внешних ключей в InnoDB позволяет иметь несколько «родителей». Из-за этого возникает трудно диагностируемая ошибка:

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , index ( a ) ) engine = innodb ;

insert into t1 values ( 1 ) , ( 1 ) ;
insert into t2 values ( 1 ) ;

delete from t1 where a= 1 limit 1 ;
ERROR 1451 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails ( `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY ( `a` ) REFERENCES `t1` ( `a` ) )

Сводная таблица

По вертикали расположены коды ошибок MySQL, которые возникают при работе с внешними ключами («нет ошибок» соответствует ситуации, когда сервер не генерирует ошибку, но и не создает внешний ключ). По горизонтали — идентификаторы причин, которые могут привести к ошибке. Плюсы на пересечении указывают какие причины приводят к той или иной ошибке.

Источник

Answer by Lara Reyes

The main problem with this erorr Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails is that it doesn’t let you know which table contains the FK failure, so it is difficult to solve the conflict.,Once you correct the foreign key relationship, your delete statement will work.,Connect and share knowledge within a single location that is structured and easy to search.,As is, you must delete the row out of the advertisers table before you can delete the row in the jobs table that it references. This:

The simple way would be to disable the foreign key check; make the changes then re-enable foreign key check.

SET FOREIGN_KEY_CHECKS=0; -- to disable them
SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

Answer by Alia Welch

Sometimes, when we must drop an InnoDB table in MySQL, we could encounter the following error due to foreign keys:,As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys.,and a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax:,The “ON UPDATE CASCADE” clause means that if we update values in the parent table (‘actor’ in our example),

In our example, we have the following parent table in a MySQL 5.7.21 server:

mysqld3-([email protected]) [sakila]> show create table actorG
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

and a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax:

mysqld3-([email protected]) [sakila]> show create table film_actorG
*************************** 1. row ***************************
       Table: film_actor
Create Table: CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The “ON UPDATE CASCADE” clause means that if we update values in the parent table (‘actor’ in our example),

mysqld3-([email protected]) [sakila]> select * from actor where actor_id=1;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysqld3-([email protected]) [sakila]> select * from film_actor where actor_id=1;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|        1 |       1 | 2006-02-15 05:05:03 |
|        1 |      23 | 2006-02-15 05:05:03 |
|        1 |      25 | 2006-02-15 05:05:03 |
|        1 |     106 | 2006-02-15 05:05:03 |
|        1 |     140 | 2006-02-15 05:05:03 |
|        1 |     166 | 2006-02-15 05:05:03 |
|        1 |     277 | 2006-02-15 05:05:03 |
|        1 |     361 | 2006-02-15 05:05:03 |
|        1 |     438 | 2006-02-15 05:05:03 |
|        1 |     499 | 2006-02-15 05:05:03 |
|        1 |     506 | 2006-02-15 05:05:03 |
|        1 |     509 | 2006-02-15 05:05:03 |
|        1 |     605 | 2006-02-15 05:05:03 |
|        1 |     635 | 2006-02-15 05:05:03 |
|        1 |     749 | 2006-02-15 05:05:03 |
|        1 |     832 | 2006-02-15 05:05:03 |
|        1 |     939 | 2006-02-15 05:05:03 |
|        1 |     970 | 2006-02-15 05:05:03 |
|        1 |     980 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
19 rows in set (0.00 sec)
mysqld3-([email protected]) [sakila]> update actor set actor_id=300 where actor_id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

then this will cause an automatic update of the matching rows in the child table (‘film_actor’):

mysqld3-([email protected]) [sakila]> select * from actor where actor_id=1;
Empty set (0.00 sec)
mysqld3-([email protected]) [sakila]> select * from film_actor where actor_id=1;
Empty set (0.00 sec)
mysqld3-([email protected]) [sakila]> select * from actor where actor_id=300;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      300 | PENELOPE   | GUINESS   | 2018-02-07 15:41:45 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysqld3-([email protected]) [sakila]> select * from film_actor where actor_id=300;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|      300 |       1 | 2006-02-15 05:05:03 |
|      300 |      23 | 2006-02-15 05:05:03 |
|      300 |      25 | 2006-02-15 05:05:03 |
|      300 |     106 | 2006-02-15 05:05:03 |
|      300 |     140 | 2006-02-15 05:05:03 |
|      300 |     166 | 2006-02-15 05:05:03 |
|      300 |     277 | 2006-02-15 05:05:03 |
|      300 |     361 | 2006-02-15 05:05:03 |
|      300 |     438 | 2006-02-15 05:05:03 |
|      300 |     499 | 2006-02-15 05:05:03 |
|      300 |     506 | 2006-02-15 05:05:03 |
|      300 |     509 | 2006-02-15 05:05:03 |
|      300 |     605 | 2006-02-15 05:05:03 |
|      300 |     635 | 2006-02-15 05:05:03 |
|      300 |     749 | 2006-02-15 05:05:03 |
|      300 |     832 | 2006-02-15 05:05:03 |
|      300 |     939 | 2006-02-15 05:05:03 |
|      300 |     970 | 2006-02-15 05:05:03 |
|      300 |     980 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+

Sometimes, when we must drop an InnoDB table in MySQL, we could encounter the following error due to foreign keys:

mysqld3-([email protected]) [sakila]> drop table actor;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In this example, the ‘actor’ table is referenced by the ‘film_actor’ one:

mysqld3-([email protected]) [sakila]> SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA = 'sakila'
    -> AND REFERENCED_TABLE_NAME = 'actor';
+------------+-------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME     | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+---------------------+-----------------------+------------------------+
| film_actor | actor_id    | fk_film_actor_actor | actor                 | actor_id               |
+------------+-------------+---------------------+-----------------------+------------------------+
1 row in set (0.01 sec)

This foreign key constraint let data being consistent over different tables and that’s also the reason why we could not drop the parent table.
We can find this same information and the error cause displaying the state of the InnoDB storage engine through the “SHOW ENGINE INNODB STATUS” command:

mysqld3-([email protected]) [sakila]> show engine innodb statusG
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-02-07 15:44:34 0x7fb734174700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 84 srv_active, 0 srv_shutdown, 85720 srv_idle
srv_master_thread log flush and writes: 85803
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1607
OS WAIT ARRAY INFO: signal count 1552
RW-shared spins 0, rounds 757, OS waits 384
RW-excl spins 0, rounds 342, OS waits 11
RW-sx spins 2, rounds 60, OS waits 1
Spin rounds per wait: 757.00 RW-shared, 342.00 RW-excl, 30.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-02-07 15:42:45 0x7fb734174700  Cannot drop table `sakila`.`actor`
because it is referenced by `sakila`.`film_actor`
...

Answer by Miracle Bender

Example 1: error 1451 (23000): cannot delete or update a parent row: a foreign key constraint fails

SET FOREIGN_KEY_CHECKS = 0; -- to disable FK checks (ex: for delete purpose)
SET FOREIGN_KEY_CHECKS = 1; -- to re-enable FK checks

Example 2: Cannot delete or update a parent row: a foreign key constraint fails (

ALTER TABLE "appointments" DROP FOREIGN KEY "appointments_user_id_foreign";

ALTER TABLE "appointments" ADD CONSTRAINT "appointments_user_id_foreign" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE;

Answer by Shay Bender

TL;DR: If you can’t quickly see why your DROP TABLE statement is failing, check for typos in your table names.,If you’re looking at this, you might be confused. I switched the order in which the tables were dropped, but, hm, I’m still getting the same complaint! Let’s look at our database by using the SHOW TABLES command:,At this point, I hope you’ve noticed the problem! There was a typo in my DROP TABLE script. The command DROP TABLE IF EXISTS toy_makes; ran without an error because it has the option IF EXISTS in it. There’s no table called toy_makes here – the table is called toy_makers.,Let’s create a simple example using MySQL, in order to demo the problem. As the root user, sign in to MySQL using your favorite client. I like to use the command line mysql client, like this: mysql -u root -p.

USE hamsters;
create table toys (
   toy_id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   PRIMARY KEY ( toy_id )
);
create table toy_makers (
   toy_maker_id INT NOT NULL AUTO_INCREMENT,
   toy_id INT,
   name VARCHAR(25),
   FOREIGN KEY ( toy_id ) REFERENCES toys ( toy_id ),
   PRIMARY KEY ( toy_maker_id )
);

Answer by Evie Rios

Here I have put my model and controller. Why this error occur when I delete the task it shows error.
This is my delete method,This is my model Todo,This is my step migration step table,You can do this by using cascade on delete in your table

Here I have put my model and controller. Why this error occur when I delete the task it shows error.
This is my delete method

   public function destroy(Todo $todo)
    {
        $todo->delete();
        return redirect()->back()->with('message','Task is completely deleted');
    }

This is my model Todo

Todo.php
class Todo extends Model
{
    protected $fillable = ['title', 'completed', 'user_id', 'description'];

    public function getRouteKeyName()
    {
        return 'id';
    }

    public function steps()
    {
        return $this->hasMany(Step::class);
    }
}

This is my step migration step table

    public function up()
    {
        Schema::create('steps', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->foreignId('todo_id')->constrained();
            $table->timestamps();
        });
    }

THis is my todo table

     */
    public function up()
    {
        Schema::create('todos', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('description');
            $table->boolean('completed')->default(false);
            $table->foreignId('user_id')->constrained();
            $table->timestamps();
        });
    }