MySQL试图删除所有不受外键约束的行 [英] MySQL attempting to delete all rows which are not constrained by foreign key

问题描述

好吧，这可能是一个非常简单的问题，但是恐怕我几乎不知道MySQL，请耐心等待。我只是试图从一个表中删除每一行，不受另一个表中的外键约束 - 一个特定的表，这里只涉及两个表。创建语句看起来有点像：

  CREATE TABLE`testschema`.`job`（
`Job_Id` int （10）unsigned NOT NULL AUTO_INCREMENT，
`Comment` varchar（255）DEFAULT NULL，
 PRIMARY KEY（`Job_Id`）使用BTREE，
）ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = UTF8; 
 
 CREATE TABLE`ermieimporttest`.`jobassignment`（
`JobAssignment_Id` int（10）unsigned NOT NULL AUTO_INCREMENT，
`JobId` int（10）unsigned DEFAULT NULL，
 PRIMARY KEY（`JobAssignment_Id`），
 KEY`FK_jobassignment_1`（`JobId`），
 CONSTRAINT`FK_jobassignment_1` FOREIGN KEY（`JobId`）REFERENCES`job`（`Job_Id`）， 
）ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8; 
  

我的任何SQL语句都是：

 从作业中删除使用作业INNER JOIN jobAssignment WHERE job.Job_Id！= jobAssignment.JobId; 
  

我认为这是正确的 - 它应该从作业表中删除不存在的每个作业有一份工作，因为它是外键。但是，当我尝试执行它时，出现以下错误：
$ b

无法删除或更新父行：
a foreign （code> testdatabase 。 jobassignment ，
CONSTRAINT FK_jobassignment_1
FOREIGN KEY（ JobId ）REFERENCES job
code> Job_Id ））

那么我做错了什么傻事？ >

编辑：像往常一样，我发现这里只有几秒钟后的答案。我使用了（完全不同的）查询：
$ b $ pre $ 从作业中删除Job_Id不在（选择JobId FROM jobassignment）


出于好奇，这是更好的方法吗？我原来的想法是否可行？如果是的话，那有什么问题呢？

解决方案

 从作业中删除作业
左连接jobAssignment ON（job。 Job_Id = jobAssignment.JobId）
 WHERE jobAssignment.JobId IS NULL; 
  

Okay, this is (probably) a very simple question, but I am afraid I know almost no MySQL, so please put up with me. I'm just trying to delete every row from one table which is not constrained by a Foreign Key in another table - a specific table, there are only two tables involved here. The create statements look a bit like:

CREATE TABLE  `testschema`.`job` (
  `Job_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Job_Id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE  `ermieimporttest`.`jobassignment` (
  `JobAssignment_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `JobId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`JobAssignment_Id`),
  KEY `FK_jobassignment_1` (`JobId`),
  CONSTRAINT `FK_jobassignment_1` FOREIGN KEY (`JobId`) REFERENCES `job` (`Job_Id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Any my SQL statement is:

DELETE FROM job USING job INNER JOIN jobAssignment WHERE job.Job_Id != jobAssignment.JobId;

I thought this was correct - it should delete every job from the job table for which there does not exist a job assignment which has that job as it's Foreign Key. However, this fails with the following error when I try and execute it:

Cannot delete or update a parent row: a foreign key constraint fails (testdatabase.jobassignment, CONSTRAINT FK_jobassignment_1 FOREIGN KEY (JobId) REFERENCES job (Job_Id))

So what silly thing am I doing wrong?

EDIT: As usual, I found an answer only seconds after posting here. I used the (completely different) query:

DELETE FROM job WHERE Job_Id NOT IN (SELECT JobId FROM jobassignment) 

Out of curiosity, is this the better way to do it? Was my original idea even feasible? And if so, what was wrong with it?

解决方案

DELETE FROM job USING job 
LEFT JOIN jobAssignment ON(job.Job_Id = jobAssignment.JobId)
WHERE jobAssignment.JobId IS NULL;

这篇关于MySQL试图删除所有不受外键约束的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！