Mysql код ошибки 1451

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

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.

You haven’t provided enough details but the problem is likely caused by the ill-defined foreign key. You have a foreign key (1 column) that does not reference the primary key (which is 2 columns):

primary key (Engineer_Name, Team),

foreign key (Boss) references `Team Engineers` (Engineer_Name)

This construction — while it is allowed by MySQL — it is not recommended, even by its documentation.

Solution:

Adjust the foreign key reference so it matches the primary key. I see 2 options here, depending on the business requirements. Can the Boss of an Engineer be on a different Team? Or are they always on the same Team?

If they can be on separate teams, then you need a Boss_Team column, too. Code:

-- added
Boss_Team char(50) null,           

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Boss_Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

If Engineer and Boss are always on the same Team:

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

I assume that the PRIMARY KEY cannot be modified. If it is changed (say Engineer_Name becomes the primary key), then the foreign key you have will work correctly.