부모 행을 삭제하거나 업데이트 할 수 없습니다. 외래 키 제약 조건이 실패합니다

부모 행을 삭제하거나 업데이트 할 수 없습니다. 외래 키 제약 조건이 실패합니다

---

할 때 :

DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1 

오류 :

#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))

내 테이블은 다음과 같습니다.

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

---

있는 그대로, 광고주 테이블에서 행을 삭제해야 참조 테이블이 참조하는 작업 테이블에서 행을 삭제할 수 있습니다. 이:

ALTER TABLE `advertisers`
  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) 
      REFERENCES `jobs` (`advertiser_id`);

... 실제와 반대입니다. 즉, 광고주보다 작업 테이블에 레코드가 있어야 함을 의미합니다. 따라서 다음을 사용해야합니다.

ALTER TABLE `jobs`
  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) 
      REFERENCES `advertisers` (`advertiser_id`);

외래 키 관계를 수정하면 삭제 문이 작동합니다.

---

간단한 방법은 외래 키 검사를 비활성화하는 것입니다. 변경 후 외래 키 확인을 다시 활성화하십시오.

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

---

현재 (결함이있는) 디자인에서 참조하는 작업 테이블에서 행을 삭제 하기 전에 광고주 테이블 에서 행을 삭제해야합니다.

Alternatively, you could set up your foreign key such that a delete in the parent table causes rows in child tables to be deleted automatically. This is called a cascading delete. It looks something like this:

ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`)
ON DELETE CASCADE;

Having said that, as others have already pointed out, your foreign key feels like it should go the other way around since the advertisers table really contains the primary key and the jobs table contains the foreign key. I would rewrite it like this:

ALTER TABLE `jobs`
ADD FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`);

And the cascading delete won't be necessary.

---

If you want to drop a table you should execute the following query in a single step

SET FOREIGN_KEY_CHECKS=0; DROP TABLE table_name;

---

I think that your foreign key is backwards. Try:

ALTER TABLE 'jobs'
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`)

---

If there are more than one job having the same advertiser_id, then your foreign key should be:

ALTER TABLE `jobs`
ADD CONSTRAINT `advertisers_ibfk_1` 
FOREIGN KEY (`advertiser_id`) 
REFERENCES `advertisers` (`advertiser_id`);

Otherwise (if its the other way round in your case), if you want the rows in advertiser to be automatically deleted if the row in job is deleted add the 'ON DELETE CASCADE' option to your foreign key

ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1` 
FOREIGN KEY (`advertiser_id`) 
REFERENCES `jobs` (`advertiser_id`);
ON DELETE CASCASE

Check out Foreign Key constraints

---

You need to delete it by order There are dependency in the tables

---

I had this problem in laravel migration too
the order of drop tables in down() method does matter

Schema::dropIfExists('groups');
Schema::dropIfExists('contact');

may not work, but if you change the order, it works.

Schema::dropIfExists('contact');
Schema::dropIfExists('groups');

---

When you create database or create tables

You should add that line at top script create database or table

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;

Now you want to delete records from table? then you write as

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1

Good luck!

---

if you need to support client as soon as possible, and do not have access to

FOREIGN_KEY_CHECKS

so that data integrity can be disabled:

1) delete foreign key

ALTER TABLE `advertisers` 
DROP FOREIGN KEY `advertisers_ibfk_1`;

2) activate your deleting operation thruogh sql or api

3) add the foreign key back to schema

ALTER TABLE `advertisers`
  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`);

however, it is a hot-fix, so it is on your own risk, because the main flaw of such approach is that it is needed afterwards to keep the data integrity manually.

---

How about this alternative I've been using: allow the foreign key to be NULL and then choose ON DELETE SET NULL.

Personally I prefer using both "ON UPDATE CASCADE" as well as "ON DELETE SET NULL" to avoid unnecessary complications, but on your set up you may want a different approach. Also, NULL'ing foreign key values may latter lead complications as you won't know what exactly happened there. So this change should be in close relation to how your application code works.

Hope this helps.

---

You could create a trigger to delete the referenced rows in before deleting the job.

    DELIMITER $$
    CREATE TRIGGER before_jobs_delete 
        BEFORE DELETE ON jobs
        FOR EACH ROW 
    BEGIN
        delete from advertisers where advertiser_id=OLD.advertiser_id;
    END$$
    DELIMITER ;

---

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.

If you use MySQL or similar, I found out that you can create an ER diagram for your database, then you can review and safely remove any conflicts triggering the error.

1. Use MySQL workbench
2. Click on Database -> Reverse Engineering
3. Select a correct connection
4. Next till the end, remember to select database & tables that need examine
5. Now you have the ER diagram, you can see which table have FK conflict

---

Maybe you should try ON DELETE CASCADE

참고URL : https://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails